java操作excel

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiUtil
{
    public static void main(String[] args)
    {
        try
        {
            // 获取wb，获取第1个sheet，获取第一行
            final XSSFWorkbook wb = readWorkbook("D:/a.xlsx");
            final XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row0 = sheet.getRow(0);
            if (null == row0)
            {
                row0 = sheet.createRow(0);
            }
            row0.setHeight((short) 3000); // 手动设置行高,一整行都是同样的高度

            XSSFCell cell = row0.createCell(0);
            cell.setCellStyle(getCellStyle(wb, "0", "微软雅黑", Color.RED, HorizontalAlignment.CENTER, Color.pink));
            cell.setCellValue(10);

            cell = row0.createCell(1);
            cell.setCellStyle(getCellStyle(wb, "0.000", "楷体", null, null, Color.GRAY));
            cell.setCellValue(20.1234);

            cell = row0.createCell(2);
            cell.setCellStyle(getCellStyle(wb, "0.0000%"));
            cell.setCellValue(30.12345678);

            cell = row0.createCell(3);
            cell.setCellStyle(getCellStyle(wb, "0.0000%"));
            cell.setCellValue("30.12345678"); // 需得用数值类型才行，字符串的不行

            cell = row0.createCell(4);
            cell.setCellStyle(getCellStyle(wb, "text"));
            cell.setCellValue("hello world");

            cell = row0.createCell(5);
            cell.setCellStyle(getCellStyle(wb, "yyyy年mm/dd 原样输出 hh小时:MM分钟#ss 秒"));
            cell.setCellValue(19023); // 将整数计算距离1900-01-01后19023天的日期
            sheet.setColumnWidth(5, 15000); // 手动设置列宽,这一列都是同样的宽度

            cell = row0.createCell(6);
            cell.setCellStyle(getCellStyle(wb, "yyyy--mm月/dd 原样输出 hh小时:MM分钟#ss 秒"));
            cell.setCellValue(new Date()); // 将Date类型数据处理
            sheet.autoSizeColumn(6); // 自适应列宽

            cell = row0.createCell(6);
            cell.setCellStyle(getCellStyle(wb, "yyyy--mm月/dd 原样输出 hh小时:MM分钟#ss 秒"));
            cell.setCellValue(new Date()); // 将Date类型数据处理

            cell = row0.createCell(7);
            cell.setCellStyle(getCellStyle(wb, "yyyy--mm月/dd 原样输出 hh小时:MM分钟#ss 秒"));
            cell.setCellValue("2017-08-12 00:12:34"); // 字符串类型数据无法格式化成上面的格式

            cell = row0.createCell(8);
            cell.setCellStyle(getCellStyle(wb, "yyyy--mm月/dd 原样输出 hh小时:MM分钟#ss 秒"));
            cell.setCellValue(Calendar.getInstance()); // 将字符串类型数据处理

            // 公式的应用
            cell = row0.createCell(9);
            cell.setCellStyle(getCellStyle(wb, "0.000", "宋体", Color.RED, null, Color.yellow));
            cell.setCellValue(getCellValue(wb, cell, "(A1+B1)*5"));

            // 动态公式使用,第1行有7个数字，第2行有7个数字,行号从0开始,计算第3行的数字
            final XSSFRow row3 = sheet.createRow(3);
            for (int i = 0; i < 6; i++)
            {
                final XSSFCell cell_calc = row3.createCell(i); // 待计算的表格
                final int columnIndex = cell_calc.getColumnIndex(); // 得到当前表格的列的索引，索引从0开始
                final int rowNum = cell_calc.getRow().getRowNum(); // 得到当前表 格的行号
                final String columnName = getExcelColumnName(columnIndex); // 得到这个表格的列名
                final String columnNextName = getExcelColumnName(columnIndex + 1); // 得到这个表格下一列的列名

                final String formula = "(" + columnName + (rowNum) + "+" + columnNextName + (rowNum - 1) + "-"
                        + columnName + (rowNum - 1) + ")*5";
                System.out.println("第" + i + "列的公式：" + formula);
                cell_calc.setCellStyle(getCellStyle(wb, "0.000", "宋体", Color.RED, HorizontalAlignment.CENTER,
                        Color.orange));
                cell_calc.setCellValue(getCellValue(wb, cell_calc, formula));
            }

            writeWrokbook(wb, "D:/aaa.xlsx");
        }
        catch (final FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (final IOException e)
        {
            e.printStackTrace();
        }

    }

    /**
     * 打开一个workbook
     * 
     * @param path
     *            读取文件路径
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static XSSFWorkbook readWorkbook(String path) throws FileNotFoundException, IOException
    {
        final XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(path)));
        return wb;
    }

    /**
     * 写出一个workbook
     * 
     * @param wb
     * @param path
     *            写文件路径
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void writeWrokbook(XSSFWorkbook wb, String path) throws FileNotFoundException, IOException
    {
        wb.write(new FileOutputStream(new File(path)));
    }

    /**
     * 指定列的索引，得到这列的标题，例如：A， B， MN等
     * 
     * @param col
     * @return
     */
    public static String getExcelColumnName(int col)
    {
        final StringBuilder str = new StringBuilder(2);
        setExcelColumnName(str, col);
        return str.toString();
    }

    private static void setExcelColumnName(StringBuilder str, int col)
    {
        final int tmp = col / 26;
        if (tmp > 26)
        {
            setExcelColumnName(str, tmp - 1);
        }
        else if (tmp > 0)
        {
            str.append((char) (tmp + 64));
        }
        str.append((char) (col % 26 + 65));
    }

    /**
     * 获取格式对象
     * 
     * @param wb
     * @param style
     *            样式，例如：0:整数 , 0.00=任意位小数 , 0.0000%任意位百分比的小数, text纯文本,
     *            yyyy-mm-dd日期, hh:MM:ss时间, yyyy/mm月-dd日 hh小时mm_ss分
     * @return
     */
    public static XSSFCellStyle getCellStyle(XSSFWorkbook wb, String style)
    {
        final XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(wb.createDataFormat().getFormat(style));
        return cellStyle;
    }

    /**
     * 获取格式对象
     * 
     * @param wb
     * @param style
     *            样式，例如：0:整数 , 0.00=任意位小数 , 0.0000%任意位百分比的小数, text纯文本,
     *            yyyy-mm-dd日期, hh:MM:ss时间, yyyy/mm月-dd日 hh小时mm_ss分
     * @param fontName
     *            字体名称
     * @param fontColor
     *            字体颜色
     * @param horizontalAlignment
     *            居中，居左，居右
     * @param backgroundColor
     *            背景色
     * @return
     */
    public static XSSFCellStyle getCellStyle(XSSFWorkbook wb,
                                             String style,
                                             String fontName,
                                             Color fontColor,
                                             HorizontalAlignment horizontalAlignment,
                                             Color backgroundColor)
    {
        final XSSFCellStyle cellStyle = getCellStyle(wb, style);
        final XSSFFont font = wb.createFont();
        if (null != fontName && fontName.trim().length() != 0)
        {
            font.setFontName(fontName);
            cellStyle.setFont(font);
        }
        if (null != fontColor)
        {
            font.setColor(new XSSFColor(fontColor));
            cellStyle.setFont(font);
        }
        if (null != horizontalAlignment)
        {
            cellStyle.setAlignment(horizontalAlignment);
        }
        if (null != backgroundColor)
        {
            cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(new XSSFColor(backgroundColor));
        }
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        return cellStyle;
    }

    /**
     * 支持表格中的多表格之间的数学运算
     * 
     * @param wb
     * @param cell
     * @param formula
     *            公式
     * @return
     */
    public static double getCellValue(XSSFWorkbook wb, XSSFCell cell, String formula)
    {
        final XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(wb); //生成计算对象
        cell.setCellFormula(formula);
        final CellValue cellValue = evaluator.evaluate(cell); // 执行计算
        return cellValue.getNumberValue();
    }
}