ExcelUtil
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";// 定制浮点数格式 privateString 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,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,row2); } } HSSFRow row3= createRow(0,sheet2); for (int i = 0; i < colNum2; i++) { setCell(i, this.name2,row3); }// 输出数据 for (int n = 0; n < rowNum2; n++) { HSSFRow row4= createRow(n + 1,sheet2); for (int m = 0; m < colNum2; m++) { if(data2!=null && data2!=null){ setCell(m, this.data2,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,row1); }// 输出数据 for (int n = 0; n < rowNum; n++) { HSSFRow row2= createRow(n + 1); for (int m = 0; m < colNum; m++) { setCell(m, this.data1,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) { HSSFRowrow = 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; String[][] data2=new String; ExcelUtil eu =new ExcelUtil(name1,name2,data,data2,response); eu.write();
页:
[1]