ExcelUtil

cpf1985

import java.io.IOException;import java.net.URLEncoder;import java.util.Calendar;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Sheet;public class ExcelUtil {    private static String DATE_FORMAT = "yyyy-MM-dd"; // "YYYY-MM-DD" 定制日期格式    private static String NUMBER_FORMAT = "#,##0.00";// 定制浮点数格式    private  String xlsFilePath;// 文件名    private String fileName = "export";    private HSSFWorkbook workbook;// 工作薄    private HSSFSheet sheet1;// 工作表    private HSSFSheet sheet2;// 工作表   // private HSSFRow row;// 行       private String[] name1;    private String[][] data1;    private String[] name2;    private String[][] data2;    private HttpServletResponse response;    public ExcelUtil(String[] name,String[][] data,HttpServletResponse response) {        this.name1 = name;        this.data1 = data;        this.response = response;        init();    }    public ExcelUtil(String[] name1,String[] name2,String[][] data1,String[][] data2,HttpServletResponse response) {        this.name1 = name1;        this.data1 = data1;        this.name2 = name2;        this.data2 = data2;        this.response = response;        init("订单","订单详情");    }    private void init(String sheetName1,String sheetName2){        this.workbook = new HSSFWorkbook();        this.sheet1 = workbook.createSheet(sheetName1);        int rowNum1 = this.data1.length;        int colNum1 = this.name1.length;                this.sheet2 = workbook.createSheet(sheetName2);        int rowNum2 = this.data2.length;        int colNum2 = this.name2.length;//        输出表头        HSSFRow row1= createRow(0,sheet1);        for (int i = 0; i < colNum1; i++) {            setCell(i, this.name1[i],row1);        }//        输出数据        for (int n = 0; n < rowNum1; n++) {        HSSFRow row2 =  createRow(n + 1,sheet1);            for (int m = 0; m < colNum1; m++) {                setCell(m, this.data1[n][m],row2);            }        }                        HSSFRow row3= createRow(0,sheet2);        for (int i = 0; i < colNum2; i++) {            setCell(i, this.name2[i],row3);        }//        输出数据        for (int n = 0; n < rowNum2; n++) {        HSSFRow row4= createRow(n + 1,sheet2);            for (int m = 0; m < colNum2; m++) {            if(data2[n]!=null && data2[n][m]!=null){             setCell(m, this.data2[n][m],row4);            }                           }        }    }    private void init(){        this.workbook = new HSSFWorkbook();        this.sheet1 = workbook.createSheet();        int rowNum = this.data1.length;        int colNum = this.name1.length;//        输出表头        HSSFRow row1=  createRow(0);        for (int i = 0; i < colNum; i++) {         setCell(i, this.name1[i],row1);        }//        输出数据        for (int n = 0; n < rowNum; n++) {         HSSFRow row2=   createRow(n + 1);            for (int m = 0; m < colNum; m++) {                setCell(m, this.data1[n][m],row2);            }        }    }    //    输出流    public void write() throws IOException{        this.response.setContentType("application/vnd.ms-excel; charset=UTF-8");           this.response.setHeader("Content-Disposition", "filename="+URLEncoder.encode(this.fileName,"utf-8")+".xls");        this.response.setHeader("Cache-Control", "no-cache");        ServletOutputStream os = this.response.getOutputStream();        workbook.write(os);        os.flush();        os.close();    }                //增加一行    public HSSFRow createRow(int index) {    HSSFRow row = this.sheet1.createRow(index);    return row;    }    public HSSFRow createRow(int index,HSSFSheet sheet) {    HSSFRow  row = sheet.createRow(index);        return row;     }    //设置单元格，传入值为字符串的    public void setCell(int index, String value,HSSFRow row) {        HSSFCell cell = row.createCell(index);        cell.setCellType(HSSFCell.CELL_TYPE_STRING);        cell.setCellValue(value);     }    //    设置单元格，传入值为日期的    public void setCell(int index, Calendar value,HSSFRow row) {       HSSFCell cell = row.createCell(index);       cell.setCellValue(value.getTime());       HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式       cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式       cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式    }    //设置单元格，传入数据为int型    public void setCell(int index, int value,HSSFRow row) {       HSSFCell cell = row.createCell(index);       cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);       cell.setCellValue(value);    }    //设置单元格，传入值为double型    public void setCell(int index, double value,HSSFRow row) {       HSSFCell cell =  row.createCell(index);       cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);       cell.setCellValue(value);       HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式       HSSFDataFormat format = workbook.createDataFormat();       cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式       cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式    }        public String getXlsFilePath() {        return xlsFilePath;    }    public void setXlsFilePath(String xlsFilePath) {        this.xlsFilePath = xlsFilePath;    }    public String getFileName() {        return fileName;    }    public void setFileName(String fileName) {        this.fileName = fileName;    }    public HSSFWorkbook getWorkbook() {        return workbook;    }    }使用： String[] name1 = {"订单编号","买家会员名","收货人姓名","详细地址","买家留言"}; String[] name2 = {"订单编号","标题","颜色","尺码","价格","购买数量","备注"}; String[][] data = new String[list.size()][11]; String[][] data2=new String[10][7]; ExcelUtil eu =new ExcelUtil(name1,name2,data,data2,response); eu.write();