最近想使用写文本的方式导出Excel,首先想到的是Freemarker,可惜Excel 2007保存为(.*xml)时候报错了,保存为2003(.*xml)Excel打开报文件格式错误。如下:
而是转使用Jxls的:
<jx:forEach items="${dataList}" var="bo"> .... </jx:forEach>
发现导出2万条数据直接oom了,而是放弃了这种方式。
在poi自带的例子中发现了一个拼接xml导出Excel的代码,如下:
本人稍微修改了下,代码如下:
package com.excel; 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)只适用Excel 07+ * 2)单Sheet * 3)不能调整Excel行高和列宽 * 4)不支持合并单元格 * 5)不支持复杂类型 */ public class WriteExcel07_S2_Test { public static void main(String[] args) throws Exception { WriteExcel07_S2_Test t = new WriteExcel07_S2_Test(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("测试导出"); Map<String, XSSFCellStyle> styles = createStyles(wb); String sheetRef = sheet.getPackagePart().getPartName().getName(); File tmpFile = new File("f:/saveFile/temp/template.xlsx"); String resultFile = "f:/saveFile/temp/" + System.currentTimeMillis() + ".xlsx"; if (!tmpFile.exists()) { tmpFile.createNewFile(); } FileOutputStream os = new FileOutputStream(tmpFile); wb.write(os); os.close(); List<String> headList = t.generateExcelHeader(); List<ProductBo> dataList = t.generateRandData(2000); long start = System.currentTimeMillis(); File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), "UTF-8"); t.generateExcel(headList, dataList, fw, styles); fw.close(); FileOutputStream out = new FileOutputStream(resultFile); t.substitute(tmpFile, tmp, sheetRef.substring(1), out); out.close(); tmpFile.delete(); System.out.println(System.currentTimeMillis() - start); } //拼接XML private void generateExcel(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.beginSheet(); //表头 sw.insertRow(0); 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.endRow(); 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(); } sw.endSheet(); } //表头 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 substitute(File zipfile, File tmpfile, String entry, 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 (!ze.getName().equals(entry)) { zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = zip.getInputStream(ze); copyStream(is, zos); is.close(); } } zos.putNextEntry(new ZipEntry(entry)); InputStream is = new FileInputStream(tmpfile); copyStream(is, zos); is.close(); zos.close(); zip.close(); } private static void copyStream(InputStream in, OutputStream out) throws IOException { byte[] chunk = new byte[1024]; int count; while ((count = in.read(chunk)) >= 0) out.write(chunk, 0, count); } public static class SpreadsheetWriter { private final Writer _out; private int _rownum; public SpreadsheetWriter(Writer out) { this._out = out; } public void beginSheet() throws IOException { this._out .write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"); this._out.write("<sheetData>\n"); } public void endSheet() throws IOException { this._out.write("</sheetData>"); 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 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); } } }
bean:
package com.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; } }
结果为:
调整格式后:
这种方式导出几万还是很快的,缺点也有很多:
1)只适用Excel 07+。
2)单Sheet。
3)不能调整Excel行高和列宽。
4)不支持合并单元格。
5)不支持复杂类型。
上述的缺点没发现解决方法,欢迎指教。
全文完。
相关推荐
poi基于模板导出excel,poi基于模板导出excelpoi,基于模板导出excel
使用poi从数据库导出excel表的示例
使用POI筛选字段导出Excel,以及条件查询和指定数据导出
springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式Excel模板,完整项目,导出即用。springboot+poi导出指定格式...
可以运行的POI导出Excel文件实例,里面有两种方法,一个是Servlet,一个是main
poi读取大文件Excel,使用xml格式解析,速度实测50mb文件13s,可指定sheet页内容,带工具类和测试类
poi excel poi excel poi excel poi excel poi excel poi excel poi excel poi excel
Poi导出Excel工具类 支持大数据量 多sheet页方式 已经封装好,直接拿来调用里面的方法传入参数就行 很简单
利用POI实现数据库导出Excel,压缩文件里包括例子源代码、poi.jar包、文档代码(有注释)
此代码包括poi导出excel的所有jar,自定义Excel模板后,将数据导入其中
使用poi方式导出excel,支持将多个工作簿合成一个Excel进行导出
利用纯struts2.0+poi插件 实现导出excel
基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip基于SpringBoot和POI实现单线程和多线程导出Excel.zip...
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...
用poi根据模板导出excel的帮助类,支持xls和xlsx两种格式,支持上万数据导出不会内存移除,代码有注释。
POI使用excel模板循环输出行到文件并导出的一个小例子
SpringBoot +Mybatis +POI导入、导出Excel文件 简单清晰几个类,处理SpringBoot 导入导出excel doc目录下 是mysql数据库脚本 和 导入的模板
Apache POI导入和导出Excel文件
亲测 poi 导出word 导出excel项目 导入 myeclipse 可运行
对大数据量的导出excel,用多线程,用倒数计数器对文件进行生成,使用poi,可以支持大数据量的生成,项目中使用的poi是3.1的,上传的是4.1的。