认识达内从这里开始

昆明达内培训的老师这一期给大家讲JAVA读写Excel

ExcelUtil.java

1 package pers.kangxu.datautils.utils;

2

3 import java.io.File;

4 import java.io.FileInputStream;

5 import java.io.FileOutputStream;

6 import java.io.IOException;

7 import java.io.InputStream;

8 import java.util.ArrayList;

9 import java.util.HashMap;

10 import java.util.Iterator;

11 import java.util.List;

12 import java.util.Map;

13

14 import org.apache.poi.hssf.usermodel.HSSFCellStyle;

15 import org.apache.poi.hssf.usermodel.HSSFFont;

16 import org.apache.poi.hssf.usermodel.HSSFWorkbook;

17 import org.apache.poi.ss.usermodel.Cell;

18 import org.apache.poi.ss.usermodel.CellStyle;

19 import org.apache.poi.ss.usermodel.CellValue;

20 import org.apache.poi.ss.usermodel.Font;

21 import org.apache.poi.ss.usermodel.FormulaEvaluator;

22 import org.apache.poi.ss.usermodel.Row;

23 import org.apache.poi.ss.usermodel.Sheet;

24 import org.apache.poi.ss.usermodel.Workbook;

25 import org.apache.poi.ss.util.CellRangeAddress;

26

27 /**

28 *

29 * <b>

30 *   excel工具

31 * </b>

32 * @author kangxu

33 *

34 */

35 public class ExcelUtil {

36    

37    /**

38     *导出excel

39     * @param filePath 文件全路径

40     * @param sheetName sheet页名称

41     * @param sheetIndex当前sheet下表 从0开始

42     * @param fileHeader头部

43     * @param datas内容

44     */

45    public static void writeExcel(String filePath,String sheetName,

46                                    int sheetIndex,

47                                    String[] fileHeader,

48                                    List<String[]> datas){

49        //创建工作簿

50        Workbook wb = new HSSFWorkbook();

51        //创建工作表sheet

52        Sheet s = wb.createSheet();

53        

54        wb.setSheetName(sheetIndex, sheetName);

55        

56        Row r = s.createRow(0);

57        Cell c = null;

58        Font font = null; 

59        CellStyle styleHeader = null;

60        CellStyle styleContent = null;

61        

62        

63        //粗体 

64        font = wb.createFont(); 

65        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 

66        //设置头样式

67        styleHeader = wb.createCellStyle(); 

68        styleHeader.setFont(font);

69        styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框   

70        styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   

71        styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框   

72        styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

73        //设置内容样式

74        styleContent = wb.createCellStyle();

75        styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框   

76        styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   

77        styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框   

78        styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

79        

80        

81        //设置头

82        for(int i=0;i<fileHeader.length;){

83            c = r.createCell(i);

84            c.setCellStyle(styleHeader);

85            c.setCellValue(fileHeader[i]);

86            i++;

87        }

88        

89        //设置内容

90        for(int rownum=0;rownum<datas.size();){ //行row  datas.size()

91            r = s.createRow(rownum+1); //创建行

92            for(int cellnum=0;cellnum<fileHeader.length;){

93                c = r.createCell(cellnum);

94                

95                c.setCellValue(datas.get(rownum)[cellnum]);

96                c.setCellStyle(styleContent);

97                cellnum++;

98            }

99            

100            rownum++;

101        }

102        

103        FileOutputStream out = null;

104        try {

105            //创建文件或者文件夹,将内容写进去

106            if(FileUtil.createFile(new File(filePath))){

107                out = new FileOutputStream(filePath);

108                wb.write(out);

109            }

110            

111        } catch (Exception e) {

112            e.printStackTrace();

113        }finally {

114            try {

115                //关闭流

116                if(out != null){

117                    out.flush();

118                    out.close();

119                }

120            } catch (IOException e) {

121                e.printStackTrace();

122            }

123        }

124        

125    }

126    

127    /**

128     *读取excel文件内容

129     * @param filePath

130     * @param sheetIndex

131     */

132    public static List<Map<String,String>> readExcel(String filePath,int sheetIndex){

133        List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();

134        //头

135        List<String> list = new ArrayList<String>();

136        //

137        

138        int cnt = 0;

139        int idx = 0;

140        

141        try { 

142            InputStream input = new FileInputStream(filePath); //建立输入流 

143            Workbook wb = null; 

144            

145            wb = new HSSFWorkbook(input); 

146            

147            //获取sheet页

148            Sheet sheet = wb.getSheetAt(sheetIndex);

149            

150            Iterator<Row> rows = sheet.rowIterator();

151            while (rows.hasNext()) {

152                Row row = rows.next();

153                Iterator<Cell> cells = row.cellIterator();

154                

155                Map<String,String> map = ew HashMap<String,String>();

156

157                if(cnt == 0){ //将头放进list中

158                    while (cells.hasNext()) { 

159                        Cell cell = cells.next(); 

160                        if(isContainMergeCell(sheet)){

161                            cancelMergeCell(sheet);

162                        }

163                        list.add(getStringCellValue(cell));

164                    }

165                    cnt ++;

166                    continue;

167                    

168                }else {

169                    while (cells.hasNext()) { 

170                        Cell cell = cells.next(); 

171                        if(isContainMergeCell(sheet)){

172                            cancelMergeCell(sheet);

173                        }

174                        //区别相同的头

175                        list = ListUtil.changeSameVal(list);

176                        map.put(list.get(idx++), getStringCellValue(cell));

177                    }

178                }

179                idx = 0;

180                mapList.add(map);

181                

182            }

183            return mapList;

184        } catch (IOException ex) { 

185            ex.printStackTrace(); 

186        }

187        return null;

188         

189    }

190    

191    /**

192     *合并单元格

193     * @param sheet  当前sheet页

194     * @param firstRow开始行

195     * @param lastRow 结束行

196     * @param firstCol 开始列

197     * @param lastCol 结束列

198     */

199    public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

200        if(sheet == null){

201            return -1;

202        }

203        return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

204    }

