jExcelApi - 单元格日期格式重用不起作用 [英] jExcelApi - cell date format reusing doesn't work
问题描述
我创建了一个jExcelApi类的包装器，可轻松将对象列表导出到Excel。为了最小化对象创建单元格格式被创建为静态字段，并在连续的导出调用中重复使用。但是我有日期格式的问题 - 第一个调用工作正常，但在所有连续的导出日期单元格中都有数字格式而不是日期格式。如果我为日期格式创建一个新对象，而不是使用静态字段，一切都很好。是否有任何理由为不同的工作表或工作簿使用相同的格式对象失败？
这里是代码，异常处理简化，省略其他数据类型，可能还有一些输入错误：
I created a wrapper to jExcelApi classes to easily export lists of objects to Excel. To minimize object creation cell formats are created as static fields and reused in consecutive calls to export. But I have problems with Date format - the first call works good, but in all consecutive exports date cells have numeric format instead of date format. If I create a new object for date format instead of using static field, everything is fine. Is there any reason that using same format object for different sheets or workbooks fails?
Here is the code, with exception handling simplified, other data types omitted and probably some imports missing:
ExcelCellGenerator.java：
ExcelCellGenerator.java:
import jxl.write.WritableCell;
public interface ExcelCellGenerator<T> {
WritableCell getCell(int col, int row, T arg);
}
ExcelCellGeneratorFactory.java：
ExcelCellGeneratorFactory.java:
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import ExcelExporter.DateTimeExtractor;
final class ExcelCellGeneratorFactory {
private ExcelCellGeneratorFactory() {}
private static final WritableCellFormat DATE_FORMAT = new WritableCellFormat ( new DateFormat ("dd MMM yyyy hh:mm:ss")); // reusing this field fails
static public <T> ExcelCellGenerator<T> createDateCellGenerator(final DateTimeExtractor<T> extractor) {
return new ExcelCellGenerator<T>() {
public WritableCell getCell(int col, int row, T arg) {
return new DateTime(col, row, extractor.extract(arg), DATE_FORMAT);
// if there is new WritableCellFormat(new DateFormat(...)) instead of DATE_FORMAT, works fine
}
};
}
}
ExcelExporter.java：
ExcelExporter.java:
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExcelExporter<T> {
// describe a column in Excel sheet
private static class ColumnDescription<T> {
public ColumnDescription() {}
// column title
private String title;
// a way to generate a value given an object to export
private ExcelCellGenerator<T> generator;
}
// all columns for current sheet
private List<ColumnDescription<T>> columnDescList = new ArrayList<ColumnDescription<T>>();
// export given list to Excel (after configuring exporter using addColumn function
// in row number rowStart starting with column colStart there will be column titles
// and below, in each row, extracted values from each rowList element
public byte[] exportList(int rowStart, int colStart, List<? extends T> rowList) {
final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
final WritableSheet sheet = workbook.createSheet("Arkusz1", 0);
int currRow = rowStart;
try {
int currCol = colStart;
for (ColumnDescription<T> columnDesc : columnDescList) {
final Label label = new Label(currCol, currRow, columnDesc.title);
sheet.addCell(label);
currCol++;
}
currRow++;
for (T object : rowList) {
currCol = colStart;
for (ColumnDescription<T> columnDesc : columnDescList) {
sheet.addCell(columnDesc.generator.getCell(currCol, currRow, object));
currCol++;
}
currRow++;
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return outputStream.toByteArray();
}
// configure a Date column
public ExcelExporter<T> addColumn(String title, DateTimeExtractor<T> extractor) {
final ColumnDescription<T> desc = new ColumnDescription<T>();
desc.title = title;
desc.generator = ExcelCellGeneratorFactory.createDateCellGenerator(extractor);
columnDescList.add(desc);
return this;
}
// and test that shows the problem
public static void main(String []args) {
final ExcelExporter<Date> exporter = new ExcelExporter<Date>();
exporter.addColumn("Data", new DateTimeExtractor<Date>() {
public Date extract(Date date) {
return date;
}});
// this file looks OK
FileOutputStream ostream = new FileOutputStream("C:\\tmp\\test1.xls");
try {
ostream.write(exporter.exportList(0, 0, Collections.singletonList(new Date())));
} finally {
ostream.close();
}
// but in this file date is shown in cell with numeric format
final ExcelExporter<Date> exporter2 = new ExcelExporter<Date>();
exporter2.addColumn("Data", new DateTimeExtractor<Date>() {
public Date extract(Date date) {
return date;
}});
ostream = new FileOutputStream("C:\\tmp\\test2.xls");
try {
ostream.write(exporter2.exportList(0, 0, Collections.singletonList(new Date())));
} finally {
ostream.close();
}
}
}
推荐答案
Telcontar的答案是有帮助的，因为说明这是一个功能，而不是一个错误，但还不够，没有提供常见问题或文档的任何链接。所以我做了一些研究，发现一个常见问题解答，说：
Telcontar's answer was helpful as stating it's a feature, not a bug, but was not enough as not giving any link to FAQ or doc. So I did some research and found out a FAQ that says:
同样重要的是，您不要将您的单元格格式声明为 static 。由于单元格格式被添加到工作表中，因此会为其分配内部索引号。
also, it's important that you Do Not declare your cell formats as static. As a cell format is added to a sheet, it gets assigned an internal index number.
所以答案是 - 格式不能重复使用在不同的表格中，因为它们不是以这种方式重新使用的。
So the answer is - formats cannot be reused in different sheets because they are not designed to be reused this way.
这篇关于jExcelApi - 单元格日期格式重用不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
