java如何将数据写入excel表格_java快速写入数据到excel表格

1 packagecom.service;2

3 import java.io.*;4 import java.util.*;5 importjava.util.zip.ZipEntry;6 importjava.util.zip.ZipFile;7 importjava.util.zip.ZipOutputStream;8

9 importorg.apache.poi.ss.usermodel.DateUtil;10 importorg.apache.poi.ss.usermodel.IndexedColors;11 importorg.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-218 * Time: 上午10:3019 * To change this template use File | Settings | File Templates.20 */

21 public classBigGridDemo {22 public static void main(String[] args) throwsException {23

24 //Step 1. Create a template file. Setup sheets and workbook-level objects such as25 //cell styles, number formats, etc.

26 Date date1 = newDate();27 System.out.print("开始执行：" + date1.getTime()+"\n");28 XSSFWorkbook wb = newXSSFWorkbook();29 XSSFSheet sheet = wb.createSheet("Big Grid");30

31 Map 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 = newFileWriter(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 MapcreateStyles(XSSFWorkbook wb) {58 Map styles = new HashMap();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 returnstyles;90 }91

92 private static void generate(Writer out, Map styles) throwsException {93

94 Random rnd = newRandom();95 Calendar calendar =Calendar.getInstance();96

97 SpreadsheetWriter sw = newSpreadsheetWriter(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 *@paramzipfile the template file130 *@paramtmpfile the XML file with the sheet data131 *@paramentry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml132 *@paramout the stream to write the result to133 */

134 private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throwsIOException {135 ZipFile zip = newZipFile(zipfile);136

137 ZipOutputStream zos = newZipOutputStream(out);138

139 @SuppressWarnings("unchecked")140 Enumeration en = (Enumeration) zip.entries();141 while(en.hasMoreElements()) {142 ZipEntry ze =en.nextElement();143 if (!ze.getName().equals(entry)) {144 zos.putNextEntry(newZipEntry(ze.getName()));145 InputStream is =zip.getInputStream(ze);146 copyStream(is, zos);147 is.close();148 }149 }150 zos.putNextEntry(newZipEntry(entry));151 InputStream is = newFileInputStream(tmpfile);152 copyStream(is, zos);153 is.close();154

155 zos.close();156 }157

158 private static void copyStream(InputStream in, OutputStream out) throwsIOException {159 byte[] chunk = new byte[1024];160 intcount;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 classSpreadsheetWriter {171 private finalWriter _out;172 private int_rownum;173

174 publicSpreadsheetWriter(Writer out) {175 _out =out;176 }177

178 public void beginSheet() throwsIOException {179 _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +

180 "");181 _out.write("\n");182 }183

184 public void endSheet() throwsIOException {185 _out.write("");186 _out.write("");187 }188

189 /**

190 * Insert a new row191 *192 *@paramrownum 0-based row number193 */

194 public void insertRow(int rownum) throwsIOException {195 _out.write("\n");196 this._rownum =rownum;197 }198

199 /**

200 * Insert row end marker201 */

202 public void endRow() throwsIOException {203 _out.write("\n");204 }205

206 public void createCell(int columnIndex, String value, int styleIndex) throwsIOException {207 String ref = newCellReference(_rownum, columnIndex).formatAsString();208 _out.write("");211 _out.write("" + value + "");212 _out.write("");213 }214

215 public void createCell(int columnIndex, String value) throwsIOException {216 createCell(columnIndex, value, -1);217 }218

219 public void createCell(int columnIndex, double value, int styleIndex) throwsIOException {220 String ref = newCellReference(_rownum, columnIndex).formatAsString();221 _out.write("");224 _out.write("" + value + "");225 _out.write("");226 }227

228 public void createCell(int columnIndex, double value) throwsIOException {229 createCell(columnIndex, value, -1);230 }231

232 public void createCell(int columnIndex, Calendar value, int styleIndex) throwsIOException {233 createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);234 }235 }236

237 }