Webwarp Util Excel

General

This module simplifies reading and writing of Excel-Files with the HSSF-Library from Appache. Additionally reading and writing a MS OpenXML File replacing some special placeholders is also supported.

Class Overview

Reading Files

Writing Files

Manipulating OpenXML formatted files.

Example Usage

Reading Files

HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(HssfUtilTest.class.getResourceAsStream("MyExcel.xls"))); HSSFSheet sheet = wb.getSheetAt(0); List<String> columnNames = new ArrayList<String>(); columnNames.add("name"); columnNames.add("prename"); columnNames.add("dateOfBirth"); ExcelSheet excelSheet = new ExcelSheet(sheet, columnNames); int index = 1; while(true){ Map<String, Object> row = excelSheet.getRow(index++); if(row.size()==0){ break; } String name = (String)row.get("name"); String prename = (String)row.get("prename"); java.util.Date date = (Date) row.get("dateOfBirth"); [...] // do something with the row data }

Additionally you can perform replacements on data read using the ExcelSheetWithReplaceMap class. Hereby the base usage is identical to the one when using the ExcelSheet class. // Initialization equals to the one in the previous code block // *** New: define a replacement map Map repMap = new HashMap(); repMap.put("aValue", "theReplacedValue"); ExcelSheet excelSheet = new ExcelSheetWithReplaceMap(sheet, columnNames, repMap); [...] // go ahead as before

Writing Files

Using the ExcelExporter class

interface Data{ public String getName(); public java.util.Date getDateOfBirth(); } Data[] data = new Data[]{ new Data(){ public String getName(){ return "Dataname"; } public java.util.Date getDateOfBirth(){ return new java.util.Date(); } } }; ExcelColumn column1 = new ExcelColumn("name", "String Value"); ExcelColumn column2 = new ExcelColumn("dateOfBirth", "Date Value", "dd.mm.yyyy"); ExcelColumn[] columns = new ExcelColumn[]{column1, column2}; ExcelExporter exporter = new ExcelExporter(); exporter.setSheetName("MyDataSheet"); OutputStream bos = new FileOutputStream("MyExcel.xls"); exporter.export(bos, columns, data);

Manipulating OpenXML files

Following the test code for demonstrating how an OpenXML file can be manipulated using this module: import net.sf.webwarp.util.collection.DataModel; import net.sf.webwarp.util.collection.DefaultDataModel; import net.sf.webwarp.util.excel.openxml.OpenXmlDocument; import net.sf.webwarp.util.excel.openxml.OpenXmlUtil; [...] OpenXmlDocument openXmlDocument = OpenXmlUtil.readDocumentFromResource("YourFile.xlsx"); // xlsx, xlsm DateTimeZone.setDefault(DateTimeZone.UTC); LocalDateTime localDateTime = new LocalDateTime(2008, 7, 11, 15, 13, 58); LocalDate localDate = localDateTime.toLocalDate(); DateTime dateTime = localDateTime.toDateTime(); Date date = dateTime.toDate(); DataModel dataModel = new DefaultDataModel(new String[]{"number", "date", "boolean", "text"}); DataModel.RowObject rowObject; rowObject = new DefaultDataModel.DefaultRowObject(4); rowObject.setValueAt(Integer.valueOf(1), 0); rowObject.setValueAt(date, 1); rowObject.setValueAt(Boolean.valueOf(false), 2); rowObject.setValueAt("date = " + date, 3); dataModel.insertRow(rowObject); rowObject = new DefaultDataModel.DefaultRowObject(4); rowObject.setValueAt(new BigDecimal("123456789.987654321"), 0); rowObject.setValueAt(dateTime, 1); rowObject.setValueAt(Boolean.valueOf(true), 2); rowObject.setValueAt("dateTime = " + dateTime, 3); dataModel.insertRow(rowObject); rowObject = new DefaultDataModel.DefaultRowObject(4); rowObject.setValueAt(new Double("53213.13216"), 0); rowObject.setValueAt(localDate, 1); rowObject.setValueAt(Boolean.valueOf(true), 2); rowObject.setValueAt("localDate = " + localDate, 3); dataModel.insertRow(rowObject); rowObject = new DefaultDataModel.DefaultRowObject(4); rowObject.setValueAt(new Double("53213.13216"), 0); rowObject.setValueAt(localDateTime, 1); rowObject.setValueAt(Boolean.valueOf(true), 2); rowObject.setValueAt("localDateTime = " + localDateTime, 3); dataModel.insertRow(rowObject); openXmlDocument.setDataSheet(1, dataModel); String sheetName = "MySheetName"; openXmlDocument.setSheetName(Integer.valueOf(1), sheetName); OpenXmlUtil.saveDocumentToFile(openXmlDocument, "YourTargetFile.xlsx"); if(openXmlDocument.getFileData() == null){ // an error occurred }


Webwarp Util