205    

206

207    /**

208     *取消合并单元格

209     * @param sheet

210     * @param idx

211     */

212    public static void cancelMergeCell(Sheet sheet){

213        int sheetMergeCount = sheet.getNumMergedRegions();

214        for(int idx = 0; idx < sheetMergeCount;){

215            CellRangeAddress range = sheet.getMergedRegion(idx);

216            

217            String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

218            //取消合并单元格

219            sheet.removeMergedRegion(idx);

220            

221            for(int rownum=range.getFirstRow();rownum<range.getLastRow()+1;){

222                for(int cellnum=range.getFirstColumn();cellnum<range.getLastColumn()+1;){

223                    

224                    sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

225                    

226                    cellnum ++;

227                }

228                

229                rownum ++;

230            }

231            

232            idx++;

233        }

234    }

235    

236    /**

237     *判断指定单元格是否是合并单元格

238     * @param sheet  当前sheet页

239     * @param firstRow开始行

240     * @param lastRow 结束行

241     * @param firstCol开始列

242     * @param lastCol 结束列

243     * @return

244     */

245    public static boolean isMergeCell(Sheet sheet,

246            int row ,int column){

247        

248        int sheetMergeCount = sheet.getNumMergedRegions();

249        for(int i = 0; i < sheetMergeCount;){

250            CellRangeAddress range = sheet.getMergedRegion(i);

251            

252            int firstColumn = range.getFirstColumn(); 

253            int lastColumn = range.getLastColumn(); 

254            int firstRow = range.getFirstRow(); 

255            int lastRow = range.getLastRow();

256            if(row >= firstRow && row <= lastRow){

257                if(column >= firstColumn && column <= lastColumn){ 

258                    return true; 

259                }

260            }

261            

262            i++;

263        }

264        return false;

265    }

266    

267    /**

268     *判断sheet页中是否含有合并单元格

269     * @param sheet

270     * @return

271     */

272    public static boolean isContainMergeCell(Sheet sheet){

273        if(sheet == null){

274            return false;

275        }

276        return sheet.getNumMergedRegions()>0 ? true : false;

277    }

278    

279    /**

280     *获取指定合并单元的值

281     * @param sheet

282     * @param row

283     * @param column

284     * @return

285     */

286    public static String getMergeCellValue(Sheet sheet,

287            int row ,int column){

288        

289        int sheetMergeCount = sheet.getNumMergedRegions();

290        for(int i = 0; i < sheetMergeCount;){

291            CellRangeAddress range = sheet.getMergedRegion(i);

292            

293            int firstColumn = range.getFirstColumn(); 

294            int lastColumn = range.getLastColumn(); 

295            int firstRow = range.getFirstRow(); 

296            int lastRow = range.getLastRow();

297            if(row >= firstRow && row <= lastRow){

298                if(column >= firstColumn && column <= lastColumn){ 

299                    Row fRow = sheet.getRow(firstRow);   

300                    Cell fCell = fRow.getCell(firstColumn);

301                    

302                    return getStringCellValue(fCell) ;   

303                }

304            }

305            

306            i++;

307        }

308        

309        return null;

310    }

311    

312    /**

313     *获取单元格的值

314     * @param cell

315     * @return

316     */

317    public static String getStringCellValue(Cell cell) { 

318        String strCell = ""; 

319        if(cell==null) return strCell; 

320        switch (cell.getCellType()) { 

321            case Cell.CELL_TYPE_STRING:

322                strCell = cell.getRichStringCellValue().getString().trim(); 

323                break; 

324            case Cell.CELL_TYPE_NUMERIC:  

325                strCell = String.valueOf(cell.getNumericCellValue()); 

326                break; 

327            case Cell.CELL_TYPE_BOOLEAN:     

328                strCell = String.valueOf(cell.getBooleanCellValue()); 

329                break; 

330            case Cell.CELL_TYPE_FORMULA:     

331                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); 

332                evaluator.evaluateFormulaCell(cell); 

333                CellValue cellValue = evaluator.evaluate(cell); 

334                strCell = String.valueOf(cellValue.getNumberValue()) ; 

335                break; 

336            default: 

337                strCell = ""; 

338        } 

339        return strCell; 

340    }

341

342 }

调用方式如下

ExcelUtilTester.java

1 package pers.kangxu.datautils.test;

2

3 import java.util.ArrayList;

4 import java.util.List;

5

6 import pers.kangxu.datautils.utils.ExcelUtil;

7

8 public class ExcelUtilTester {

9

10    public static void main(String[] args) {

11        List<String[]> datas = new ArrayList<String[]>();

12        datas.add(new String[]{"狗熊","母","250"});

13        datas.add(new String[]{"猪粮","不明","251"});

14        //ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, ew String[]{"姓名","年龄","性别"}, datas);

15        

16        System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

17        

18    }

19 }