POI读取/写入Excel文件

  1 import java.io.File;
  2 import java.io.FileInputStream;
  3 import java.io.FileOutputStream;
  4 import java.io.InputStream;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import org.apache.poi.hssf.usermodel.HSSFCell;
  9 import org.apache.poi.hssf.usermodel.HSSFRow;
 10 import org.apache.poi.hssf.usermodel.HSSFSheet;
 11 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 12 import org.apache.poi.hssf.util.HSSFColor;
 13 import org.apache.poi.ss.usermodel.CellStyle;
 14 import org.apache.poi.xssf.usermodel.XSSFCell;
 15 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
 16 import org.apache.poi.xssf.usermodel.XSSFFont;
 17 import org.apache.poi.xssf.usermodel.XSSFRow;
 18 import org.apache.poi.xssf.usermodel.XSSFSheet;
 19 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 20 
 21 /**
 22  * 
 23  * @Description :POI读取/创建Excel文件
 24  * @author : James
 25  * @version : 1.0
 26  * @Date : 2016年3月2日 下午2:18:27
 27  */
 28 public class ExcelManager {
 29 
 30     private HSSFWorkbook hssfWorkbook;
 31     private XSSFWorkbook xssfWorkbook;
 32 
 33     /**
 34      * 读取Excel2010版
 35      * 
 36      * @param inputStream输入流
 37      * @return
 38      */
 39     public List<String[]> readXlsx(InputStream inputStream) {
 40         List<String[]> result = new ArrayList<>();
 41         try {
 42             xssfWorkbook = new XSSFWorkbook(inputStream);
 43             for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
 44                 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
 45                 if (xssfSheet == null) {
 46                     continue;
 47                 }
 48                 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
 49                     XSSFRow xssfRow = xssfSheet.getRow(rowNum);
 50                     if (xssfRow != null) {
 51                         String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
 52                         for (int i = 0; i < row.length; i++) {
 53                             row[i] = getValue(xssfRow.getCell(i));
 54                         }
 55                         result.add(row);
 56                     }
 57                 }
 58             }
 59         } catch (Exception e) {
 60             e.printStackTrace();
 61         }
 62         return result;
 63     }
 64 
 65     /**
 66      * 读取Excel2003/2007版
 67      * 
 68      * @param inputStream输入流
 69      * @return
 70      */
 71     public List<String[]> readXls(InputStream inputStream) {
 72         List<String[]> result = new ArrayList<>();
 73         try {
 74             hssfWorkbook = new HSSFWorkbook(inputStream);
 75             for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
 76                 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
 77                 if (hssfSheet == null) {
 78                     continue;
 79                 }
 80                 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
 81                     HSSFRow hssfRow = hssfSheet.getRow(rowNum);
 82                     if (hssfRow != null) {
 83                         String[] row = new String[hssfSheet.getRow(1).getLastCellNum()];
 84                         for (int i = 0; i < row.length; i++) {
 85                             row[i] = getValue(hssfRow.getCell(i));
 86                         }
 87                         result.add(row);
 88                     }
 89                 }
 90             }
 91         } catch (Exception e) {
 92             e.printStackTrace();
 93         }
 94         return result;
 95     }
 96 
 97     public List<String[]> readXlsx(File file) {
 98         List<String[]> result = new ArrayList<>();
 99         try {
100             xssfWorkbook = new XSSFWorkbook(file);
101             for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
102                 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
103                 if (xssfSheet == null) {
104                     continue;
105                 }
106                 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
107                     XSSFRow xssfRow = xssfSheet.getRow(rowNum);
108                     if (xssfRow != null) {
109                         String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
110                         for (int i = 0; i < row.length; i++) {
111                             row[i] = getValue(xssfRow.getCell(i));
112                         }
113                         result.add(row);
114                     }
115                 }
116             }
117         } catch (Exception e) {
118             e.printStackTrace();
119         }
120         return result;
121     }
122 
123     private String getValue(XSSFCell xssfRow) {
124         if (xssfRow == null)
125             return "";
126         if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
127             return String.valueOf(xssfRow.getBooleanCellValue());
128         } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
129             return String.valueOf(xssfRow.getNumericCellValue());
130         } else {
131             return String.valueOf(xssfRow.getStringCellValue().trim());
132         }
133     }
134 
135     private String getValue(HSSFCell hssfCell) {
136         if (hssfCell == null)
137             return "";
138         if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
139             return String.valueOf(hssfCell.getBooleanCellValue());
140         } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
141             return String.valueOf(hssfCell.getNumericCellValue());
142         } else {
143             return String.valueOf(hssfCell.getStringCellValue().trim());
144         }
145     }
146 
147     /**
148      * 重新创建新Excel文件
149      * 
150      * @return
151      */
152     public String createNewExcel(File file, String newFilePath, String[] title) {
153         try {
154             InputStream inputStream = new FileInputStream(file);
155             // 文件类型
156             String fileType = file.getName().substring(file.getName().lastIndexOf(".") + 1, file.getName().length());
157             List<String[]> objList = null;
158             if ("xlsx".equals(fileType)) {
159                 objList = readXlsx(inputStream);
160             } else if ("xls".equals(fileType)) {
161                 objList = readXls(inputStream);
162             }
163             // 创建Excel的工作书册 Workbook,对应到一个excel文档
164             XSSFWorkbook wb = new XSSFWorkbook();
165             // 创建Excel的工作sheet,对应到一个excel文档的tab
166             XSSFSheet sheet = wb.createSheet("sheet1");
167             sheet.setColumnWidth(0, 5500);// 设置单元格宽度，这里设置第一列的宽度
168             XSSFRow firstRow = sheet.createRow(0);
169             // 设置字体
170             XSSFFont font = wb.createFont();
171             font.setBold(true);// 粗体字
172             font.setColor(HSSFColor.RED.index);
173             font.setFontName("宋体");
174             // 设置单元格属性
175             CellStyle cellStyle = wb.createCellStyle();
176             cellStyle.setFont(font);// 设置字体
177             cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
178             cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
179             cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
180             // 生成表头
181             for (int i = 0; i < title.length; i++) {
182                 XSSFCell cell = firstRow.createCell(i);
183                 cell.setCellValue(title[i]);
184                 cell.setCellStyle(cellStyle);// 设置单元格背景
185             }
186             // 生成数据
187             for (int i = 0; i < objList.size(); i++) {
188                 String[] lineObj = objList.get(i);
189                 XSSFRow row = sheet.createRow(i + 1);// 因为表头已经占用了第一行，所以后面生成的行需从第二行开始
190                 for (int j = 0; j < lineObj.length; j++) {
191                     XSSFCell cell = row.createCell(j);
192                     cell.setCellValue(lineObj[j].toString());
193                 }
194             }
195             FileOutputStream os = new FileOutputStream(newFilePath);
196             wb.write(os);
197             os.close();
198         } catch (Exception e) {
199             e.printStackTrace();
200         }
201         return null;
202     }
203 
204     public static void main(String[] args) {
205         String[] title = { "姓名", "年龄", "性别", "岗位", "入职年份" };
206         ExcelManager em = new ExcelManager();
207         em.createNewExcel(new File("D:\用户信息.xlsx"), "D:\new_excel.xlsx", title);
208     }
209 
210 }
效果图
原Excel文件：
生成后：