我可以选择将数据表导出到Excel工作表。
我正在尝试将工作表中所有单元格的字体类型设置为“ Calibri”。
但是下面的代码仅为页眉分配字体类型,而不为其余单元格分配字体类型。
如何为工作表中的所有单元格全局设置字体类型?
public void exportToXLS(Object document) {
HSSFWorkbook wb = (HSSFWorkbook) document;
HSSFSheet sheet = wb.getSheetAt(0);
wb.setSheetName(0, "report_lists"); // set sheet name
sheet.shiftRows(0, sheet.getLastRowNum(), 4); // shifting the rows to
HSSFRow header = sheet.getRow(4);
header.getCell(1).setCellValue("Test Group");
header.getCell(2).setCellValue("Category");
HSSFRow firstrow = sheet.getRow(0);
firstrow.createCell(0).setCellValue("Actuals");
SimpleDateFormat sdf = new SimpleDateFormat(
"EEE MMM d HH:mm:ss 'CDT' yyyy ");
Date date = new Date();
sdf.setTimeZone(TimeZone.getTimeZone("GMT-5"));
String reportDate = sdf.format(date);
HSSFRow thirdrow = sheet.getRow(3);
thirdrow.createCell(0).setCellValue(reportDate);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHeader = (HSSFFont) wb.createFont();
fontHeader.setFontName("Calibri");
cellStyle.setFont(fontHeader);
System.out.println(" header.getPhysicalNumberOfCells();::::"
+ header.getPhysicalNumberOfCells());
//only for changing font for header
for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = header.getCell(i);
cell.setCellStyle(cellStyle);
// sheet.setDefaultColumnStyle(i, cellStyle);
}
System.out.println("sheet.getLastRowNum():::" + sheet.getLastRowNum());
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
HSSFRow row = sheet.getRow(j);
if (row != null) {
System.out.println(" j>>>" + j);
// you can add sysout or debug here to check if all row passed
// successfully
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = row.getCell(i);
if (cell != null) {
System.out.println(" i++" + i);
// you can add sysout or debug here to check if all cell
// passed successfully
HSSFCell celll = header.getCell(i);
celll.setCellStyle(cellStyle);
}
}
}
}
}
这是excel的屏幕截图:
编辑您的代码变得像这样:
public void exportToXLS(Object document) {
HSSFWorkbook wb = (HSSFWorkbook) document;
HSSFSheet sheet = wb.getSheetAt(0);
wb.setSheetName(0, "report_lists"); // set sheet name
sheet.shiftRows(0, sheet.getLastRowNum(), 4); // shifting the rows to
HSSFRow header = sheet.getRow(4);
header.getCell(1).setCellValue("Test Group");
header.getCell(2).setCellValue("Category");
HSSFRow firstrow = sheet.getRow(0);
firstrow.createCell(0).setCellValue("Actuals");
SimpleDateFormat sdf = new SimpleDateFormat(
"EEE MMM d HH:mm:ss 'CDT' yyyy ");
Date date = new Date();
sdf.setTimeZone(TimeZone.getTimeZone("GMT-5"));
String reportDate = sdf.format(date);
HSSFRow thirdrow = sheet.getRow(3);
thirdrow.createCell(0).setCellValue(reportDate);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHeader = (HSSFFont) wb.createFont();
fontHeader.setFontName("Calibri");
cellStyle.setFont(fontHeader);
System.out.println(" header.getPhysicalNumberOfCells();::::"
+ header.getPhysicalNumberOfCells());
//only for changing font for header
//for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
// HSSFCell cell = header.getCell(i);
// cell.setCellStyle(cellStyle);
// sheet.setDefaultColumnStyle(i, cellStyle);
//} you dont need this because the code bellow will change all style including header
System.out.println("sheet.getLastRowNum():::" + sheet.getLastRowNum());
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
HSSFRow row = sheet.getRow(j);
if (row != null) {
System.out.println(" j>>>" + j);
// you can add sysout or debug here to check if all row passed
// successfully
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = row.getCell(i);
if (cell != null) {
System.out.println(" i++" + i);
//HSSFCell celll = header.getCell(i); <<you also don't need this, this is the root of your problem, you choose to change header only instead of already declares cell
cell.setCellStyle(cellStyle);
}
}
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句