I developed the following class as a high level Wrapper around Apaches POI API for reading and writing Microsoft Excel files. It implements an interface, but you can ignore it.

Source Code

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
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
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
/**
 
 */
package de.consulting.bolte.io.file;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
/**
 * @author Alexander Bolte (2016)
 
 */
public class ExcelFile implements SourceFile {
 
	/**
	 * Creates a new instance of this class and references an Excel file
	 * available at given file path.
	 
	 * @param path
	 *            - a full file path to an Excel file.
	 * @throws IOException
	 *             - if issues with a provided file occur. E.g. a provided file
	 *             is not an Excel file or the file does not exist.
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	public ExcelFile(String path) throws IOException,
			EncryptedDocumentException, InvalidFormatException {
		/*
		 * Reference a file.
		 */
		this.in = new FileInputStream(path);
		/*
		 * The factory decides based on the provided file format (.xls or .xlsx)
		 * which workbook type (HSSF for Excel 2003 and earlier or XSSF for
		 * Excel 2007 and later).
		 */
		this.wrk = WorkbookFactory.create(this.in);
		this.currentSheet = this.wrk.getSheetAt(this.wrk.getActiveSheetIndex());
		this.initializeSheet();
	}
 
	/**
	 * Initializes indices used for the currently referenced Sheet in a
	 * Workbook.
	 */
	private void initializeSheet() {
		this.rowIndex = 0;
	}
 
	/**
	 * Creates a new Excel workbook but does not yet write a file.
	 */
	public ExcelFile(boolean isXmlFormat) {
		if (isXmlFormat) {
			this.wrk = new XSSFWorkbook();
		} else {
			this.wrk = new HSSFWorkbook();
		}
	}
 
	/**
	 * Sets the current sheet to the sheet with given name.
	 
	 * @param sheet
	 *            - a unique name of a sheet in currently referenced workbook.
	 * @return true, if the sheet has been found and set, else false.
	 */
	public boolean selectWorksheet(String sheet) {
		try {
			this.currentSheet = this.wrk.getSheet(sheet);
			this.initializeSheet();
			return true;
		} catch (NullPointerException ex) {
			return false;
		}
	}
 
	/**
	 * Gets the name of a sheet currently referenced for read / write
	 * operations. An instance of this class can only write in one sheet.
	 
	 * @return the name of the currently referenced sheet for read / write
	 *         operations.
	 */
	public String getCurrentSheetName() {
		try {
			return this.currentSheet.getSheetName();
		} catch (Exception ex) {
			return null;
		}
 
	}
 
	@Override
	public boolean hasNextRow() {
		int rowCount = 0;
 
		rowCount = this.currentSheet.getLastRowNum();
		if (rowCount == 0) {
			rowCount = this.currentSheet.getPhysicalNumberOfRows();
		}
 
		if (this.rowIndex < rowCount) {
			return true;
		} else {
			return false;
		}
	}
 
	/**
	 * Gets the number of the last logical row in a worksheet.
	 
	 * @return the total of logical rows in currently set worksheet.
	 */
	public int getLastRowNum() {
		int rowCount = 0;
 
		rowCount = this.currentSheet.getLastRowNum();
		if (rowCount == 0) {
			rowCount = this.currentSheet.getPhysicalNumberOfRows();
		}
 
		return rowCount;
	}
 
	/**
	 * Returns the row number of the last physical row in a sheet holding data.<br/>
	 * <br/>
	 * If a row does not contain any value, meaning it contains only blank cells
	 * (equals null), and it is at the end of the used range in a sheet it is
	 * not considered.
	 
	 * @return the row number of the last physical row with data.
	 */
	public int getLastRowWithData() {
		int rowCount = 0;
		Iterator<Row> iter = this.currentSheet.rowIterator();
 
		while (iter.hasNext()) {
			Row r = iter.next();
			if (!this.isRowBlank(r)) {
				rowCount = r.getRowNum();
			}
		}
 
		return rowCount;
	}
 
	/**
	 * Gets the currently selected Sheet.
	 
	 * @return the Sheet currently referenced by an instance of this class.
	 */
	public Sheet getCurrentSheet() {
		return this.currentSheet;
	}
 
	/**
	 * Checks if a row only holds blank cells (euqal to null).
	 
	 * @param r
	 *            a Row (either logical or physical - does not matter for this
	 *            method).
	 * @return true, if the row only holds blank cells, else false.
	 */
	public boolean isRowBlank(Row r) {
		boolean ret = true;
 
		/*
		 * If a row is null, it must be blank.
		 */
		if (r != null) {
			Iterator<Cell> cellIter = r.cellIterator();
			/*
			 * Iterate through all cells in a row.
			 */
			while (cellIter.hasNext()) {
				/*
				 * If one of the cells in given row contains data, the row is
				 * considered not blank.
				 */
				if (!this.isCellBlank(cellIter.next())) {
					ret = false;
					break;
				}
			}
		}
 
		return ret;
	}
 
	/**
	 * Checks if a provided Cell is blank, meaning its value must be null.
	 
	 * @param c
	 *            a Cell to check for a null value.
	 * @return true, if a provided Cell is blank.
	 */
	public boolean isCellBlank(Cell c) {
		return (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK);
	}
 
	/**
	 * Checks if a provided Cell is blank, meaning its value is null or its
	 * value is an empty String ("").
	 
	 * @param c
	 *            a Cell to check.
	 * @return true, if given Cell is blank or holds an empty String, else
	 *         false.
	 */
	public boolean isCellEmpty(Cell c) {
		return (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK || (c
				.getCellType() == Cell.CELL_TYPE_STRING && c
				.getStringCellValue().isEmpty()));
	}
 
	@Override
	public HashMap<String, String> getNextRow() {
		HashMap<String, String> values = null;
 
		Row r = this.currentSheet.getRow(this.rowIndex++);
		if (r != null) {
			values = new HashMap<String, String>();
			Iterator<String> fields = this.header.keySet().iterator();
 
			while (fields.hasNext()) {
				values.put(fields.next(),
						r.getCell(this.header.get(fields.next()))
								.getStringCellValue());
			}
		}
 
		return values;
	}
 
	@Override
	public void addRow(HashMap<String, String> values) {
		Set<String> valField = values.keySet();
		int lr = this.getLastRowNum();
		Row r = this.currentSheet.createRow(lr + 1);
 
		for (String f : valField) {
			/*
			 * Check, if a handed field does exist in the target sheet.
			 */
			if (this.header.containsKey(f)) {
				/*
				 * Write a value into a mapped field in a new row.
				 */
				Cell c = r.createCell(this.header.get(f));
				c.setCellValue(values.get(f));
			}
		}
	}
 
	/**
	 * Writes all values in a given collection into a row available at given row
	 * number. The values are written into columns as ordered in handed
	 * collection.
	 
	 * @param rowNumber
	 *            a zero based positive number identifying a Row in a Sheet.
	 * @param val
	 *            a collection of values to write into a Row.
	 */
	public void writeRow(int rowNumber, ArrayList<String> val) {
		Row r = null;
		int i = 0;
 
		r = this.currentSheet.getRow(rowNumber);
		if (r == null) {
			r = this.currentSheet.createRow(rowNumber);
		}
 
		for (String v : val) {
			/*
			 * Write the values as ordered in provided collection into the row.
			 */
			Cell c = r.getCell(i);
			if (c == null) {
				c = r.createCell(i);
			}
			/*
			 * Write a value into cell.
			 */
			c.setCellValue(v);
			i++;
		}
	}
 
	/**
	 * Saves the currently referenced Workbook as new file.
	 
	 * @param filePath
	 *            - the full file path of a new file the workbook should be
	 *            saved as.
	 * @throws IOException
	 */
	public void saveFile(String filePath) throws IOException {
		FileOutputStream out = new FileOutputStream(filePath);
		this.wrk.write(out);
		out.flush();
		out.close();
	}
 
	public void createWorksheet(String name) {
		this.currentSheet = this.wrk.createSheet(name);
	}
 
	@Override
	public void setHeader(HashMap<String, Integer> header) {
		this.header = header;
	}
 
	@Override
	public HashMap<String, Integer> getHeader() {
		return this.header;
	}
 
	@Override
	public void closeFile() {
		try {
			/*
			 * Closes underlying input stream, if a workbook was read.
			 */
			this.currentSheet = null;
			this.wrk.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
 
	@Override
	public void resetRowIndex() {
		this.rowIndex = 0;
	}
 
	@Override
	public boolean initHeader(int rowNumber) {
		boolean isInit = false;
 
		/*
		 * Make sure header is initialized.
		 */
		this.header = new HashMap<String, Integer>();
		/*
		 * Get the physical Row by provided row number.
		 */
		Row r = this.currentSheet.getRow(rowNumber);
		if (r != null) {
			/*
			 * Iterate over all cells in a row.
			 */
			Iterator<Cell> iter = r.cellIterator();
			while (iter.hasNext()) {
				Cell c = iter.next();
				/*
				 * If a cell is empty it is not regarded.
				 */
				if (!this.isCellBlank(c)) {
					this.header.put(c.getStringCellValue(), c.getColumnIndex());
					isInit = true;
				}
			}
		}
 
		return isInit;
	}
 
	private int rowIndex;
	private Sheet currentSheet;
	private Workbook wrk;
	private FileInputStream in;
	private HashMap<String, Integer> header;
	private final String NEW_WORKBOOK = "NewWorkbook.xlsx";
}

Unit Tests

Here are some basic unit tests I implemented. Source files will have to be created and package names adjusted, if you intend to copy them.

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
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
/**
 
 */
package de.consulting.bolte.test;
 
import static org.junit.Assert.fail;
 
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
 
import de.consulting.bolte.io.file.ExcelFile;
 
/**
 * @author wjx130
 *
 */
public class ExcelFileTest {
 
	/**
	 * @throws java.lang.Exception
	 */
	@Before
	public void setUp() throws Exception {
	}
 
	/**
	 * @throws java.lang.Exception
	 */
	@After
	public void tearDown() throws Exception {
	}
 
	/**
	 * Test method for
	 * {@link de.consulting.bolte.io.file.ExcelFile#ExcelFile(java.lang.String)}
	 * .
	 
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	@Test
	public void testExcelFileString() throws IOException,
			EncryptedDocumentException, InvalidFormatException {
		/*
		 * Open a file.
		 */
		ExcelFile xl = new ExcelFile(
				"./src/de/consulting/bolte/test/xlTestRead.xlsx");
		/*
		 * Close the file.
		 */
		xl.closeFile();
	}
 
	/**
	 * Test method for {@link de.consulting.bolte.io.file.ExcelFile#ExcelFile()}
	 * .
	 */
	@Test
	public void testExcelFile() {
		fail("Not yet implemented");
	}
 
	/**
	 * Test method for
	 * {@link de.consulting.bolte.io.file.ExcelFile#selectWorksheet(java.lang.String)}
	 * .
	 
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	@Test
	public void testSelectWorksheet() throws EncryptedDocumentException,
			InvalidFormatException, IOException {
		String sName = null;
 
		/*
		 * Open a file.
		 */
		ExcelFile xl = new ExcelFile(
				"./src/de/consulting/bolte/test/xlTestRead.xlsx");
 
		/*
		 * Select a sheet.
		 */
		xl.selectWorksheet("xlTestRead");
		sName = xl.getCurrentSheetName();
 
		/*
		 * Close the file.
		 */
		xl.closeFile();
 
		Assert.assertEquals("xlTestRead", sName);
	}
 
	/**
	 * Test method for
	 * {@link de.consulting.bolte.io.file.ExcelFile#hasNextRow()}.
	 
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	@Test
	public void testHasNextRow() throws EncryptedDocumentException,
			InvalidFormatException, IOException {
		boolean actual;
 
		/*
		 * Open a file.
		 */
		ExcelFile xl = new ExcelFile(
				"./src/de/consulting/bolte/test/xlTestRead.xlsx");
 
		xl.selectWorksheet("Sheet2");
		actual = xl.hasNextRow();
 
		/*
		 * Close the file.
		 */
		xl.closeFile();
 
		Assert.assertEquals(false, actual);
	}
 
	@Test
	public void testGetLastRow() throws EncryptedDocumentException,
			InvalidFormatException, IOException {
		final int expected = 9;
		int actual;
 
		/*
		 * Open a file.
		 */
		ExcelFile xl = new ExcelFile(
				"./src/de/consulting/bolte/test/xlTestRead.xlsx");
 
		System.out.println(xl.getCurrentSheet().getFirstRowNum());
		System.out.println(xl.getCurrentSheet().rowIterator().next()
				.getRowNum());
		System.out.println(xl.getLastRowNum());
 
		/*
		 * Get the last row from currently selected sheet.
		 */
		// actual = xl.getLastRow();
		actual = xl.getLastRowWithData();
 
		/*
		 * Close the file.
		 */
		xl.closeFile();
 
		Assert.assertEquals(expected, actual);
	}
 
	@Test
	public void testUpdateExcelFile() throws EncryptedDocumentException,
			InvalidFormatException, IOException {
		final int expected = 12;
		int actual;
 
		/*
		 * Open a file.
		 */
		ExcelFile xl = new ExcelFile(
				"./src/de/consulting/bolte/test/xlTestRead.xlsx");
 
		if (!xl.initHeader(3)) {
			/*
			 * Close the file.
			 */
			xl.closeFile();
			fail("header not initalized");
		}
 
		/*
		 * Add a new line to the currently referenced sheet.
		 */
		HashMap<String, String> values = new HashMap<String, String>();
		values.put("A", "bla bla bla");
		values.put("C", "hello World");
		xl.addRow(values);
 
		/*
		 * Get the last row from currently selected sheet.
		 */
		actual = xl.getLastRowWithData();
 
		/*
		 * Close the file.
		 */
		xl.closeFile();
 
		Assert.assertEquals(expected, actual);
	}
 
	@Test
	public void testCreateExcelFile() throws IOException {
		ExcelFile xl = new ExcelFile(true);
		ArrayList<String> val = new ArrayList<String>();
 
		xl.createWorksheet("Huhuh");
		val.add("A");
		val.add("");
		val.add("745z12r4zquipehfquil 346ß01ui0ääääää????ßßßßߧ$%&/()=");
		xl.writeRow(0, val);
 
		xl.saveFile("./src/de/consulting/bolte/test/xlTesCreate.xlsx");
		xl.closeFile();
	}
 
	/**
	 * Test method for
	 * {@link de.consulting.bolte.io.file.ExcelFile#addRow(java.util.HashMap)}.
	 */
	@Test
	public void testAddRow() {
		fail("Not yet implemented");
	}
 
}

References

http://www.avajava.com/tutorials/lessons/how-do-i-write-to-an-excel-file-using-poi.html

http://poi.apache.org/apidocs/index.html