使用Apache的POI相关API导出Excel设置单元格格式
栗子，一下各个代码之间的变量是通用的，要是在某个代码块中找不到某个变量，则可以向上找寻
准备工作
InputStream = template//文件输入流
XSSFWorkbook wb = new XSSFWorkbook(template);
Sheet sheet = wb.getSheetAt(0);
设置单元格格式
XSSFCellStyle cellStyle = wb.createCellStyle();//初始化单元格格式对象
cellStyle.setAlignment(CellStyle.ALIGN_GENERAL);//设置水平对齐方式，有多种对齐方式，如果你稍微了解一点英文也能知道：Alignment(水平)、ALIGN_LEFT(左对齐)
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直对齐方式,Vertical(垂直)
XSSFDataFormat dataFormat = wb.createDataFormat();//创建格式化对象
cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));//设置数值类型格式为保留两位小数
cellStyle.setFillBackgroundColor(IndexedColors.PINK.getIndex());;//设置单元格背景色为骚粉
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//改用指定模式填充单元格颜色(主要用于使你设置的颜色生效以及生效方式)
cellStyle.setWrapText(true);//开启自动换行
Row excelRow = sheet.createRow(0);//创建单元行，索引从0开始
excelRow.setHeightInPoints(30);//设置行高
sheet.setColumnWidth(i, 256 * 15);//设置某列的列宽，列号为“i”
Cell cell = excelRow.createCell(0);//创建单元格，索引从0开始
cell.setCellStyle(cellStyle);// 设置单元格格式
创建各种类型单元格
// 创建数字类型的单元格
Cell cell = row.createCell((short)0);
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
//创建单元格并接设置值为简单字符串
row.createCell(2).setCellValue("This is a string cell");
//创建单元格并接设置值为富文本
RichTextString str = creationHelper.createRichTextString("Apache");
Font font = wb.createFont();
font.setItalic(true);
font.setUnderline(Font.U_SINGLE);
str.applyFont(font);
row.createCell(3).setCellValue(str);
//创建boolean类型的单元格
row.createCell(4).setCellValue(true);
//创建单元格，当前单元的值是通过公式得到的 formula
row.createCell(5).setCellFormula("SUM(A1:B1)");
//创建日期类型的单元格并接进行格式化
CellStyle style = wb.createCellStyle();
style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(6);
cell.setCellValue(new Date());
cell.setCellStyle(style);
//创建超链接类型的单元格
cell.setCellFormula("HYPERLINK(\"\",\"baidu\")");
创建单元格注释
CreationHelper factory = wb.getCreationHelper();
Cell cell = sheet.createRow(1).createCell(1);//创建第二行第二列的单元格
Drawing drawing = sheet.createDrawingPatriarch(); //创建顶层DrawingPatriarch，用于添加图形或图表(通常会覆盖原有效果)
ClientAnchor anchor = factory.createClientAnchor(); //创建ClientAnchor，使用此对象将工程图对象放置在工作表中
Comment comment = drawing.createCellComment(anchor); //创建注释
RichTextString text = creationHelper.createRichTextString("flydoging's demo");
Font font = wb.createFont(); //创建字体用于设置注释字体格式
font.setFontName("Arial");
font.setFontHeightInPoints((short)14);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(IndexedColors.RED.getIndex()); //设置红色字体
text.applyFont(font);
comment.setString(text);//设置注释体
comment.setAuthor("flydoging");//设置作者
cell.setCellComment(comment);
CreationHelper是个啥，官方文档
An object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF. Works around a limitation in Java where we cannot have static methods on interfaces or abstract classes. This allows you to get the appropriate class for a given interface, without you having to worry about if you’re dealing with HSSF or XSSF.
英文不太好，大概意思是说：你可以使用CreationHelper的创建各种用于HSSF、XSSF实例化的对象，通过它的API也可以发现：创建富文本、超链接等等。其实你通过上边wb创建的是HSSFCreationHelper, SXSSFCreationHelper, XSSFCreationHelper三个其中之一，具体是哪个取决于你使用的WorkBook。
获取文档的嵌入文件
public static void main(String[] args) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook(args[0]);
for (PackagePart pPart : workbook.getAllEmbedds()) {
String contentType = pPart.getContentType();
// Excel Workbook - either binary or OpenXML
if (contentType.equals("application/vnd.ms-excel")) {//offic excel
HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());
}
// Excel Workbook - OpenXML file format
else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {//offic excel
XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream());
}
// Word Document - binary (OLE2CDF) file format
else if (contentType.equals("application/msword")) {//offic word
HWPFDocument document = new HWPFDocument(pPart.getInputStream());
}
// Word Document - OpenXML file format
else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) {
XWPFDocument document = new XWPFDocument(pPart.getInputStream());
}
// PowerPoint Document - binary file format
else if (contentType.equals("application/vnd.ms-powerpoint")) {
HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream());
}
// PowerPoint Document - OpenXML file format
else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) {
OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
}
// Any other type of embedded object.
else {
System.out.println("Unknown Embedded Document: " + contentType);
InputStream inputStream = pPart.getInputStream();
}
}
}
