POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:
先获取工作薄对象:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle setBorder = wb.createCellStyle();
一、设置背景色:
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
四、设置字体:
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
五、设置列宽:
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
六、设置自动换行:
setBorder.setWrapText(true);//设置自动换行
七、合并单元格:
Region region1 = new Region(0, (short) 0, 0, (short) 6);
//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);
附上一个例子:
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
publicclass ExcelWriter {
/**
* Write Excel 2003
*/
publicvoid buildXSLXExcel(List<Map<String, String>> list, String filePath) {
HSSFWorkbook workBook = null;
String[] cellTitle = { "Screen Name", "Object Name", "XPath" };
try {
workBook = new HSSFWorkbook();// Create workbook
HSSFSheet sheet = workBook.createSheet();// create work sheet
workBook.setSheetName(0, "Repository");// set sheet name
HSSFRow titleRow = sheet.createRow(0);// Create first row
HSSFCell ScreenNameCell = titleRow.createCell(0, 0);
HSSFCell ObjectCell = titleRow.createCell(2, 0);
HSSFCell XpathCell = titleRow.createCell(4, 0);
// Create background color style
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setFillForegroundColor((short)13);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Create font style
Font titleFont = workBook.createFont();
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(titleFont);
// Add color style
ScreenNameCell.setCellStyle(cellStyle);
ObjectCell.setCellStyle(cellStyle);
XpathCell.setCellStyle(cellStyle);
// Set value
ScreenNameCell.setCellValue(cellTitle[0]);
ObjectCell.setCellValue(cellTitle[1]);
XpathCell.setCellValue(cellTitle[2]);
if (list != null && !list.isEmpty()) {
for (int i = 0; i < list.size(); i++) {
Map<String, String> dataMap = list.get(i);
HSSFRow row = sheet.createRow(i + 1);
HSSFCell ScreenNamecell = row.createCell(0);
HSSFCell Objectcell = row.createCell(2);
HSSFCell XPathcell = row.createCell(4);
ScreenNamecell.setCellValue(dataMap
.get(ParseXMLUtil.PARAM_SCREEN_NAME));
Objectcell.setCellValue(dataMap
.get(ParseXMLUtil.PARAM_OBJECT_NAME));
XPathcell.setCellValue(dataMap
.get(ParseXMLUtil.PARAM_OBJECT_PATH));
}
}
File file = new File(filePath);
File parent = file.getParentFile();
if (!parent.exists()) {
parent.mkdir();
}
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream outStream = new FileOutputStream(file);
workBook.write(outStream);
outStream.flush();
outStream.close();
} catch (Exception e) {
System.err.println(e);
e.printStackTrace();
}
}
publicstaticvoid main(String[] args) {
// TODO Auto-generated method stub
}
}