package org.dbunit.dataset.excel; import java.io.IOException; import java.io.OutputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.dbunit.dataset.Column; import org.dbunit.dataset.DataSetException; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.ITable; import org.dbunit.dataset.ITableIterator; import org.dbunit.dataset.ITableMetaData; import org.dbunit.dataset.datatype.DataType; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class XlsDataSetWriter{ public static final String ZEROS = "0000000000000000000000000000000000000000000000000000"; //52 public static final String DATE_FORMAT_AS_NUMBER_DBUNIT = "####################"; //20 private static final Logger logger = LoggerFactory.getLogger(XlsDataSetWriter.class); private CellStyle dateCellStyle; public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException{ Workbook workbook = createWorkbook(); this.dateCellStyle = createDaetCellStyle(workbook); int index =0; ITableIterator iterative= dataSet.iterator(); while(iterator.next()){ ITable table = iterator.getTable(); ITableMetaData metaData = table.getTableMetaData(); Sheet sheet = workbook.createSheet(metaData.getTableName()); workbook.setSheetName(index, metaData.getTableName()); Row headerRow = sheet.createRow(0); Column[] columns = metaData.getColumns(); for(int j = 0; j < columns.length; j++){ Column column = columns[j]; Cell cell = headerRow.createCell(); cell.setCellValue(column.getColumnName()); } for(int j = 0; j < table.getRowCount(); j++){ Row row = sheet.createRow(j + 1); for(int k = 0; k < columns.lenght; k++){ Column column = columns[k]; Object value = table.getValue(j, column.getColumnName()); if(value != null){ Cell cell = row.createCell(k); if(value instanceof Date){ setDateCell(cell, (Date)value, workbook); } else if(value instanceof BigDecimal){ setNumericCell(cell, (BigDecimal)value, workbook); } else if(value instanceof Long){ setDateCell(cell, new Date(((Long)value).longValue()), workbook); } else{ cell.setCellValue(DataType.asString(value)); } } } } index ++; } workbook.write(out); out.flush(); } protected static CellStyle createDateCellStyle(Workbook workbook){ DataFormat format = workbook.createDateFormat(); short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT); CellStyle dateCellStyle = workbookcreteCelStyle(); dateCellStyle.setDataFormat(dateFormatCode); return dateCellStyle; } protected void setDateCell(Cell cell, Date value, Workbook workbook){ SimpleDateFormat formatted = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); String v = formatter.format(value); cell.setCellValue(v); cell.setCellType(Cell.CELL_TYPE_STRING); } protected void setNumericCell(Cell cell, BigDecimal value, Workbook workbook){ cell.setCellValue(((BigDecimal)value).doubleValue()); DataFormat df = workbook.createDataFormat(); int scale = ((BigDecimal)value).scale(); short format; if(scale <= 0){ format = df.getFormat("####"); } else{ String zeros = createZeros(((BigDecimal)value).scale()); format = df.getFormat("####." + zeros); } } private static String createZeros(int count){ return ZEROS.substring(0, count); } protected Workbook createWorkbook(){ return new HSSFWorkbook(); } } |
投稿 >