java快速写入数据到excel表格
1 package com.service; 2 3 import java.io.*; 4 import java.util.*; 5 import java.util.zip.ZipEntry; 6 import java.util.zip.ZipFile; 7 import java.util.zip.ZipOutputStream; 8 9 import org.apache.poi.ss.usermodel.DateUtil; 10 import org.apache.poi.ss.usermodel.IndexedColors; 11 import org.apache.poi.ss.util.CellReference; 12 import org.apache.poi.xssf.usermodel.*; 13 14 /** 15 * Created with IntelliJ IDEA. 16 * User: 小码工 17 * Date: 13-8-2 18 * Time: 上午10:30 19 * To change this template use File | Settings | File Templates. 20 */ 21 public class BigGridDemo { 22 public static void main(String[] args) throws Exception { 23 24 // Step 1. Create a template file. Setup sheets and workbook-level objects such as 25 // cell styles, number formats, etc. 26 Date date1 = new Date(); 27 System.out.print("开始执行：" + date1.getTime()+"\n"); 28 XSSFWorkbook wb = new XSSFWorkbook(); 29 XSSFSheet sheet = wb.createSheet("Big Grid"); 30 31 Map<String, XSSFCellStyle> styles = createStyles(wb); 32 //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml 33 String sheetRef = sheet.getPackagePart().getPartName().getName(); 34 35 //save the template 36 FileOutputStream os = new FileOutputStream("template.xlsx"); 37 wb.write(os); 38 os.close(); 39 40 //Step 2. Generate XML file. 41 File tmp = File.createTempFile("sheet", ".xml"); 42 Writer fw = new FileWriter(tmp); 43 generate(fw, styles); 44 fw.close(); 45 46 //Step 3. Substitute the template entry with the generated data 47 FileOutputStream out = new FileOutputStream("big-grid.xlsx"); 48 substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); 49 out.close(); 50 System.out.print("执行结束：" + new Date().getTime()+"\n"); 51 System.out.print("一百万条数据数据写入excel所需时间为：" + (new Date().getTime() - date1.getTime()) / 1000 + "秒！"); 52 } 53 54 /** 55 * Create a library of cell styles. 56 */ 57 private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { 58 Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); 59 XSSFDataFormat fmt = wb.createDataFormat(); 60 61 XSSFCellStyle style1 = wb.createCellStyle(); 62 style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 63 style1.setDataFormat(fmt.getFormat("0.0%")); 64 styles.put("percent", style1); 65 66 XSSFCellStyle style2 = wb.createCellStyle(); 67 style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); 68 style2.setDataFormat(fmt.getFormat("0.0X")); 69 styles.put("coeff", style2); 70 71 XSSFCellStyle style3 = wb.createCellStyle(); 72 style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 73 style3.setDataFormat(fmt.getFormat("$#,##0.00")); 74 styles.put("currency", style3); 75 76 XSSFCellStyle style4 = wb.createCellStyle(); 77 style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 78 style4.setDataFormat(fmt.getFormat("mmm dd")); 79 styles.put("date", style4); 80 81 XSSFCellStyle style5 = wb.createCellStyle(); 82 XSSFFont headerFont = wb.createFont(); 83 headerFont.setBold(true); 84 style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); 85 style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); 86 style5.setFont(headerFont); 87 styles.put("header", style5); 88 89 return styles; 90 } 91 92 private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception { 93 94 Random rnd = new Random(); 95 Calendar calendar = Calendar.getInstance(); 96 97 SpreadsheetWriter sw = new SpreadsheetWriter(out); 98 sw.beginSheet(); 99 100 //insert header row 101 sw.insertRow(0); 102 int styleIndex = styles.get("header").getIndex(); 103 sw.createCell(0, "Title", styleIndex); 104 sw.createCell(1, "%Change", styleIndex); 105 sw.createCell(2, "Ratio", styleIndex); 106 sw.createCell(3, "Expenses", styleIndex); 107 sw.createCell(4, "Date", styleIndex); 108 109 sw.endRow(); 110 111 //write data rows 112 for (int rownum = 1; rownum < 1000000; rownum++) { 113 sw.insertRow(rownum); 114 115 sw.createCell(0, "Hello, " + rownum + "!"); 116 sw.createCell(1, (double) rnd.nextInt(100) / 100, styles.get("percent").getIndex()); 117 sw.createCell(2, (double) rnd.nextInt(10) / 10, styles.get("coeff").getIndex()); 118 sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex()); 119 sw.createCell(4, calendar, styles.get("date").getIndex()); 120 121 sw.endRow(); 122 123 calendar.roll(Calendar.DAY_OF_YEAR, 1); 124 } 125 sw.endSheet(); 126 } 127 128 /** 129 * @param zipfile the template file 130 * @param tmpfile the XML file with the sheet data 131 * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml 132 * @param out the stream to write the result to 133 */ 134 private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException { 135 ZipFile zip = new ZipFile(zipfile); 136 137 ZipOutputStream zos = new ZipOutputStream(out); 138 139 @SuppressWarnings("unchecked") 140 Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries(); 141 while (en.hasMoreElements()) { 142 ZipEntry ze = en.nextElement(); 143 if (!ze.getName().equals(entry)) { 144 zos.putNextEntry(new ZipEntry(ze.getName())); 145 InputStream is = zip.getInputStream(ze); 146 copyStream(is, zos); 147 is.close(); 148 } 149 } 150 zos.putNextEntry(new ZipEntry(entry)); 151 InputStream is = new FileInputStream(tmpfile); 152 copyStream(is, zos); 153 is.close(); 154 155 zos.close(); 156 } 157 158 private static void copyStream(InputStream in, OutputStream out) throws IOException { 159 byte[] chunk = new byte[1024]; 160 int count; 161 while ((count = in.read(chunk)) >= 0) { 162 out.write(chunk, 0, count); 163 } 164 } 165 166 /** 167 * Writes spreadsheet data in a Writer. 168 * (YK: in future it may evolve in a full-featured API for streaming data in Excel) 169 */ 170 public static class SpreadsheetWriter { 171 private final Writer _out; 172 private int _rownum; 173 174 public SpreadsheetWriter(Writer out) { 175 _out = out; 176 } 177 178 public void beginSheet() throws IOException { 179 _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + 180 "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"); 181 _out.write("<sheetData>\n"); 182 } 183 184 public void endSheet() throws IOException { 185 _out.write("</sheetData>"); 186 _out.write("</worksheet>"); 187 } 188 189 /** 190 * Insert a new row 191 * 192 * @param rownum 0-based row number 193 */ 194 public void insertRow(int rownum) throws IOException { 195 _out.write("<row r=\"" + (rownum + 1) + "\">\n"); 196 this._rownum = rownum; 197 } 198 199 /** 200 * Insert row end marker 201 */ 202 public void endRow() throws IOException { 203 _out.write("</row>\n"); 204 } 205 206 public void createCell(int columnIndex, String value, int styleIndex) throws IOException { 207 String ref = new CellReference(_rownum, columnIndex).formatAsString(); 208 _out.write("<c r=\"" + ref + "\" t=\"inlineStr\""); 209 if (styleIndex != -1) _out.write(" s=\"" + styleIndex + "\""); 210 _out.write(">"); 211 _out.write("<is><t>" + value + "</t></is>"); 212 _out.write("</c>"); 213 } 214 215 public void createCell(int columnIndex, String value) throws IOException { 216 createCell(columnIndex, value, -1); 217 } 218 219 public void createCell(int columnIndex, double value, int styleIndex) throws IOException { 220 String ref = new CellReference(_rownum, columnIndex).formatAsString(); 221 _out.write("<c r=\"" + ref + "\" t=\"n\""); 222 if (styleIndex != -1) _out.write(" s=\"" + styleIndex + "\""); 223 _out.write(">"); 224 _out.write("<v>" + value + "</v>"); 225 _out.write("</c>"); 226 } 227 228 public void createCell(int columnIndex, double value) throws IOException { 229 createCell(columnIndex, value, -1); 230 } 231 232 public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { 233 createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); 234 } 235 } 236 237 }
需要jar：poi-ooxml-3.5-beta6.jar,poi-ooxml-schemas-3.7-20101029.jar,xmlbeans-2.3.0.jar，poi-3.7-20101029.jar等jar。
