Java POI组件——写Excel

接上一篇《Java POI组件——读Excel》http://blog.csdn.net/chy555chy/article/details/52738519

本次读写Excel需要使用到的jar为

poi-3.15.jar
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
commons-collections4-4.1.jar
xmlbeans-2.6.0.jar

注意: 在Eclipse中这些jar不单单是加到libs目录下就好了，还要add to “Java Build Path -> Libraries”中。

package com.example.pkg;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.Format;
import java.util.Date;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.extractor.XSSFExcelExtractor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiExcelClass {

    private static final String EXCEL_PATH = "C:/Users/chenjia2014/Desktop/temp/PoiExample/test2007.xlsx";
    // 2003版本的.xls, 一张sheet表允许存2^16 ＝ 次方行数据，2^8 = 256列数据，
    private static final String SUFFIX_HSSF = ".xls";
    // 2007版本以上的.xlsx，一张sheet表允许存的数据就更大了，是百万级别的。行： 2^20 = 1048576; 列：2^14 =
    // 16384 行。
    private static final String SUFFIX_XSSF = ".xlsx";
// 使用POI创建excel工作簿
    public static void createWorkBook(String fileName) {
        Workbook[] workbooks = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
        for (Workbook wb : workbooks) {
            CreationHelper creationHelper = wb.getCreationHelper();
            Sheet sheet = wb.createSheet("New Sheet1");
            Font font1 = wb.createFont();
            Font font2 = wb.createFont();
            CellStyle cs1 = wb.createCellStyle();
            CellStyle cs2 = wb.createCellStyle();
            DataFormat df = wb.createDataFormat();

            font1.setFontHeightInPoints((short) 10);
            font1.setColor(IndexedColors.BLUE.getIndex());

            // 字体大小
            font2.setFontHeightInPoints((short) 16);
            // 字体颜色
            font2.setColor(IndexedColors.RED.getIndex());
            // 粗体
            font1.setBold(true);
            // 斜体
            font1.setItalic(true);
            // 下划线
            font1.setUnderline(Font.U_DOUBLE);
            // 删除线
            font2.setStrikeout(true);
            font2.setFontName("华文行楷");

            cs1.setFont(font1);
            cs1.setDataFormat(df.getFormat("#,##0.0"));
            /*
             * 设置单元格边框样式
             * CellStyle.BORDER_DOUBLE 双边线
             * CellStyle.BORDER_THIN 细边线
             * CellStyle.BORDER_MEDIUM 中等边线
             * CellStyle.BORDER_DASHED 虚线边线
             * CellStyle.BORDER_HAIR 小圆点虚线边线
             * CellStyle.BORDER_THICK 粗边线
             */
            cs1.setBorderBottom(BorderStyle.THIN);
            cs1.setBorderRight(BorderStyle.DOUBLE);
            // 设置边框颜色
            cs1.setBottomBorderColor(IndexedColors.GREEN.getIndex());
            // 设置文本自动换行
            cs1.setWrapText(true);
            // 设置背景色
            cs1.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
            /*
             * 填充模式
             * FillPatternType.NO_FILL;
             * FillPatternType.SOLID_FOREGROUND;
             * FillPatternType.FINE_DOTS;
             * FillPatternType.ALT_BARS;
             * FillPatternType.SPARSE_DOTS;
             * FillPatternType.THICK_HORZ_BANDS;
             * FillPatternType.THICK_VERT_BANDS;
             * FillPatternType.THICK_BACKWARD_DIAG;
             * FillPatternType.THICK_FORWARD_DIAG;
             * FillPatternType.BIG_SPOTS; FillPatternType BRICKS;
             * FillPatternType.THIN_HORZ_BANDS; FillPatternType THIN_VERT_BANDS;
             * FillPatternType.THIN_BACKWARD_DIAG;
             * FillPatternType.THIN_FORWARD_DIAG;
             * FillPatternType.SQUARES;
             * FillPatternType.DIAMONDS;
             * FillPatternType.LESS_DOTS;
             * FillPatternType.LEAST_DOTS;
             */
            cs1.setFillPattern(FillPatternType.BIG_SPOTS);

            cs2.setFont(font2);
            cs2.setDataFormat(df.getFormat("text"));
            cs2.setBorderBottom(BorderStyle.THICK);
            cs2.setBorderBottom(BorderStyle.SLANTED_DASH_DOT);

            for (int rownum = 0; rownum < 20; rownum++) {
                Row row = sheet.createRow(rownum);
                // 设置行高度, 单位磅(heightInPoints 设置的值永远是height属性值的20倍)
                // row.setHeight((short) (rownum % 2 == 0 ? 15*20 : 30*20));
                row.setHeightInPoints((short) (rownum % 2 == 0 ? 15 : 30));
                for (int cellnum = 0; cellnum < 10; cellnum += 2) {
                    if (rownum == 0) {
                        // 设置列宽, 单位字符(in units of 1/256th of a character width)
//                      sheet.setColumnWidth(cellnum, cellnum % 2 == 0 ? 5 * 256 : 40 * 256);
                        //设置宽度自适应
                        sheet.autoSizeColumn(0);
                        //取消宽度自适应
//                      sheet.autoSizeColumn(0, false);
                    }

                    Cell c1 = row.createCell(cellnum);
                    Cell c2 = row.createCell(cellnum + 1);

                    if(cellnum == 0) {
                        //设置为函数的时候前面不需要加等号, The specified formula '=ROW()-1' starts with an equals sign which is not allowed.
                        c1.setCellFormula("ROW()-1");
                    } else {
                        c1.setCellValue((double) rownum + (cellnum / 10));
                        c1.setCellStyle(cs1);
                    }

                    String prefixStr = "Hello! ";
                    String rownumStr = rownum + "";
                    String cellnumStr = cellnum + "";
                    RichTextString richTextStr = creationHelper.createRichTextString(prefixStr + "(" + rownumStr + "," + cellnumStr + ")");
                    richTextStr.applyFont(prefixStr.length() + 1, prefixStr.length() + 1 + rownumStr.length(), font1);
                    richTextStr.applyFont(prefixStr.length() + 1 + rownumStr.length() + 1, prefixStr.length() + 1 + rownumStr.length() + 1 + cellnumStr.length(), font2);
                    c2.setCellValue(richTextStr);
                    c2.setCellStyle(cs2);
                }
            }
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(
                    20,// 起始行
                    21,// 结束行(包括)
                    0, // 起始列
                    5 // 结束列(包括)
            ));
            sheet.createRow(20).createCell(0).setCellValue("合并单元格");

            String fullFileName = (wb instanceof HSSFWorkbook) ? (fileName + SUFFIX_HSSF) : (fileName + SUFFIX_XSSF);
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(fullFileName);
                wb.write(fos);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        createWorkBook("output");
    }

说明一下，超过9的序号都显示为“#”表示省略的意思，拉长这列的宽度，则序号又会重新显示出来