上次模仿poi例子写了个拼接XML导出Excel的例子,缺点很多,比如只能导出单sheet页,不支持合并单元格,不能设置行高列宽等,最近趁着有空,稍微修改了下,现在可以导出多个sheet页,支持合并单元格,支持设置行高和列宽,也有些小缺憾,比如,必须在写数据之前确定单元格列宽,注意,本方法不支持Excel 03,代码如下:
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.Writer; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.Enumeration; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.bean.ProductBo; /** * 1,支持合并单元格 * 2,支持设置列宽 * 3,支持设置行高 * 4,支持多sheet页 * @author 53873039oycg */ public class WriteExcel07_ByXML { public static void main(String[] args) throws Exception { WriteExcel07_ByXML t = new WriteExcel07_ByXML(); List<String> headList = t.generateExcelHeader(); long start=System.currentTimeMillis(); t.generateExcel(headList, 110000, 50000, "sheet", "f:/saveFile/temp"); System.out.println("本次导出耗时:"+(System.currentTimeMillis()-start)+"毫秒"); } public void generateExcel(List<String> headList, int totalSize, int pageSize, String sheetName, String tempFilePath) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); List<String> sheetRefList = new ArrayList<String>(); List<File> sheetFileList = new ArrayList<File>(); int totalSheet = totalSize / pageSize + (totalSize % pageSize == 0 ? 0 : 1); for (int i = 1; i <= totalSheet; i++) { XSSFSheet sheet = wb.createSheet(sheetName + i); String sheetRef = sheet.getPackagePart().getPartName().getName(); sheetRefList.add(sheetRef.substring(1)); File tmp = new File(tempFilePath + "/" + (sheetName + i) + ".xml"); sheetFileList.add(tmp); } File tmpFile = new File(tempFilePath + "/template2.xlsx"); String resultFile = tempFilePath + "/" + System.currentTimeMillis() + ".xlsx"; FileOutputStream os = new FileOutputStream(tmpFile); wb.write(os); os.close(); for (int i = 0; i < totalSheet; i++) { if (i == totalSheet - 1) { pageSize = totalSize - (totalSheet - 1) * pageSize; } List<ProductBo> dataList = generateRandData(pageSize); Writer fw = new OutputStreamWriter(new FileOutputStream( sheetFileList.get(i)), "UTF-8"); generateExcelSheet(headList, dataList, fw, styles); fw.close(); } FileOutputStream out = new FileOutputStream(resultFile); substituteAll(tmpFile, sheetFileList, sheetRefList, out); out.close(); // 删除临时文件 tmpFile.delete(); for (File file : sheetFileList) { file.delete(); } } private void generateExcelSheet(List<String> headList, List<ProductBo> dataList, Writer out, Map<String, XSSFCellStyle> styles) throws Exception { XSSFCellStyle stringStyle = styles.get("cell_string"); XSSFCellStyle longStyle = styles.get("cell_long"); XSSFCellStyle doubleStyle = styles.get("cell_double"); XSSFCellStyle dateStyle = styles.get("cell_date"); Calendar calendar = Calendar.getInstance(); SpreadsheetWriter sw = new SpreadsheetWriter(out); sw.beginWorkSheet(); sw.beginSetColWidth(); for (int i = 10, len = headList.size() - 2; i < len; i++) { sw.setColWidthBeforeSheet(i, 13); } sw.setColWidthBeforeSheet(headList.size() - 1, 16); sw.endSetColWidth(); sw.beginSheet(); // 表头 sw.insertRowWithheight(0, headList.size(), 25); int styleIndex = ((XSSFCellStyle) styles.get("sheet_title")).getIndex(); for (int i = 0, len = headList.size(); i < len; i++) { sw.createCell(i, headList.get(i), styleIndex); } sw.endWithheight(); int cellIndex = 0; for (int rownum = 1, len = dataList.size() + 1; rownum < len; rownum++) { cellIndex = 0; sw.insertRow(rownum); ProductBo bo = dataList.get(rownum - 1); Class<?> clz = bo.getClass(); Field[] fields = clz.getDeclaredFields(); Object val = null; Method fieldMethod = null; for (int k = 0, len2 = fields.length; k < len2; k++) { if ("serialVersionUID".equals(fields[k].getName())) { continue; } fieldMethod = (Method) clz.getMethod("get" + getMethodName(fields[k].getName())); fieldMethod.setAccessible(true);// 不进行安全检测 val = fieldMethod.invoke(bo); String typeName = fields[k].getGenericType().toString(); if (typeName.endsWith("int") || typeName.endsWith("nteger")) { sw.createCell(cellIndex, (Integer) val, longStyle.getIndex()); } else if (typeName.endsWith("ong")) { sw.createCell(cellIndex, (Long) val, longStyle.getIndex()); } else if (typeName.endsWith("ouble")) { sw.createCell(cellIndex, (Double) val, doubleStyle.getIndex()); } else if (typeName.endsWith("util.Date")) { calendar.setTime((java.util.Date) val); sw.createCell(cellIndex, calendar, dateStyle.getIndex()); } else if (typeName.endsWith("sql.Date")) { calendar.setTime((java.sql.Date) val); sw.createCell(cellIndex, calendar, dateStyle.getIndex()); } else { sw.createCell(cellIndex, val.toString(), stringStyle.getIndex()); } cellIndex++; } sw.endRow(); if (rownum % 2000 == 0) { out.flush(); } } sw.endSheet(); // 合并单元格 sw.beginMergerCell(); for (int i = 0, len = dataList.size() + 1; i < len; i++) { sw.setMergeCell(i, 8, i, 9); } sw.endMergerCell(); sw.endWorkSheet(); } // 表头 private List<String> generateExcelHeader() { List<String> headList = new ArrayList<String>(); headList.add("编号"); headList.add("省"); headList.add("市"); headList.add("产品编码"); headList.add("产品品牌"); headList.add("产品型号"); headList.add("产品成本"); headList.add("优惠价"); headList.add("当日销售数量"); headList.add("当日销售小计"); headList.add("本周销售数量"); headList.add("本周销售小计"); headList.add("本月销售数量"); headList.add("本月销售小计"); headList.add("是否结算"); headList.add("统计时间"); return headList; } private List<ProductBo> generateRandData(int num) { List<ProductBo> list = new ArrayList<ProductBo>(num * 3 / 2); for (int i = 1; i < num + 1; i++) { double price = new Random().nextInt(3000); ProductBo pb = new ProductBo(10000 + i, "北京", "北京", 2000 + i, "测试_S72" + i, "测试机型" + i, price, new Random().nextInt(5000), new Random().nextInt(30), price * new Random().nextInt(30), new Random().nextInt(100), price * new Random().nextInt(100), new Random().nextInt(300), price * new Random().nextInt(300), "是", new Date()); list.add(pb); } return list; } // 创建Excel样式 private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> stylesMap = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_string", style); XSSFCellStyle style2 = wb.createCellStyle(); style2.setDataFormat(fmt.getFormat("0")); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_long", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setDataFormat(fmt.getFormat("0.00")); style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_double", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setDataFormat(fmt.getFormat("yyyy-MM-dd")); style4.setAlignment(XSSFCellStyle.ALIGN_CENTER); style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_date", style4); XSSFCellStyle style5 = wb.createCellStyle(); style5.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setAlignment(XSSFCellStyle.ALIGN_CENTER); style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("sheet_title", style5); return stylesMap; } // 首字母大写 private String getMethodName(String fildeName) throws Exception { byte[] items = fildeName.getBytes(); items[0] = (byte) ((char) items[0] - 'a' + 'A'); return new String(items); } // 打包压缩 private void substituteAll(File zipfile, List<File> tmpfileList, List<String> entryList, OutputStream out) throws IOException { ZipFile zip = new ZipFile(zipfile); ZipOutputStream zos = new ZipOutputStream(out); Enumeration en = zip.entries(); while (en.hasMoreElements()) { ZipEntry ze = (ZipEntry) en.nextElement(); if (!entryList.contains(ze.getName())) { zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = zip.getInputStream(ze); copyStream(is, zos); is.close(); } } InputStream is = null; for (int i = 0, len = entryList.size(); i < len; i++) { zos.putNextEntry(new ZipEntry(entryList.get(i))); is = new FileInputStream(tmpfileList.get(i)); copyStream(is, zos); is.close(); } zos.close(); zip.close(); } private static void copyStream(InputStream in, OutputStream out) throws IOException { byte[] chunk = new byte[1024*10]; int count; while ((count = in.read(chunk)) >= 0) out.write(chunk, 0, count); } public int getTrueColumnNum(String address) { address = address.replaceAll("[^a-zA-Z]", "").toLowerCase(); char[] adds = address.toCharArray(); int base = 1; int total = 0; for (int i = adds.length - 1; i >= 0; i--) { total += (adds[i] - 'a' + 1) * base; base = 26 * base; } return total; } public static class SpreadsheetWriter { private final Writer _out; private int _rownum; public SpreadsheetWriter(Writer out) { this._out = out; } public void beginWorkSheet() throws IOException { this._out .write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"); } public void beginSheet() throws IOException { this._out.write("<sheetData>\n"); } public void endSheet() throws IOException { this._out.write("</sheetData>"); // 合并单元格 } public void endWorkSheet() throws IOException { this._out.write("</worksheet>"); } //插入行 不带高度 public void insertRow(int rownum) throws IOException { this._out.write("<row r=\"" + (rownum + 1) + "\">\n"); this._rownum = rownum; } public void endRow() throws IOException { this._out.write("</row>\n"); } //插入行且设置高度 public void insertRowWithheight(int rownum, int columnNum, double height) throws IOException { this._out.write("<row r=\"" + (rownum + 1) + "\" spans=\"1:" + columnNum + "\" ht=\"" + height + "\" customHeight=\"1\">\n"); this._rownum = rownum; } public void endWithheight() throws IOException { this._out.write("</row>\n"); } public void beginSetColWidth() throws IOException { this._out.write("<cols>\n"); } // 设置列宽 下标从0开始 public void setColWidthBeforeSheet(int columnIndex, double columnWidth) throws IOException { this._out.write("<col min=\"" + (columnIndex + 1) + "\" max=\"" + (columnIndex + 1) + "\" width=\"" + columnWidth + "\" customWidth=\"1\"/>\n"); } public void endSetColWidth() throws IOException { this._out.write("</cols>\n"); } public void beginMergerCell() throws IOException { this._out.write("<mergeCells>\n"); } public void endMergerCell() throws IOException { this._out.write("</mergeCells>\n"); } // 合并单元格 下标从0开始 public void setMergeCell(int beginColumn, int beginCell, int endColumn, int endCell) throws IOException { this._out.write("<mergeCell ref=\"" + getExcelName(beginCell + 1) + (beginColumn + 1) + ":" + getExcelName(endCell + 1) + (endColumn + 1) + "\"/>\n");// 列行:列行 } public void createCell(int columnIndex, String value, int styleIndex) throws IOException { String ref = new CellReference(this._rownum, columnIndex) .formatAsString(); this._out.write("<c r=\"" + ref + "\" t=\"inlineStr\""); if (styleIndex != -1) this._out.write(" s=\"" + styleIndex + "\""); this._out.write(">"); this._out.write("<is><t>" + value + "</t></is>"); this._out.write("</c>"); } public void createCell(int columnIndex, String value) throws IOException { createCell(columnIndex, value, -1); } public void createCell(int columnIndex, double value, int styleIndex) throws IOException { String ref = new CellReference(this._rownum, columnIndex) .formatAsString(); this._out.write("<c r=\"" + ref + "\" t=\"n\""); if (styleIndex != -1) this._out.write(" s=\"" + styleIndex + "\""); this._out.write(">"); this._out.write("<v>" + value + "</v>"); this._out.write("</c>"); } public void createCell(int columnIndex, double value) throws IOException { createCell(columnIndex, value, -1); } public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); } //10 进制转26进制 private String getExcelName(int i) { char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray(); StringBuilder sb = new StringBuilder(); while (i > 0) { sb.append(allChar[i % 26 - 1]); i /= 26; } return sb.reverse().toString(); } } }
Bean如下:
import java.util.Date; public class ProductBo { private long id; private String provinceName; private String cityName; private long productNo; private String brandName; private String productName; private double costPrice; private double salePrice; private long todayNum; private double todayTotal; private long weekNum; private double weekTotal; private long monthNum; private double monthTotal; private String isSale; private Date doneDate; public ProductBo(long id, String provinceName, String cityName, long productNo, String brandName, String productName, double costPrice, double salePrice, long todayNum, double todayTotal, long weekNum, double weekTotal, long monthNum, double monthTotal, String isSale, Date doneDate) { super(); this.id = id; this.provinceName = provinceName; this.cityName = cityName; this.productNo = productNo; this.brandName = brandName; this.productName = productName; this.costPrice = costPrice; this.salePrice = salePrice; this.todayNum = todayNum; this.todayTotal = todayTotal; this.weekNum = weekNum; this.weekTotal = weekTotal; this.monthNum = monthNum; this.monthTotal = monthTotal; this.isSale = isSale; this.doneDate = doneDate; } public long getId() { return id; } public void setId(long id) { this.id = id; } public String getProvinceName() { return provinceName; } public void setProvinceName(String provinceName) { this.provinceName = provinceName; } public String getCityName() { return cityName; } public void setCityName(String cityName) { this.cityName = cityName; } public long getProductNo() { return productNo; } public void setProductNo(long productNo) { this.productNo = productNo; } public String getBrandName() { return brandName; } public void setBrandName(String brandName) { this.brandName = brandName; } public String getProductName() { return productName; } public void setProductName(String productName) { this.productName = productName; } public double getCostPrice() { return costPrice; } public void setCostPrice(double costPrice) { this.costPrice = costPrice; } public double getSalePrice() { return salePrice; } public void setSalePrice(double salePrice) { this.salePrice = salePrice; } public long getTodayNum() { return todayNum; } public void setTodayNum(long todayNum) { this.todayNum = todayNum; } public double getTodayTotal() { return todayTotal; } public void setTodayTotal(double todayTotal) { this.todayTotal = todayTotal; } public long getWeekNum() { return weekNum; } public void setWeekNum(long weekNum) { this.weekNum = weekNum; } public double getWeekTotal() { return weekTotal; } public void setWeekTotal(double weekTotal) { this.weekTotal = weekTotal; } public long getMonthNum() { return monthNum; } public void setMonthNum(long monthNum) { this.monthNum = monthNum; } public double getMonthTotal() { return monthTotal; } public void setMonthTotal(double monthTotal) { this.monthTotal = monthTotal; } public String getIsSale() { return isSale; } public void setIsSale(String isSale) { this.isSale = isSale; } public Date getDoneDate() { return doneDate; } public void setDoneDate(Date doneDate) { this.doneDate = doneDate; } }
结果为:
如需更高级的功能,请自行编写代码,方法如下:
在Excel中设置自己想要的效果,把Excel后缀名修改为.zip,解压,在解压文件夹找到xl/worksheets文件夹,其中的sheet.xml就是Excel的数据。如下所示:
本文系原创,请尊重本人的劳动成果,转载请注明原处,谢谢。
全文完。
相关推荐
在本案例中,我们关注的是如何使用 Apache POI 库来导出 Excel 文件,特别是根据模板导出和简单列表导出。下面将详细介绍这个过程。 1. **Apache POI 概述** Apache POI 提供了 Java API 来读写 Microsoft Office ...
在本示例中,我们将探讨如何使用Apache POI库从数据库导出数据到Excel表格,这在数据分析、报告生成或批量数据处理场景中非常常见。Apache POI是一个开源项目,它允许Java开发者创建、修改和显示Microsoft Office...
使用POI筛选字段导出Excel,以及条件查询和指定数据导出
POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI...
在Excel方面,POI支持HSSF(Horizontally Stored SpreadSheet Format)和XSSF(XML Spreadsheet Format),分别对应旧版的BIFF8格式和新式的OOXML格式。 二、准备工作 在开始之前,确保已经将Apache POI的jar文件...
以下是一个简化的示例,演示了如何使用POI导出Excel: ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io....
基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip...
apache poi根据模板导出excel的模版
这个项目"Java Struts2+poi插件 实现导出Excel"就是结合这两个工具,为用户提供一个功能,能够将数据导出到Excel格式的文件中。 首先,让我们详细了解一下Struts2。Struts2的核心是Action,它负责接收请求、处理...
本教程将详细介绍如何利用Apache POI库来实现使用多个Sheet(工作簿)导出一个Excel文件的功能。 一、Apache POI简介 Apache POI 是一个开源项目,提供API来读取、写入和修改Microsoft Office文件格式,如Word(DOC...
springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式...
本教程将详细介绍如何利用Apache POI库实现从数据库导出数据到Excel的工作流程。 首先,我们需要理解Apache POI的基本概念。POI提供了HSSF(Horizontally Stored Spreadsheet Format)用于处理老版本的Excel文件(....
- 设置样式、合并单元格等高级特性,使导出的Excel文件更加美观。 - 将Workbook对象写入到HttpServletResponse的OutputStream中,完成导出。 5. **代码示例** - 创建一个`ExcelImportService`服务,该服务包含`...
Java POI 实现 Excel 导入导出 Java POI 是一个流行的 Java 库,用于处理 Microsoft Office 文件格式,包括 Excel 文件。在本文中,我们将详细介绍如何使用 Java POI 实现 Excel 导入导出功能。 1. 什么是 Java ...
首先,`poi-ooxml-schemas-3.15-beta1.jar`是POI库中的XML schema定义,用于解析和创建符合Office Open XML标准的Excel文件。这个jar包包含了微软Office文件格式的XML模式定义,使得POI可以理解和操作.xlsx文件。 ...
本项目“poi多线程大数据导出excel文件”提供了一个解决方案,利用多线程来提高Excel的大数据导出效率。 Apache POI 3.1版本是较早的版本,而项目中使用了更新的4.1版本,这意味着它可能利用了更多优化和新特性。在...
在这个特定的例子中,我们将讨论如何使用POI库基于一个Excel模板文件循环输出数据行,并将结果导出为新的Excel文件。 首先,我们需要理解POI库的基本概念。POI提供了HSSF(Horizontally SpreadSheet Format)和XSSF...
### POI的EXCEL导出,自动换行 在日常工作中,经常需要处理大量的数据导入导出任务,尤其是在企业级应用开发中,Excel文件的处理成为了一项必不可少的能力。Apache POI项目提供了一系列用于读写Microsoft Office...
Java基于POI的导入导出excel功能,附带测试功能,项目基于Maven,导入即可用,带测试Main方法提供例子。 用于导入导出Excel的Util包,基于Java的POI。可将List导出成Excel,或读取Excel成List、Map,Object>,很方便...
Apache POI导入和导出Excel文件