学会简单使用poi进行excel有关操作

直接上代码：

官网上的抄的api例子：

package com.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
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.hssf.usermodel.HeaderFooter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
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.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.ss.util.WorkbookUtil;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

/**
 * 根据api一些简单操作
 * @author Administrator
 *
 */
public class CreateExcel {
    String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook";
    public static void main(String[] args) {
        CreateExcel ce=new CreateExcel();
//        String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook";
//        ce.createSheet(new HSSFWorkbook(), str+"\\demo0.xls");
//        ce.createCell();
        ce.createDateCell();
    }

    /**
     * 创建Workbook
     */
    public void createWorkbook(){

        Workbook wb=null;
        FileOutputStream fileOut=null;
        try {
            wb=new HSSFWorkbook();
            fileOut=new FileOutputStream(str+"\\demo.xls");
            wb.write(fileOut);
            fileOut.close();

            wb=new XSSFWorkbook();
            fileOut=new FileOutputStream(str+"\\demo1.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //建表，不要问我为什么注释有些是英文的，官网上扣下来的
    public void createSheet(Workbook wb,String filename){
        Sheet sheet1=wb.createSheet("sheet1");
        // Note that sheet name is Excel must not exceed 31 characters
        // and must not contain any of the any of the following characters:
        // 0x0000
        // 0x0003
        // colon (:)
        // backslash (\)
        // asterisk (*)
        // question mark (?)
        // forward slash (/)
        // opening square bracket ([)
        // closing square bracket (])

        // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
        // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')

        String safeName=WorkbookUtil.createSafeSheetName("[O'Briend's sales*?]");//这个方法会去除名称字符串中的不合法字符
        Sheet sheet3=wb.createSheet(safeName);
        try {
            FileOutputStream fileOut=new FileOutputStream(filename, true);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建细胞，也就是创建格子
     */
    public void createCell(){
        Workbook wb=new HSSFWorkbook();//xsl
        //Workbook wb=new XSSFWorkbook();
        CreationHelper createHelper=wb.getCreationHelper();
        Sheet sheet=wb.createSheet("sheet3");

        //create a row and put some cells in it.Rows are 0 based
        Row row=sheet.createRow((short)0);
        //create a cell and put a value in it
        Cell cell=row.createCell(0);//不填写类型的话，默认的类型是this.createCell(column,HSSFCell.CELL_TYPE_BLANK);
        cell.setCellValue(1);

        //or do it on one line
        row.createCell(1).setCellValue(1.2);//创建和赋值连成一行
        row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
        row.createCell(3).setCellValue(true);
        row.createCell(4).setCellValue("nihao");

        //write the output to a file
        try {

            FileOutputStream fileOut=new FileOutputStream("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\demo.xls",true);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建日期类型cell
    public void createDateCell(){
        Workbook wb=new HSSFWorkbook();
        //Workbook wb=new XSSFWorkbook();
        CreationHelper createHelper=wb.getCreationHelper();
        Sheet sheet=wb.createSheet("new sheet");

        Row row=sheet.createRow(0);
        //create a row and put a date value in it.The first cell is not styled as a date.
        Cell cell=row.createCell(0);
        cell.setCellValue(new Date());

        //we style the second cell as a date (and time).It is important to create a new
        //cell style from the workbook oterwise you can end up modifying the builting in style and effecting not only this cell but other cells
        //设置日期的格式是分厂重要的
        CellStyle cellStyle=wb.createCellStyle();
        cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat("m/d/yy h:mm")
        )    ;
        cell=row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cellStyle);

        //write the output to a file
        FileOutputStream fileOut;
        try {

            fileOut = new FileOutputStream(str+"\\demo4.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //使用不同类型的cell
    @Test
    public void useOtherTypeCell(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("new sheet");
        Row row=sheet.createRow((short)2);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(new Date());
        row.createCell(2).setCellValue(Calendar.getInstance());
        row.createCell(3).setCellValue("a string");
        row.createCell(4).setCellValue(true);
        row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);

        //write the output to a file
        FileOutputStream fileOut;
        try {

            fileOut = new FileOutputStream(str+"\\demo5.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //文件VS输入流
    @Test
    public void FileVSStream(){
        //打开工作薄时，要么.xsl HSSFWorkbook或.xlsx XSSFWorkbook,可以从一个工作薄 文件 或者一个InputStream，使用一个文件对象允许更低的
        //内存消耗，而一个InputStream需要更多的内存缓冲区

        //使用WorkbookFactory获取Workbook
        try {
            Workbook wb=WorkbookFactory.create(new File(str+"\\MyExcel.xlsx"));
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //使用InputStream获取Workbook对象
        try {
            Workbook wb=WorkbookFactory.create(new FileInputStream(str+"\\MyExcel.xlsx"));

        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //如果使用HSSFWorkbook或者XSSFWorkbook直接，通常应该使用NPOIFSFileSystem或OPCPackage 有完全控制的生命周期（包括完成后关闭该文件）
        try {
            NPOIFSFileSystem fs=new NPOIFSFileSystem(new File(str+"\\MyExcel.xls"));
            HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true);
            //...
            fs.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            NPOIFSFileSystem fs=new NPOIFSFileSystem(new FileInputStream(str+"\\MyExcel.xls"));
            HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true);
            //...
            fs.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //XXSFWorkbook
        try {
            OPCPackage pkg=OPCPackage.open(new File(str+"\\MyExcel.xlsx"));
            XSSFWorkbook wb=new XSSFWorkbook(pkg);
            //...
            pkg.close();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            OPCPackage pkg=OPCPackage.open(new FileInputStream(str+"\\MyExcel.xlsx"));
            XSSFWorkbook wb=new XSSFWorkbook(pkg);
            //...
            pkg.close();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //演示各种对齐选项
    @Test
    public void alignTest(){
        Workbook wb=new XSSFWorkbook();

        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow((short)2);
        row.setHeightInPoints(30);//设置行高

        createCell(wb,row,(short)0,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_BOTTOM);
        createCell(wb,row,(short)1,CellStyle.ALIGN_CENTER_SELECTION,CellStyle.VERTICAL_BOTTOM);
        createCell(wb,row,(short)2,CellStyle.ALIGN_FILL,CellStyle.VERTICAL_CENTER);
        createCell(wb,row,(short)3,CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
        createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
        createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
        createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);

        //write the output to a file
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\xssf-align.xlsx");
             wb.write(fileOut);
             fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public void createCell(Workbook wb,Row row,short column,short halign,short valign){
        Cell cell=row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle=wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }

    //使用边界
    @Test
    public void usrBorder(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("new sheet");

        Row row=sheet.createRow(1);

        Cell cell=row.createCell(1);
        cell.setCellValue(4);

        //style the cell with borders all around
        CellStyle style=wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo7.xls");
             wb.write(fileOut);
             fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void IteratorRowAndCell(){
        //有时候，你想遍历workbook，一个表中的所有行或者所有的cell
        /*
         *workbook.sheetIterator(),sheet.rowIterator(),row.cellIterator() ,或者隐式地使用forEach
         *注意使用迭代器等遍历注意间隔和空缝隙
         */
    }

    public void IteratorWhiteCell(Sheet sheet){
        int rowStart=Math.min(15,sheet.getFirstRowNum());
        int rowEnd=Math.max(1400, sheet.getLastRowNum());

        for(int rowNum=rowStart;rowNum<rowEnd;rowNum++){
            Row r=sheet.getRow(rowNum);
            if(r==null){
                //这行是空的
                continue;
            }
            int lastColumn=r.getLastCellNum();
            for(int cn=0;cn<lastColumn;cn++){
                Cell c=r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                if(c==null){
                    //the spreadsheet is empty in this cell
                }else{
                    //Do something useful with the cell's contents
                }
            }
        }
    }
    //获取单元格内容
    /*
     *To get the contents of a cell,you first need to know what kind of the cell is(asking a string cell for its numeric will
     *ge you a NumberFormatException for example)
     *
     * 得到单元格内容，你首先需要知道它是什么类型，否则错误的类型可能会出现NumberFormatException
     */
    @Test
    public void getContent(){
        Workbook wb=null;
        try {
            wb = new XSSFWorkbook(new File(str+"\\订餐统计.xlsx"));
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Sheet sheet1=wb.getSheetAt(0);
        for(Row row:sheet1){
            for(Cell cell:row){
                CellReference cellRef=new CellReference(row.getRowNum(),cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.println(" - ");

                switch(cell.getCellType()){
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if(DateUtil.isCellDateFormatted(cell)){
                        System.out.println(cell.getDateCellValue());
                    }else{
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                default:
                    System.out.println();
                }
            }
        }

    }

    //文本提取
    //对于大多数文本提取要求
    @Test
    public void getText(){
        InputStream inp=null;
        HSSFWorkbook wb=null;
        try {
            inp = new FileInputStream(str+"\\demo.xls");
            wb=new HSSFWorkbook(new POIFSFileSystem(inp));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        ExcelExtractor extractor=new ExcelExtractor(wb);

        extractor.setFormulasNotResults(true);
        extractor.setIncludeSheetNames(false);
        String text=extractor.getText();
        System.out.println(text);
    }

    //填充和颜色
    @Test
    public void paddingAndColor(){
        Workbook wb=new XSSFWorkbook();
        Sheet sheet=wb.createSheet("new sheet");

        //create a row and put some cells in it.Row are 0 based
        Row row=sheet.createRow((short)1);

        //Aqua background  aqua:浅绿色
        CellStyle style=wb.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
        style.setFillPattern(CellStyle.BIG_SPOTS);
        Cell cell=row.createCell((short)1);
        cell.setCellValue("X");
        cell.setCellStyle(style);

        //Orange "foreground",foreground beging the fill foreground not the font color . foreground:前景
        style=wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell=row.createCell((short)2);
        cell.setCellValue("X");
        cell.setCellStyle(style);

        //write to output to a file
         FileOutputStream fileOut;
            try {
                fileOut = new FileOutputStream(str+"\\demo8.xlsx");
                 wb.write(fileOut);
                 fileOut.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

    //合并单元格
    @Test
    public void combineCell(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("new sheet");

        Row row=sheet.createRow((short)1);
        Cell cell=row.createCell((short)1);
        cell.setCellValue("this is a test of merging");

        sheet.addMergedRegion(new CellRangeAddress(
            1, //    firstRow, 全部从0为基数
            1, //    lastRow,
            1,//    firstCol,
            2//    lastCol
            ));

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo9.xls");
             wb.write(fileOut);
             fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //设置字体
    @Test
    public void useFont(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("new sheet");

        Row row=sheet.createRow(1);

        //create a new font and alert it
        Font font=wb.createFont();
        font.setFontHeight((short)24);
        font.setFontName("Courier New");
        font.setItalic(true);
        font.setStrikeout(true);

        CellStyle style=wb.createCellStyle();
        style.setFont(font);
        Cell cell=row.createCell(1);
        cell.setCellValue("This is a test of fonts");
        cell.setCellStyle(style);

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo10.xls");
             wb.write(fileOut);
             fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //Note ,the maximun number of unique fonts in a workbooks is limited 32767,you should reuse fonts in you applications
        //instead of creating a font for each cell
        //在一个workbook中，每一个font是唯一的，好像在32767之内。你应该使用同一个Font然后循环给单元格设置样式而不是在循环中初始化多个font

        /*//错误的：
        for(int i=0;i<10000;i++){
            row=sheet.createRow(i);
            cell=row.createCell((short)0);

            style=wb.createCellStyle();
            font=wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(font);
            cell.setCellStyle(style);
        }
        //正确的：
        style=wb.createCellStyle();
        font=wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        for(int i=0;i<10000;i++){
            row=sheet.createRow(i);
            cell=row.createCell((short)0);
            cell.setCellStyle(style);
        }*/
    }

    //自定义颜色
    @Test
    public  void customColor(){
//        //HSSF中
//        HSSFWorkbook wb=new HSSFWorkbook();
//        HSSFSheet sheet=wb.createSheet();
//        HSSFRow row=sheet.createRow((short)0);
//        @SuppressWarnings("deprecation")
//        HSSFCell cell=row.createCell((short)0);
//        cell.setCellValue("Default Palette");
//
//        //apply some colors from the standard palette,as in the previous examples
//        //we will use red text on a lime background
//        HSSFCellStyle style=wb.createCellStyle();
//        style.setFillForegroundColor(HSSFColor.LIME.index);
//        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//        HSSFFont font=wb.createFont();
//        font.setColor(HSSFColor.RED.index);//字体颜色
//        style.setFont(font);
//
//        cell.setCellStyle(style);
//
//        //save with the default palette
//        try {
//            FileOutputStream out=new FileOutputStream(str+"\\default_patte.xls");
//            wb.write(out);
//            wb.close();
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//        //now let's replace RED and LIMIT in the palette with a more attractive combination
//        //(lovingly borrowed from freebsd.org)
//        cell.setCellValue("Modified Palette");
//
//        //creating a custom palette for the workbook
//        HSSFPalette palette=wb.getCustomPalette();
//
//        //replacing the standard red with freebsd.org red
//        palette.setColorAtIndex(HSSFColor.LIME.index,
//                (byte) 153,//red,
//                (byte) 0,//green,
//                (byte) 0//blue
//            );
//        //replacing lime with freebsd.org gold
//        palette.setColorAtIndex(HSSFColor.LIME.index, (byte)255, (byte)204, (byte)102);
//
//        //save with the modified palette
//        //note that wherever we have previously used RED or LIME,the new colors magically appear
//        try {
//            FileOutputStream out=new FileOutputStream(str+"\\modified_palette.xls");
//            wb.write(out);
//            out.close();
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
        //在XSSF中
        XSSFWorkbook wb1=new XSSFWorkbook();
        XSSFSheet sheet1=wb1.createSheet();
        XSSFRow row1=sheet1.createRow(0);
        XSSFCell cell1=row1.createCell(0);
        cell1.setCellValue("custom XSSF colors");

        XSSFCellStyle style1=wb1.createCellStyle();
        style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128,0,128)));//设置背景颜色
        style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell1.setCellStyle(style1);
        try {
            FileOutputStream out=new FileOutputStream(str+"\\modified_palette1.xlsx");
            wb1.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //读和修改
    @Test
    public void readAndRewrite(){
        try {
            InputStream inp=new FileInputStream(str+"\\cs.xlsx");
            //InputStream inp=new FileInputStream("workbook.xlsx");

            Workbook wb=WorkbookFactory.create(inp);
            Sheet sheet=wb.getSheetAt(0);
            Row row=sheet.getRow(2);
            Cell cell=row.getCell(3);
            if(cell==null){
                cell=row.createCell(3);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue("a test");

            FileOutputStream fileOut=new FileOutputStream(str+"\\cs.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //在单元格内使用文本换行
    @Test
    public void useNewLine(){
        Workbook wb=new XSSFWorkbook();//or new HSSFWorkbook();
        Sheet sheet=wb.createSheet();

        Row row=sheet.createRow(2);
        Cell cell=row.createCell(2);
        cell.setCellValue("use \n with word warp on to create a new line");

        //to enable newlines you need set a cell styles with warp==true
        CellStyle cs=wb.createCellStyle();
        cs.setWrapText(true);
        cell.setCellStyle(cs);

        //increase row height to accomodate two lines of text
        //设置行高为两个行高
        row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints());

        //adjust column width to fit the content
        sheet.autoSizeColumn((short)2);

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\ooxml-newlines.xlsx");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //数据格式
    @Test
    public void formatData(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("format sheet");
        CellStyle style;
        DataFormat format=wb.createDataFormat();
        Row row;
        Cell cell;
        short rowNum=0;
        short colNum=0;

        row=sheet.createRow(rowNum++);
        cell=row.createCell(colNum);
        cell.setCellValue(11111.25);
        style=wb.createCellStyle();
        style.setDataFormat(format.getFormat("0.0"));
        cell.setCellStyle(style);

        row=sheet.createRow(rowNum++);
        cell=row.createCell(colNum);
        cell.setCellValue(11111.25);
        style=wb.createCellStyle();
        style.setDataFormat(format.getFormat("#,##0.0000"));
        cell.setCellStyle(style);

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo11.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    //有道翻译感觉不太准确
    //Fix Sheet to One Page
    @Test
    public void fixToOnePage(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("format sheet");
        PrintSetup ps=sheet.getPrintSetup();

        sheet.setAutobreaks(true);

        ps.setFitHeight((short)1);
        ps.setFitWidth((short)1);

        //create various cells and rows for spreadsheet
        Row row=sheet.createRow(1);
        Cell cell=row.createCell(0);
        cell.setCellValue("nihao");
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo12.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //Set Print Area 设置打印区域
    @Test
    public void setPrintArea(){//其实我感觉不出什么东西来，不过api上有就写一下
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet("sheet1");
        //sets the print area for the first sheet
        wb.setPrintArea(0, "$A$1:$C$2");

        //Alternatibely
        wb.setPrintArea(
                0,//sheetIndex,
                0,//startColumn,
                0,//endColumn,
                0,//startRow,
                0//endRow
            );
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo13.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //Set Page Numbers on Footer
    @Test
    public void setFooter(){
        Workbook wb=new HSSFWorkbook();//or new XSSFWorkbook();
        Sheet sheet=wb.createSheet("format sheet");
        Footer footer=sheet.getFooter();
        footer.setRight("Page "+HeaderFooter.page()+" of "+HeaderFooter.numPages());

        //Create various cells and rows for spreadsheet
        Row row=sheet.createRow(1);
        Cell cell=row.createCell(0);
        cell.setCellValue("haha");
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo14.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //使用一些函数
    @Test
    public void useSomeFunction(){
        Workbook wb=new HSSFWorkbook();//new XSSFWorkbook();
        Sheet sheet1=wb.createSheet("new sheet");

        //create a merged region 合并区域
        Row row=sheet1.createRow(1);
        Row row2=sheet1.createRow(2);
        Cell cell=row.createCell(1);
        cell.setCellValue("This is a test of merging");
        CellRangeAddress region=CellRangeAddress.valueOf("B2:E5");
        sheet1.addMergedRegion(region);

        //Set the border and border colors
        final short borderMediumDashed=CellStyle.BORDER_MEDIUM_DASHED;
        RegionUtil.setBorderTop(borderMediumDashed, region, sheet1, wb);
        RegionUtil.setBorderLeft(borderMediumDashed, region, sheet1, wb);
        RegionUtil.setBorderBottom(borderMediumDashed, region, sheet1, wb);
        RegionUtil.setRightBorderColor(borderMediumDashed, region, sheet1, wb);
        RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
        RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
        RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
        RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);

        //shows some usages of HSSFCellUtil
        CellStyle style=wb.createCellStyle();
        style.setIndention((short)4);
        CellUtil.createCell(row, 8, "this is the value of the cell",style);
        Cell cell2=CellUtil.createCell(row2, 8, "this is the value of the cell");
        CellUtil.setAlignment(cell2,wb,CellStyle.ALIGN_CENTER);

        //Write out the workbook
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo15.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    //重复的行和列
    @Test
    public void repeatingRowAndCol(){
        Workbook wb=new HSSFWorkbook();
        Sheet sheet1=wb.createSheet("Sheet1");
        Sheet sheet2=wb.createSheet("Sheet2");

        //set the rows to repeat from 4 to 5 on the first sheet
        sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
        //set the columns to repeat from column A to C on the secend sheet
        sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(str+"\\demo16.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

简单解析excel：

package com.test;

import java.io.File;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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;
/*
  static int     CELL_TYPE_BLANK
          Blank Cell type (3)
static int     CELL_TYPE_BOOLEAN
          Boolean Cell type (4)
static int     CELL_TYPE_ERROR
          Error Cell type (5)
static int     CELL_TYPE_FORMULA
          Formula Cell type (2)
static int     CELL_TYPE_NUMERIC
          Numeric Cell type (0)
static int     CELL_TYPE_STRING
          String Cell type (1)
 */
public class ParseexcelDemo {
    public static void main(String[] args) {
        ParseexcelDemo pxd=new ParseexcelDemo();
        pxd.parseXml("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\学员烧烤通讯录表.xlsx");
    }
    public void parseXml(String filename){
        Workbook wb=null;
        try {
            wb=WorkbookFactory.create(new File(filename));
            Sheet sheet=wb.getSheetAt(0);

            for(Row row:sheet){

                for(Cell cell:row){
                    System.out.print(getCellValue(cell)+"---");
                }
                System.out.println();
            }
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public Object getCellValue(Cell cell){
        int type=cell.getCellType();
        String show=null;
        switch(type){
        case Cell.CELL_TYPE_BLANK:
            show=null;break;
        case Cell.CELL_TYPE_BOOLEAN:
            show= String.valueOf(cell.getBooleanCellValue());break;
        case Cell.CELL_TYPE_ERROR:
            show=String.valueOf(cell.getErrorCellValue());break;
        case Cell.CELL_TYPE_FORMULA:
            show=cell.getCellFormula();break;
        case Cell.CELL_TYPE_NUMERIC:
            show=String.valueOf(cell.getNumericCellValue());break;
        case Cell.CELL_TYPE_STRING:
            show=cell.getStringCellValue();break;
        default:
            show=null;
        }
        return show;
    }
}

简单创建excel：

package com.listenerVedio;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

public class PotTest1 {
    @Test
    public void TestWrite01(){
        Workbook wb=new HSSFWorkbook();
        FileOutputStream fos=null;
        try {
            fos=new FileOutputStream("d:/test/poi/w1.xls");
            Sheet sheet=wb.createSheet("测试01");
            Row row=sheet.createRow(0);
            row.setHeightInPoints(30);//设置行高
            CellStyle cs=wb.createCellStyle();//新建单元格样式对象
            //我们可以使用CellStyle设置单元格的一些关于美化方向的属性，比如颜色，边框，背景等
            cs.setAlignment(CellStyle.ALIGN_CENTER);//设置水平居中
            cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直居中
            /*
            cs.setBorderBottom((short)1);//设置边框样式 这里发现边框设置样式失败。原来设置边框中使用的是样式的常量
            cs.setBorderTop((short)1);
            cs.setBorderLeft((short)1);
            cs.setBorderRight((short)1);
            */
            cs.setBorderBottom(CellStyle.BORDER_DASH_DOT);
            cs.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT_DOT);
            Cell c=row.createCell(0);
            c.setCellStyle(cs);//给单元格设置样式
            c.setCellValue("标识");
            c=row.createCell(1);
            c.setCellStyle(cs);
            c.setCellValue("用户名");
            wb.write(fos);
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        /**
         * 发现这样写是不现实的，因为设置一个单元格样式太过麻烦。虽然使用for、循环可以达到效果
         * 我 们习惯于创建一个模板，模板里面将样式定好，然后我们填充模板里面的元素，然后用来完成数据的导出
         * 能够根据excel转换成对象，能够将对象导成excel，能够带模板的方式导出excel。终极目标
         *
         * 一些思路：如何将对象导出成excel，首先需要有对象，对象一般不是你的实体类而是专门用来导出的自定义对象，应为导出对象的列应该不是所有的
         * 如何确定顶部的标题：使用注解实现。创建Annotation，在其中说明这是excel的resource，顺序也是使用注解实现
         *
         */
    }
}

使用模板简单创建excel：

package com.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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;

/**
 * Excel模板
 * @author Administrator
 *
 */
public class ExcelTemplate {
    private static ExcelTemplate et=new ExcelTemplate();
    public final static String DATA_LINE="datas";
    public final static String DEFAULT_STYLE="defaultStyle";
    public final static String OWNSTYLE="style";
    private Workbook wb;
    private Sheet sheet;
    /**
     * 数据的初始化列
     */
    private int initColIndex;
    /**
     * 数据的初始化行
     */
    private int initRowIndex;
    /**
     * 数据的当前列
     */
    private int curColIndex;
    /**
     * 数据的当前行
     */
    private int curRowIndex;
    /**
     * 数据的最后一行
     */
    private int lastRowIndex;
    /**
     * 当前行对象
     */
    private Row curRow;
    /**
     * 默认样式
     */
    private CellStyle defaultStyle;
    /**
     * 自定义的样式的集合
     */
    private Map<Integer,CellStyle> styles;
    /**
     * 数据列行高
     */
    private float dataLineHeight;
    /**
     * 单例获取et
     * @return
     */
    public static ExcelTemplate getInstance(){
        return et;
    }
    /**
     * 根据classPath读取模板
     * @param classPath
     * @return
     */
    public ExcelTemplate readTemplateByClasspath(String classPath){
        try {
            wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath));
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException("取读模板格式有误！请检查");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("取读的模板不存在！请检查");
        }
        initTemp();
        return et;
    }
    /**
     * 根据filePath读取模板
     * @param filePath
     * @return
     */
    public ExcelTemplate  readTemplateByFilepath(String filePath){
        try {
            wb=WorkbookFactory.create(new File(filePath));
        }catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException("取读模板格式有误！请检查");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("取读的模板不存在！请检查");
        }
        initTemp();
        return et;
    }
    /**
     * 将Workbook写入文件
     * @param filePath
     */
    public void writeToFile(String filePath){
        FileOutputStream fos=null;
        try {
            fos=new FileOutputStream(filePath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("写入的文件不存在！"+e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("写入数据失败！"+e.getMessage());
        }finally{
            try {
                if(fos!=null){
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 将Workbook写入输出流
     * @param os
     */
    public void writeToStream(OutputStream os){
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("写入流失败！"+e.getMessage());
        }
    }
    /**
     * 初始化模板
     */
    public void initTemp(){
        sheet=wb.getSheetAt(0);
        initConfigData();
        curRow=sheet.getRow(initRowIndex);
    }
    /**
     * 获取初始化配置数据
     */
    private void initConfigData() {
        lastRowIndex=sheet.getLastRowNum();
        styles=new HashMap<Integer,CellStyle>();
        int styleinRow=0;
        for(Row row:sheet){
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(str.equals(DATA_LINE)){
                    curColIndex=initColIndex=cell.getColumnIndex();
                    curRowIndex=initRowIndex=cell.getRowIndex();
                    dataLineHeight=row.getHeightInPoints();
                }
                if(str.equals(DEFAULT_STYLE)){
                    defaultStyle=cell.getCellStyle();
                    styleinRow=cell.getRowIndex();
                }
                if(str.equals(OWNSTYLE)){
                    styles.put(cell.getColumnIndex(), cell.getCellStyle());
                    styleinRow=cell.getRowIndex();
                }

                /**
                 * 后面可以附加好多其他东西
                 */

            }
        }
        //sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了
        /*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/
        sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true);
    }
    /**
     * 创建新行
     */
    public void createNewRow(){
        moveData();
        curRow=sheet.createRow(++curRowIndex);
        curRow.setHeightInPoints(dataLineHeight);
        curColIndex=initColIndex;
    }
    /**
     * 创建新列并为新列赋值
     * @param value
     */
    public void createCell(String value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(int value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(double value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(boolean value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(Date value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(Calendar value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(RichTextString value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }

    /**
     * 将excel尾部描述列往后移动
     */
    private void moveData(){
        sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true);
    }
    /**
     * 设置序号 可选
     */
    public void insertSerial(){
        int serialRow=-1;
        int serialCol=-1;
        boolean findSerial=false;
        for(Row row:sheet){
            if(findSerial) break;
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(str.equals("serial")){
                    serialRow=cell.getRowIndex();
                    serialCol=cell.getColumnIndex();
                    findSerial=true;
                    break;
                }
            }
        }
        if(serialRow==-1 || serialCol==-1){
            return;
        }
        int count=curRowIndex-initRowIndex+1;
        for(int i=1;i<=count;i++){
            Cell c=sheet.getRow(serialRow++).createCell(serialCol);
            c.setCellValue(i);
        }
    }
    /**
     * 为一些变量赋值
     * @param datas
     */
    public void replaceVariable(Map<String,String> datas){
        for(Row row:sheet){
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(!str.startsWith("#")) continue;
                if(datas.containsKey(str.substring(1))){
                    cell.setCellValue(datas.get(str.substring(1)));
                }
            }
        }
    }
}

实现excel和对象之间的转换：

package com.ann;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Column {
    String title();
    int order() default 9999;
}
package com.entity;

import com.ann.Column;
/**
 * 用户类
 * @author Administrator
 *
 */
public class User{
    @Column(title="编号",order=1)
    private int id;
    @Column(title="姓名",order=2)
    private String name;
    @Column(title="年龄",order=3)
    private int age;
    @Column(title="邮箱",order=4)
    private String email;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public User() {
        super();
    }

    public User(int id, String name, int age, String email) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age
                + ", email=" + email + "]";
    }

}

package com.util;
/**
 * 列标题列顺序和对应对象字段组成的自定义对象
 * excel和Object的中间对象
 * @author Administrator
 *
 */
public class ExcelHeader implements Comparable<ExcelHeader>{
    /**
     * excel的标题名
     */
    private String title;
    /**
     * 每一个标题的顺序
     */
    private int order;
    /**
     * 每一个列对应的字段名
     * @return
     */
    private String fieldName;
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public ExcelHeader(String title, int order,String filedName) {
        super();
        this.title = title;
        this.order = order;
        this.fieldName=filedName;
    }
    public ExcelHeader() {
        super();

    }

    @Override
    public String toString() {
        return "ExcelHeader [title=" + title + ", order=" + order
                + ", fieldName=" + fieldName + "]";
    }
    public int getOrder() {
        return order;
    }
    public void setOrder(int order) {
        this.order = order;
    }
    @Override
    public int compareTo(ExcelHeader o) {
        return order>o.order?1:(order==o.order?0:-1);
    }
    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }
    public String getFieldName() {
        return fieldName;
    }

}
package com.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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;

/**
 * Excel模板
 * @author Administrator
 *
 */
public class ExcelTemplate {
    private static ExcelTemplate et=new ExcelTemplate();
    public final static String DATA_LINE="datas";
    public final static String DEFAULT_STYLE="defaultStyle";
    public final static String OWNSTYLE="style";
    private Workbook wb;
    private Sheet sheet;
    /**
     * 数据的初始化列
     */
    private int initColIndex;
    /**
     * 数据的初始化行
     */
    private int initRowIndex;
    /**
     * 数据的当前列
     */
    private int curColIndex;
    /**
     * 数据的当前行
     */
    private int curRowIndex;
    /**
     * 数据的最后一行
     */
    private int lastRowIndex;
    /**
     * 当前行对象
     */
    private Row curRow;
    /**
     * 默认样式
     */
    private CellStyle defaultStyle;
    /**
     * 自定义的样式的集合
     */
    private Map<Integer,CellStyle> styles;
    /**
     * 数据列行高
     */
    private float dataLineHeight;
    /**
     * 单例获取et
     * @return
     */
    public static ExcelTemplate getInstance(){
        return et;
    }
    /**
     * 根据classPath读取模板
     * @param classPath
     * @return
     */
    public ExcelTemplate readTemplateByClasspath(String classPath){
        try {
            wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath));
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException("取读模板格式有误！请检查");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("取读的模板不存在！请检查");
        }
        initTemp();
        return et;
    }
    /**
     * 根据filePath读取模板
     * @param filePath
     * @return
     */
    public ExcelTemplate  readTemplateByFilepath(String filePath){
        try {
            wb=WorkbookFactory.create(new File(filePath));
        }catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException("取读模板格式有误！请检查");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("取读的模板不存在！请检查");
        }
        initTemp();
        return et;
    }
    /**
     * 将Workbook写入文件
     * @param filePath
     */
    public void writeToFile(String filePath){
        FileOutputStream fos=null;
        try {
            fos=new FileOutputStream(filePath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("写入的文件不存在！"+e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("写入数据失败！"+e.getMessage());
        }finally{
            try {
                if(fos!=null){
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 将Workbook写入输出流
     * @param os
     */
    public void writeToStream(OutputStream os){
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("写入流失败！"+e.getMessage());
        }
    }
    /**
     * 初始化模板
     */
    public void initTemp(){
        sheet=wb.getSheetAt(0);
        initConfigData();
        curRow=sheet.getRow(initRowIndex);
    }
    /**
     * 获取初始化配置数据
     */
    private void initConfigData() {
        lastRowIndex=sheet.getLastRowNum();
        styles=new HashMap<Integer,CellStyle>();
        int styleinRow=0;
        for(Row row:sheet){
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(str.equals(DATA_LINE)){
                    curColIndex=initColIndex=cell.getColumnIndex();
                    curRowIndex=initRowIndex=cell.getRowIndex();
                    dataLineHeight=row.getHeightInPoints();
                }
                if(str.equals(DEFAULT_STYLE)){
                    defaultStyle=cell.getCellStyle();
                    styleinRow=cell.getRowIndex();
                }
                if(str.equals(OWNSTYLE)){
                    styles.put(cell.getColumnIndex(), cell.getCellStyle());
                    styleinRow=cell.getRowIndex();
                }

                /**
                 * 后面可以附加好多其他东西
                 */

            }
        }
        //sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了
        /*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/
        sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true);
    }
    /**
     * 创建新行
     */
    public void createNewRow(){
        moveData();
        curRow=sheet.createRow(++curRowIndex);
        curRow.setHeightInPoints(dataLineHeight);
        curColIndex=initColIndex;
    }
    /**
     * 创建新列并为新列赋值
     * @param value
     */
    public void createCell(String value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(int value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(double value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(boolean value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(Date value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(Calendar value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }
    public void createCell(RichTextString value){
        Cell c=curRow.createCell(curColIndex);
        if(styles.containsKey(curColIndex))
            c.setCellStyle(styles.get(curColIndex));
        else
            c.setCellStyle(defaultStyle);
        c.setCellValue(value);
        curColIndex++;
    }

    /**
     * 将excel尾部描述列往后移动
     */
    private void moveData(){
        sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true);
    }
    /**
     * 设置序号 可选
     */
    public void insertSerial(){
        int serialRow=-1;
        int serialCol=-1;
        boolean findSerial=false;
        for(Row row:sheet){
            if(findSerial) break;
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(str.equals("serial")){
                    serialRow=cell.getRowIndex();
                    serialCol=cell.getColumnIndex();
                    findSerial=true;
                    break;
                }
            }
        }
        if(serialRow==-1 || serialCol==-1){
            return;
        }
        int count=curRowIndex-initRowIndex+1;
        for(int i=1;i<=count;i++){
            Cell c=sheet.getRow(serialRow++).createCell(serialCol);
            c.setCellValue(i);
        }
    }
    /**
     * 为一些变量赋值
     * @param datas
     */
    public void replaceVariable(Map<String,String> datas){
        for(Row row:sheet){
            for(Cell cell:row){
                if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
                String str=cell.getStringCellValue().trim();
                if(!str.startsWith("#")) continue;
                if(datas.containsKey(str.substring(1))){
                    cell.setCellValue(datas.get(str.substring(1)));
                }
            }
        }
    }
}
package com.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

import com.ann.Column;

/**
 * 对象和excel之间的互相转换
 * 使用模板写入excel与不使用模板写入
 * 读取excel数据到对象集合
 * @author Administrator
 *
 */
@SuppressWarnings({"rawtypes","unchecked"})
public class ExcelUtil {
    private static ExcelUtil eu=new ExcelUtil();
    /**
     * 单例
     */
    private ExcelUtil(){}
    /**
     * excel字段内容对象集合
     */
    private List<ExcelHeader> headers;

    public static ExcelUtil getInstance(){
        return eu;
    }
    /**
     * 模板对象
     */
    private ExcelTemplate et;
    /**
     * excel工作薄对象
     */
    private Workbook wb;
    /**
     * 将对象插入到excel表格中，使用模板，目标是文件
     * @param datas
     * @param alertableValues
     * @param objectClassType
     * @param path
     * @param isFilePath
     * @param outPath
     */
    public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,String outPath){
        et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
        try {
            handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType);

            et.writeToFile(outPath);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
    /**
     * 将对象插入到excel表格中，使用模板，目标是输出流
     * @param datas
     * @param alertableValues
     * @param objectClassType
     * @param path
     * @param isFilePath
     * @param os
     */
    public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,OutputStream os){
        et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
        try {
            handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType);
            et.writeToStream(os);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
    /**
     * 将对象插入到excel表格中，使用模板，目标是文件
     * @param datas
     * @param alertableValues
     * @param objectClassType
     * @param path
     * @param isFilePath
     * @param outPath
     */

    public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,String outPath){
        et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
        try {
            Map maps=new HashMap<String,String>();
            if(!(prop==null || prop.size()==0)) maps.putAll(prop);
            handerObj2ExcelUseTemplate(datas,maps,objectClassType);
            et.writeToFile(outPath);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
    /**
     * 将对象插入到excel表格中，使用模板，目标是输出流
     * @param datas
     * @param alertableValues
     * @param objectClassType
     * @param path
     * @param isFilePath
     * @param os
     */
    public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,OutputStream os){
        et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
        try {
            Map maps=new HashMap<String,String>();
            if(!(prop==null || prop.size()==0)) maps.putAll(prop);
            handerObj2ExcelUseTemplate(datas,maps,objectClassType);
            et.writeToStream(os);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
    /**
     * 使用模板将对象插入Excel中的转换工作的方法
     * @param datas
     * @param alertableValues
     * @param objectClassType
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     * @throws InstantiationException
     */
    private void handerObj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException{
        headers=getHeaderList(objectClassType);
        Collections.sort(headers);
        for(ExcelHeader eh:headers){//设置标题
            et.createCell(eh.getTitle());
        }
        for(Object o:datas){
            et.createNewRow();
            for(ExcelHeader eh:headers){
                et.createCell(BeanUtils.getProperty(o, eh.getFieldName()));
            }
        }
        if(alertableValues==null || alertableValues.size()==0) return;
        et.replaceVariable(alertableValues);
    }
    /**
     * 获取excel字段内容对象集合
     * @param clz
     * @return
     */
    private List<ExcelHeader> getHeaderList(Class clz) {
        List<ExcelHeader> headers=new ArrayList<ExcelHeader>();
        Field[] fields=clz.getDeclaredFields();
        for(Field f:fields){
            if(f.isAnnotationPresent(Column.class)){
                Column c=f.getAnnotation(Column.class);
                headers.add(new ExcelHeader(c.title(), c.order(), f.getName()));
            }
        }
        return headers;
    }
    /**
     * 对象写入excel文件
     * @param isXSSF
     * @param datas
     * @param objectClassType
     * @param outPath
     */
    public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,String outPath){
        wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook());
        try {
            Obj2Excel(datas,objectClassType);
            writeToFile(outPath);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }
    /**
     * 对象写入excel文件 流
     * @param isXSSF
     * @param datas
     * @param objectClassType
     * @param os
     */
    public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,OutputStream os){
        wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook());
        try {
            Obj2Excel(datas,objectClassType);
            writeToStream(os);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }
    /**
     * 将Workbook写入文件
     * @param outPath
     */
    private void writeToFile(String outPath) {
        FileOutputStream fos=null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                if(fos!=null) fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 将Workbook写入输出流
     * @param os
     */
    private void writeToStream(OutputStream os){
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 处理将对象转换成excel的具体实现，不使用模板
     * @param datas
     * @param objectClassType
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    private void Obj2Excel(List<Object> datas,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
        Sheet sheet=wb.createSheet();
        Row r=sheet.createRow(0);
        //插入表头
        headers=getHeaderList(objectClassType);
        Collections.sort(headers);
        for(int i=0;i<headers.size();i++){
            Cell c=r.createCell(i);
            c.setCellValue(headers.get(i).getTitle());
        }
        //插入数据内容
        for(int i=0;i<datas.size();i++){
            r=sheet.createRow(i+1);
            for(int j=0;j<headers.size();j++){
                Cell c=r.createCell(j);
                c.setCellValue(BeanUtils.getProperty(datas.get(i),headers.get(j).getFieldName()));
            }
        }
    }
    /**
     * 从流中读取表格信息到对象集合
     * @param ins
     * @param objectClassType
     * @param readLine
     * @param tailLine
     * @return
     */
    public List<Object> readExcel2Objs(InputStream ins,Class objectClassType,int readLine,int tailLine){
        try {
            wb=WorkbookFactory.create(ins);
            return handerReadExcel2Objs(objectClassType,readLine,tailLine);
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 从文件中读取表格信息到对象集合
     * @param filePath
     * @param objectClassType
     * @param readLine
     * @param tailLine
     * @return
     */
    public List<Object> readExcel2Objs(String filePath,Class objectClassType,int readLine,int tailLine){
        try {
            wb=WorkbookFactory.create(new File(filePath));
            return handerReadExcel2Objs(objectClassType,readLine,tailLine);
        } catch (EncryptedDocumentException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 转换表格信息到对象集合的实现方法
     * @param objectClassType
     * @param readLine
     * @param tailLine
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    private List<Object> handerReadExcel2Objs(Class objectClassType,int readLine,int tailLine) throws InstantiationException, IllegalAccessException, InvocationTargetException{
        List<Object> objs=new ArrayList<Object>();
        Sheet sheet=wb.getSheetAt(0);
        Map<Integer,String> headerMap=getHeaderMap(sheet.getRow(readLine),objectClassType);//列数和字段的map集合
        for(int i=readLine+1;i<sheet.getLastRowNum()-tailLine+1;i++){
            Row r=sheet.getRow(i);
            Object o=objectClassType.newInstance();
            Set<Integer> cols=headerMap.keySet();
            for(int x:cols){
                Cell c=r.getCell(x);
                BeanUtils.copyProperty(o, headerMap.get(x), getCellValue(c));
            }
            objs.add(o);
        }
        return objs;
    }
    /**
     * 获取不同值的方法
     * @param cell
     * @return
     */
    private Object getCellValue(Cell cell){
        int type=cell.getCellType();
        String show=null;
        switch(type){
        case Cell.CELL_TYPE_BLANK:
            show=null;break;
        case Cell.CELL_TYPE_BOOLEAN:
            show= String.valueOf(cell.getBooleanCellValue());break;
        case Cell.CELL_TYPE_ERROR:
            show=String.valueOf(cell.getErrorCellValue());break;
        case Cell.CELL_TYPE_FORMULA:
            show=cell.getCellFormula();break;
        case Cell.CELL_TYPE_NUMERIC:
            show=String.valueOf(cell.getNumericCellValue());break;
        case Cell.CELL_TYPE_STRING:
            show=cell.getStringCellValue();break;
        default:
            show=null;
        }
        return show;
    }
    /**
     * 获取列和对应字段的映射集合
     */
    public Map<Integer,String> getHeaderMap(Row r,Class objectClassType){
        headers=getHeaderList(objectClassType);
        Collections.sort(headers);
        Map<Integer,String> maps=new HashMap<Integer,String>();
        for(Cell c:r){
            String title=c.getStringCellValue();
            for(ExcelHeader eh:headers){
                if(title.equals(eh.getTitle())){
                    maps.put(c.getColumnIndex(), eh.getFieldName());
                    break;
                }
            }
        }
        return maps;
    }
}

一个用来测试的properties：
title="User Message List"
date="2016-7-11"
author="guodaxia"