POI读取/写入Excel文件

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @Description :POI读取/创建Excel文件
 * @author : James
 * @version : 1.0
 * @Date : 2016年3月2日 下午2:18:27
 */
public class ExcelManager {

    private HSSFWorkbook hssfWorkbook;
    private XSSFWorkbook xssfWorkbook;

    /**
     * 读取Excel2010版
     * 
     * @param inputStream输入流
     * @return
     */
    public List<String[]> readXlsx(InputStream inputStream) {
        List<String[]> result = new ArrayList<>();
        try {
            xssfWorkbook = new XSSFWorkbook(inputStream);
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
                        for (int i = 0; i < row.length; i++) {
                            row[i] = getValue(xssfRow.getCell(i));
                        }
                        result.add(row);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 读取Excel2003/2007版
     * 
     * @param inputStream输入流
     * @return
     */
    public List<String[]> readXls(InputStream inputStream) {
        List<String[]> result = new ArrayList<>();
        try {
            hssfWorkbook = new HSSFWorkbook(inputStream);
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        String[] row = new String[hssfSheet.getRow(1).getLastCellNum()];
                        for (int i = 0; i < row.length; i++) {
                            row[i] = getValue(hssfRow.getCell(i));
                        }
                        result.add(row);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public List<String[]> readXlsx(File file) {
        List<String[]> result = new ArrayList<>();
        try {
            xssfWorkbook = new XSSFWorkbook(file);
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
                        for (int i = 0; i < row.length; i++) {
                            row[i] = getValue(xssfRow.getCell(i));
                        }
                        result.add(row);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    private String getValue(XSSFCell xssfRow) {
        if (xssfRow == null)
            return "";
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue().trim());
        }
    }

    private String getValue(HSSFCell hssfCell) {
        if (hssfCell == null)
            return "";
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue().trim());
        }
    }

    /**
     * 重新创建新Excel文件
     * 
     * @return
     */
    public String createNewExcel(File file, String newFilePath, String[] title) {
        try {
            InputStream inputStream = new FileInputStream(file);
            // 文件类型
            String fileType = file.getName().substring(file.getName().lastIndexOf(".") + 1, file.getName().length());
            List<String[]> objList = null;
            if ("xlsx".equals(fileType)) {
                objList = readXlsx(inputStream);
            } else if ("xls".equals(fileType)) {
                objList = readXls(inputStream);
            }
            // 创建Excel的工作书册 Workbook,对应到一个excel文档
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            XSSFSheet sheet = wb.createSheet("sheet1");
            sheet.setColumnWidth(0, 5500);// 设置单元格宽度，这里设置第一列的宽度
            XSSFRow firstRow = sheet.createRow(0);
            // 设置字体
            XSSFFont font = wb.createFont();
            font.setBold(true);// 粗体字
            font.setColor(HSSFColor.RED.index);
            font.setFontName("宋体");
            // 设置单元格属性
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(font);// 设置字体
            cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            // 生成表头
            for (int i = 0; i < title.length; i++) {
                XSSFCell cell = firstRow.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(cellStyle);// 设置单元格背景
            }
            // 生成数据
            for (int i = 0; i < objList.size(); i++) {
                String[] lineObj = objList.get(i);
                XSSFRow row = sheet.createRow(i + 1);// 因为表头已经占用了第一行，所以后面生成的行需从第二行开始
                for (int j = 0; j < lineObj.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(lineObj[j].toString());
                }
            }
            FileOutputStream os = new FileOutputStream(newFilePath);
            wb.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        String[] title = { "姓名", "年龄", "性别", "岗位", "入职年份" };
        ExcelManager em = new ExcelManager();
        em.createNewExcel(new File("D:\用户信息.xlsx"), "D:\new_excel.xlsx", title);
    }

}

效果图

原Excel文件：

生成后：