poi导出excel
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
此例子用的是poi3.9的版本，请在apache官网自行下载。
一、相关api介绍
//生成Workbook (.xls) HSSFWorkbook wb = new HSSFWorkbook(); //生成Workbook (.xlsx) XSSFWorkbook wb = new XSSFWorkbook(); //添加Worksheet（不添加sheet时生成的xls文件打开时会报错） @SuppressWarnings("unused") Sheet sheet1 = wb.createSheet(); //保存为Excel文件 FileOutputStream out = null; try { out = new FileOutputStream("c:\\text.xls"); wb.write(out); } catch (IOException e) { System.out.println(e.toString()); } finally { try { out.close(); } catch (IOException e) { System.out.println(e.toString()); } } //读取Workbook FileInputStream in = null; Workbook wb = null; try { in = new FileInputStream(TEST_WORKBOOK_NAME); wb = WorkbookFactory.create(in); } catch (IOException e) { System.out.println(e.toString()); } catch (InvalidFormatException e) { System.out.println(e.toString()); } finally { try { in.close(); } catch (IOException e) { System.out.println(e.toString()); } } //行操作 //创建 Row row1 = wb.getSheet("sheet1").createRow(1); //删除 wb.getSheet("sheet1").removeRow(row1); //移动行（把第2行和第3行移到第6行之后） wb.getSheet("sheetname5").shiftRows(1, 2, 6); //设置默认行高 wb.getSheet("sheetname6").setDefaultRowHeight((short)100); //设置行高 wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20)); //设置默认列宽 wb.getSheet("sheetname7").setDefaultColumnWidth(12); //设置列宽 wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256); //单元格操作 //追加行 for (int i = 0; i < 10; i++) { Row row = wb.getSheet("sheetname10").createRow(i); for (int j = 0; j < 10; j++) { //添加单元格 Cell cell = row.createCell(j); cell.setCellValue(i + 1); } //删除单元格 row.removeCell(row.getCell(5)); } //合并单元格 //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3)); //设置单元格值 for (List<Object> dataRow : objects) { row = sheet.createRow(lastRowIndex); lastRowIndex++; for (int j = 0; j < dataRow.size(); j++) { Cell contentCell = row.createCell(j); Object dataObject = dataRow.get(j); if (dataObject != null) { if (dataObject instanceof Integer) { contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); contentCell.setCellStyle(contentIntegerStyle); contentCell.setCellValue(Integer.parseInt(dataObject.toString())); } else if (dataObject instanceof Double) { contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); contentCell.setCellStyle(contentDoubleStyle); contentCell.setCellValue(Double.parseDouble(dataObject.toString())); } else if (dataObject instanceof Long && dataObject.toString().length() == 13) { contentCell.setCellType(XSSFCell.CELL_TYPE_STRING); contentCell.setCellStyle(contentStyle); contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString())))); } else if (dataObject instanceof Date) { contentCell.setCellType(XSSFCell.CELL_TYPE_STRING); contentCell.setCellStyle(contentStyle); contentCell.setCellValue(getCnDate((Date) dataObject)); } else { contentCell.setCellType(XSSFCell.CELL_TYPE_STRING); contentCell.setCellStyle(contentStyle); contentCell.setCellValue(dataObject.toString()); } } else { contentCell.setCellStyle(contentStyle); // 设置单元格内容为字符型 contentCell.setCellValue(""); } } } //设置标题头与标题列名 // 合并单元格 sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1)); // 产生表格标题行 Row rowMerged = sheet.createRow(lastRowIndex); lastRowIndex++; Cell mergedCell = rowMerged.createCell(0); mergedCell.setCellStyle(headStyle); mergedCell.setCellValue(new XSSFRichTextString(sheetTitle)); // 产生表格表头列标题行 Row row = sheet.createRow(lastRowIndex); lastRowIndex++; for (int i = 0; i < columnNames.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(headStyle); RichTextString text = new XSSFRichTextString(columnNames.get(i)); cell.setCellValue(text); }
二、实例代码ExcelUtils
package com.skin.webcat.util; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExportExcelUtil2 { private static final int DEFAULT_COLUMN_SIZE = 30; /** * 导出字符串数据 * * @param file 文件名 * @param columnNames 表头 * @param sheetTitle sheet页Title * @param append 是否追加写文件 * @return file * @throws ReportInternalException */ public static void exportExcelTitle(File file,String sheetName, List<String> columnNames, String sheetTitle, boolean append,List<List<Object>> objects) throws IOException { // 声明一个工作薄 Workbook workBook = new XSSFWorkbook(); if (file.exists() && append) { workBook = new XSSFWorkbook(new FileInputStream(file)); } else { workBook = new XSSFWorkbook(); } Map<String, CellStyle> cellStyleMap = styleMap(workBook); // 表头样式 CellStyle headStyle = cellStyleMap.get("head"); // 生成一个表格 Sheet sheet = workBook.getSheet(sheetName); if (sheet == null) { sheet = workBook.createSheet(sheetName); } //最新Excel列索引,从0开始 int lastRowIndex = sheet.getLastRowNum(); if (lastRowIndex > 0) { lastRowIndex++; } // 设置表格默认列宽度 sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE); // 合并单元格 sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1)); // 产生表格标题行 Row rowMerged = sheet.createRow(lastRowIndex); lastRowIndex++; Cell mergedCell = rowMerged.createCell(0); mergedCell.setCellStyle(headStyle); mergedCell.setCellValue(new XSSFRichTextString(sheetTitle)); // 产生表格表头列标题行 Row row1 = sheet.createRow(lastRowIndex); for (int i = 0; i < columnNames.size(); i++) { Cell cell = row1.createCell(i); cell.setCellStyle(headStyle); RichTextString text = new XSSFRichTextString(columnNames.get(i)); cell.setCellValue(text); } CellStyle contentStyle = cellStyleMap.get("content"); //正文整数样式 CellStyle contentIntegerStyle = cellStyleMap.get("integer"); //正文带小数整数样式 CellStyle contentDoubleStyle = cellStyleMap.get("double"); // 生成一个表格 if (sheet == null) { sheet = workBook.createSheet(sheetName); } //最新Excel列索引,从0开始 if (lastRowIndex > 0) { lastRowIndex++; } // 设置表格默认列宽度 sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE); // 遍历集合数据,产生数据行,前两行为标题行与表头行 for (List<Object> dataRow : objects) { Row row = sheet.createRow(lastRowIndex); lastRowIndex++; for (int j = 0; j < dataRow.size(); j++) { Cell contentCell = row.createCell(j); Object dataObject = dataRow.get(j); if (dataObject != null) { if (dataObject instanceof Integer) { contentCell.setCellStyle(contentIntegerStyle); contentCell.setCellValue(Integer.parseInt(dataObject.toString())); } else if (dataObject instanceof Double) { contentCell.setCellStyle(contentDoubleStyle); contentCell.setCellValue(Double.parseDouble(dataObject.toString())); } else if (dataObject instanceof Long && dataObject.toString().length() == 13) { contentCell.setCellStyle(contentStyle); contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString())))); } else if (dataObject instanceof Date) { contentCell.setCellStyle(contentStyle); contentCell.setCellValue(getCnDate((Date) dataObject)); } else { contentCell.setCellStyle(contentStyle); contentCell.setCellValue(dataObject.toString()); } } else { contentCell.setCellStyle(contentStyle); // 设置单元格内容为字符型 contentCell.setCellValue(""); } } } try { OutputStream ops = new FileOutputStream(file); workBook.write(ops); ops.flush(); ops.close(); } catch (IOException e) { throw new IOException(e); } // return file; } /** * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss */ private static String getCnDate(Date date) { String format = "yyyy-MM-dd HH:mm:ss"; SimpleDateFormat sdf = new SimpleDateFormat(format); return sdf.format(date); } /** * 创建单元格表头样式 * * @param workbook 工作薄 */ private static CellStyle createCellHeadStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); // 设置边框样式 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); //设置对齐样式 style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成字体 Font font = workbook.createFont(); // 表头样式 style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); return style; } /** * 创建单元格正文样式 * * @param workbook 工作薄 */ private static CellStyle createCellContentStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); // 设置边框样式 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); //设置对齐样式 style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成字体 Font font = workbook.createFont(); // 正文样式 style.setFillPattern(XSSFCellStyle.NO_FILL); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style.setFont(font); return style; } /** * 单元格样式(Integer)列表 */ private static CellStyle createCellContent4IntegerStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); // 设置边框样式 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); //设置对齐样式 style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成字体 Font font = workbook.createFont(); // 正文样式 style.setFillPattern(XSSFCellStyle.NO_FILL); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style.setFont(font); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数 return style; } /** * 单元格样式(Double)列表 */ private static CellStyle createCellContent4DoubleStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); // 设置边框样式 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); //设置对齐样式 style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成字体 Font font = workbook.createFont(); // 正文样式 style.setFillPattern(XSSFCellStyle.NO_FILL); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style.setFont(font); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点 return style; } /** * 单元格样式列表 */ private static Map<String, CellStyle> styleMap(Workbook workbook) { Map<String, CellStyle> styleMap = new LinkedHashMap<>(); styleMap.put("head", createCellHeadStyle(workbook)); styleMap.put("content", createCellContentStyle(workbook)); styleMap.put("integer", createCellContent4IntegerStyle(workbook)); styleMap.put("double", createCellContent4DoubleStyle(workbook)); return styleMap; } public static void main(String[] args) { File file = new File("d:/a.xlsx"); List<List<Object>> objects = new ArrayList<List<Object>>(); List<String> listn = new ArrayList<String>(); listn.add("a"); listn.add("b"); listn.add("c"); listn.add("d"); for (int i = 0; i < 1000; i++) { List<Object> os = new ArrayList<Object>(); os.add("a"); os.add(new Date()); os.add(1); os.add(1.33); objects.add(os); } try { exportExcelTitle(file, "sdf", listn, "213", false, objects); } catch (IOException e) { e.printStackTrace(); } } }
