依赖的jar
1 | <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> |
对应的操作对象简介
1.HSSFWorkbook 读取/写入.xls文件
2.XSSFWorkbook 读写 .xlsx文件
3.SXSSFWorkbook 读取/写入.xls 与.xlsx
简要使用
1.读取操作顺序:
1> 将需要读取的文件/流 传入,产生对应的Workbook对象.
2> 取出每个sheet.根据行列数遍历取值
2.写入操作顺序:
1> 使用new Workbook()产生新的工作薄对象.
2> 创建相应的sheet并插入数据.
3> 使用workbook.writ(流)的方法,写出数据.
4> 关闭流,关闭workbook.
示例代码
1.HSSFWorkbook 写入1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140// create a new file
FileOutputStream out = new FileOutputStream("workbook.xls");
// create a new workbook
Workbook wb = new HSSFWorkbook();
// create a new sheet
Sheet s = wb.createSheet();
// declare a row object reference
Row r = null;
// declare a cell object reference
Cell c = null;
// create 3 cell styles
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
CellStyle cs3 = wb.createCellStyle();
DataFormat df = wb.createDataFormat();
// create 2 fonts objects
Font f = wb.createFont();
Font f2 = wb.createFont();
//set font 1 to 12 point type
f.setFontHeightInPoints((short) 12);
//make it blue
f.setColor( (short)0xc );
// make it bold
//arial is the default font
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
//set font 2 to 10 point type
f2.setFontHeightInPoints((short) 10);
//make it red
f2.setColor( (short)Font.COLOR_RED );
//make it bold
f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
f2.setStrikeout( true );
//set cell stlye
cs.setFont(f);
//set the cell format
cs.setDataFormat(df.getFormat("#,##0.0"));
//set a thin border
cs2.setBorderBottom(cs2.BORDER_THIN);
//fill w fg fill color
cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND);
//set the cell format to text see DataFormat for a full list
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
// set the font
cs2.setFont(f2);
// set the sheet name in Unicode
wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " +
"\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430" );
// in case of plain ascii
// wb.setSheetName(0, "HSSF Test");
// create a sheet with 30 rows (0-29)
int rownum;
for (rownum = (short) 0; rownum < 30; rownum++)
{
// create a row
r = s.createRow(rownum);
// on every other row
if ((rownum % 2) == 0)
{
// make the row height bigger (in twips - 1/20 of a point)
r.setHeight((short) 0x249);
}
//r.setRowNum(( short ) rownum);
// create 10 cells (0-9) (the += 2 becomes apparent later
for (short cellnum = (short) 0; cellnum < 10; cellnum += 2)
{
// create a numeric cell
c = r.createCell(cellnum);
// do some goofy math to demonstrate decimals
c.setCellValue(rownum * 10000 + cellnum
+ (((double) rownum / 1000)
+ ((double) cellnum / 10000)));
String cellValue;
// create a string cell (see why += 2 in the
c = r.createCell((short) (cellnum + 1));
// on every other row
if ((rownum % 2) == 0)
{
// set this cell to the first cell style we defined
c.setCellStyle(cs);
// set the cell's string value to "Test"
c.setCellValue( "Test" );
}
else
{
c.setCellStyle(cs2);
// set the cell's string value to "\u0422\u0435\u0441\u0442"
c.setCellValue( "\u0422\u0435\u0441\u0442" );
}
// make this column a bit wider
s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
}
}
//draw a thick black border on the row at the bottom using BLANKS
// advance 2 rows
rownum++;
rownum++;
r = s.createRow(rownum);
// define the third style to be the default
// except with a thick black border at the bottom
cs3.setBorderBottom(cs3.BORDER_THICK);
//create 50 cells
for (short cellnum = (short) 0; cellnum < 50; cellnum++)
{
//create a blank type cell (no value)
c = r.createCell(cellnum);
// set it to the thick black border style
c.setCellStyle(cs3);
}
//end draw thick black border
// demonstrate adding/naming and deleting a sheet
// create a sheet, set its title then delete it
s = wb.createSheet();
wb.setSheetName(1, "DeletedSheet");
wb.removeSheetAt(1);
//end deleted sheet
// write the workbook to the output stream
// close our file (don't blow out our file handles
wb.write(out);
out.close();
2.HSSFWorkbook读取/修改.xls1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269package org.apache.poi.hssf.usermodel.examples;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Locale;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* File for HSSF testing/examples
*
* THIS IS NOT THE MAIN HSSF FILE!! This is a utility for testing functionality.
* It does contain sample API usage that may be educational to regular API
* users.
*/
public final class HSSFReadWrite {
/**
* creates an {@link HSSFWorkbook} with the specified OS filename.
*/
private static HSSFWorkbook readFile(String filename) throws IOException {
FileInputStream fis = new FileInputStream(filename);
try {
return new HSSFWorkbook(fis); // NOSONAR - should not be closed here
} finally {
fis.close();
}
}
/**
* given a filename this outputs a sample sheet with just a set of
* rows/cells.
*/
private static void testCreateSampleSheet(String outputFilename) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet s = wb.createSheet();
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFCellStyle cs3 = wb.createCellStyle();
HSSFFont f = wb.createFont();
HSSFFont f2 = wb.createFont();
f.setFontHeightInPoints((short) 12);
f.setColor((short) 0xA);
f.setBold(true);
f2.setFontHeightInPoints((short) 10);
f2.setColor((short) 0xf);
f2.setBold(true);
cs.setFont(f);
cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cs2.setBorderBottom(BorderStyle.THIN);
cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cs2.setFillForegroundColor((short) 0xA);
cs2.setFont(f2);
wb.setSheetName(0, "HSSF Test");
int rownum;
for (rownum = 0; rownum < 300; rownum++) {
HSSFRow r = s.createRow(rownum);
if ((rownum % 2) == 0) {
r.setHeight((short) 0x249);
}
for (int cellnum = 0; cellnum < 50; cellnum += 2) {
HSSFCell c = r.createCell(cellnum);
c.setCellValue(rownum * 10000 + cellnum
+ (((double) rownum / 1000) + ((double) cellnum / 10000)));
if ((rownum % 2) == 0) {
c.setCellStyle(cs);
}
c = r.createCell(cellnum + 1);
c.setCellValue(new HSSFRichTextString("TEST"));
// 50 characters divided by 1/20th of a point
s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
if ((rownum % 2) == 0) {
c.setCellStyle(cs2);
}
}
}
// draw a thick black border on the row at the bottom using BLANKS
rownum++;
rownum++;
HSSFRow r = s.createRow(rownum);
cs3.setBorderBottom(BorderStyle.THICK);
for (int cellnum = 0; cellnum < 50; cellnum++) {
HSSFCell c = r.createCell(cellnum);
c.setCellStyle(cs3);
}
s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));
// end draw thick black border
// create a sheet, set its title then delete it
wb.createSheet();
wb.setSheetName(1, "DeletedSheet");
wb.removeSheetAt(1);
// end deleted sheet
FileOutputStream out = new FileOutputStream(outputFilename);
try {
wb.write(out);
} finally {
out.close();
}
} finally {
wb.close();
}
}
/**
* Method main
*
* Given 1 argument takes that as the filename, inputs it and dumps the
* cell values/types out to sys.out.<br/>
*
* given 2 arguments where the second argument is the word "write" and the
* first is the filename - writes out a sample (test) spreadsheet
* see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/>
*
* given 2 arguments where the first is an input filename and the second
* an output filename (not write), attempts to fully read in the
* spreadsheet and fully write it out.<br/>
*
* given 3 arguments where the first is an input filename and the second an
* output filename (not write) and the third is "modify1", attempts to read in the
* spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to
* "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you
* take the output from the write test, you'll have a valid scenario.
*/
public static void main(String[] args) {
if (args.length < 1) {
System.err.println("At least one argument expected");
return;
}
String fileName = args[0];
try {
if (args.length < 2) {
HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
try {
System.out.println("Data dump:\n");
for (int k = 0; k < wb.getNumberOfSheets(); k++) {
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows
+ " row(s).");
for (int r = 0; r < rows; r++) {
HSSFRow row = sheet.getRow(r);
if (row == null) {
continue;
}
System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s).");
for (int c = 0; c < row.getLastCellNum(); c++) {
HSSFCell cell = row.getCell(c);
String value;
if(cell != null) {
switch (cell.getCellTypeEnum()) {
case FORMULA:
value = "FORMULA value=" + cell.getCellFormula();
break;
case NUMERIC:
value = "NUMERIC value=" + cell.getNumericCellValue();
break;
case STRING:
value = "STRING value=" + cell.getStringCellValue();
break;
case BLANK:
value = "<BLANK>";
break;
case BOOLEAN:
value = "BOOLEAN value-" + cell.getBooleanCellValue();
break;
case ERROR:
value = "ERROR value=" + cell.getErrorCellValue();
break;
default:
value = "UNKNOWN value of type " + cell.getCellTypeEnum();
}
System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE="
+ value);
}
}
}
}
} finally {
wb.close();
}
} else if (args.length == 2) {
if (args[1].toLowerCase(Locale.ROOT).equals("write")) {
System.out.println("Write mode");
long time = System.currentTimeMillis();
HSSFReadWrite.testCreateSampleSheet(fileName);
System.out.println("" + (System.currentTimeMillis() - time)
+ " ms generation time");
} else {
System.out.println("readwrite test");
HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
try {
FileOutputStream stream = new FileOutputStream(args[1]);
try {
wb.write(stream);
} finally {
stream.close();
}
} finally {
wb.close();
}
}
} else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) {
// delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
try {
HSSFSheet sheet = wb.getSheetAt(0);
for (int k = 0; k < 25; k++) {
HSSFRow row = sheet.getRow(k);
sheet.removeRow(row);
}
for (int k = 74; k < 100; k++) {
HSSFRow row = sheet.getRow(k);
sheet.removeRow(row);
}
HSSFRow row = sheet.getRow(39);
HSSFCell cell = row.getCell(3);
cell.setCellValue("MODIFIED CELL!!!!!");
FileOutputStream stream = new FileOutputStream(args[1]);
try {
wb.write(stream);
} finally {
stream.close();
}
} finally {
wb.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
HSSFWorkbook不使用SAX 来解析例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88/**
* This example shows how to use the event API for reading a file.
*/
public class EventExample
implements HSSFListener
{
private SSTRecord sstrec;
/**
* This method listens for incoming records and handles them as required.
* @param record The record that was found while reading.
*/
public void processRecord(Record record)
{
switch (record.getSid())
{
// the BOFRecord can represent either the beginning of a sheet or the workbook
case BOFRecord.sid:
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK)
{
System.out.println("Encountered workbook");
// assigned to the class level member
} else if (bof.getType() == bof.TYPE_WORKSHEET)
{
System.out.println("Encountered sheet reference");
}
break;
case BoundSheetRecord.sid:
BoundSheetRecord bsr = (BoundSheetRecord) record;
System.out.println("New sheet named: " + bsr.getSheetname());
break;
case RowRecord.sid:
RowRecord rowrec = (RowRecord) record;
System.out.println("Row found, first column at "
+ rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
System.out.println("Cell found with value " + numrec.getValue()
+ " at row " + numrec.getRow() + " and column " + numrec.getColumn());
break;
// SSTRecords store a array of unique strings used in Excel.
case SSTRecord.sid:
sstrec = (SSTRecord) record;
for (int k = 0; k < sstrec.getNumUniqueStrings(); k++)
{
System.out.println("String table value " + k + " = " + sstrec.getString(k));
}
break;
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord) record;
System.out.println("String cell found with value "
+ sstrec.getString(lrec.getSSTIndex()));
break;
}
}
/**
* Read an excel file and spit out what we find.
*
* @param args Expect one argument that is the file to read.
* @throws IOException When there is an error processing the file.
*/
public static void main(String[] args) throws IOException
{
// create a new file input stream with the input file specified
// at the command line
FileInputStream fin = new FileInputStream(args[0]);
// create a new org.apache.poi.poifs.filesystem.Filesystem
POIFSFileSystem poifs = new POIFSFileSystem(fin);
// get the Workbook (excel part) stream in a InputStream
InputStream din = poifs.createDocumentInputStream("Workbook");
// construct out HSSFRequest object
HSSFRequest req = new HSSFRequest();
// lazy listen for ALL records with the listener shown above
req.addListenerForAllRecords(new EventExample());
// create our event factory
HSSFEventFactory factory = new HSSFEventFactory();
// process our events based on the document input stream
factory.processEvents(req, din);
// once all the events are processed close our file input stream
fin.close();
// and our document input stream (don't want to leak these!)
din.close();
System.out.println("done.");
}
}XSSFWorkbook使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
public class ExampleEventUserModel {
public void processOneSheet(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// To look up the Sheet Name / Sheet Order / rID,
// you need to process the core Workbook stream.
// Normally it's of the form rId# or rSheet#
InputStream sheet2 = r.getSheet("rId2");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processAllSheets(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while(sheets.hasNext()) {
System.out.println("Processing new sheet:\n");
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
System.out.println("");
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// Print the cell reference
System.out.print(attributes.getValue("r") + " - ");
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
// Output after we've seen the string contents
if(name.equals("v")) {
System.out.println(lastContents);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) throws Exception {
ExampleEventUserModel example = new ExampleEventUserModel();
example.processOneSheet(args[0]);
example.processAllSheets(args[0]);
}
}
5.SXSSFWorkbook 使用1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78import junit.framework.Assert;
import org.apache.poi.ss.usermodel.Cell;
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.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
The next example turns off auto-flushing (windowSize=-1) and the code manually controls how portions of data are written to disk
import org.apache.poi.ss.usermodel.Cell;
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.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
// manually control how rows are flushed to disk
if(rownum % 100 == 0) {
((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others
// ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
// this method flushes all rows
}
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
