使用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);//创建单元格，当前单元的值是通过公式得到的 formularow.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 OpenXMLif (contentType.equals("application/vnd.ms-excel")) {//offic excelHSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());}// Excel Workbook - OpenXML file formatelse if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {//offic excelXSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream());}// Word Document - binary (OLE2CDF) file formatelse if (contentType.equals("application/msword")) {//offic wordHWPFDocument document = new HWPFDocument(pPart.getInputStream());}// Word Document - OpenXML file formatelse if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) {XWPFDocument document = new XWPFDocument(pPart.getInputStream());}// PowerPoint Document - binary file formatelse if (contentType.equals("application/vnd.ms-powerpoint")) {HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream());}// PowerPoint Document - OpenXML file formatelse 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();}}}
