java中excelview_java对excel的操作

1 packagecom.tpaic.poiexcel;2

3 importlombok.extern.log4j.Log4j;4 import org.apache.poi.ss.usermodel.*;5 importorg.apache.poi.ss.util.CellRangeAddress;6 importorg.apache.poi.xssf.usermodel.XSSFCell;7 importorg.apache.poi.xssf.usermodel.XSSFRow;8 importorg.apache.poi.xssf.usermodel.XSSFSheet;9 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;10

11 importjavax.servlet.http.HttpServletRequest;12 importjavax.servlet.http.HttpServletResponse;13 import java.io.*;14 importjava.util.ArrayList;15 importjava.util.Arrays;16 importjava.util.List;17 @Log4j18 public classPoiUtils {19

20

21 /**

22 * 对比任意类型两张表是否有不同行23 *@paramexcel1Path 对比excel路径24 *@paramexcel2Path 被对比excel路径25 */

26 public static voidcompareExcelAWithExcelB(String excel1Path,String excel2Path){27 int sheetNum=0;28 FileInputStream fis1 = null;29 FileInputStream fis2 = null;30 try{31 fis1 = newFileInputStream(excel1Path);32 fis2 = newFileInputStream(excel2Path);33 List objects1 = PoiUtils.readExcelToObj(fis1, sheetNum);//解析第一个excel的数据 对比数据

34 List objects2 = PoiUtils.readExcelToObj(fis2, sheetNum);//解析第二个excel的数据 被对比数据35

36 //遍历第一个excel数据，即取的对比数据的某一行某一列具体值

37

38 Object [] objArr1 = null;//定义一个对象数组，存放每一行数据

39 List columnsList = null;//定义一个list，用来存放对比数据某一行的所有列

40

41 Object [] objArr2 = null;//定义一个对象数组，存放每一行数据

42 List rowList = null;//用来存放第二个excel某一行数据43

44 //遍历行 第一个excel

45 first: for (int i=0;i

47 objArr1 = objects1.get(i);//将excel1的每行数据存到objArr1

48

49 columnsList = Arrays.asList(objArr1);//将每一行对象数组转为list，为了某行某列的值50

51 //遍历行 第二个excel

52 second: for (int k=0;k

54 //遍历列 第一个excel

55 for (int j=0;j

57 objArr2 = objects2.get(k);//将excel2的每行数据村到objArr2

58

59 rowList = Arrays.asList(objArr2);//数组转换list，为了比较excel2中的某一行是否存在excel1中的某一行所有列数据

60

61 boolean contains = rowList.contains(columnsList.get(j));//rowList是否包含columnsList(j)

62 if (!contains){63 if (k==objects2.size()-1){64 log.info("第"+(i+1)+"行-----"+Arrays.toString(objArr1));65 }66 break;67 }68 if (j==columnsList.size()-1){69 breaksecond;70 }71

72 }73

74 }75

76 }77 } catch(Exception e) {78 e.printStackTrace();79 }finally{80 try{81 if (fis1 != null){82 fis1.close();83 }84 } catch(IOException e) {85 e.printStackTrace();86 }87 try{88 if (fis2 != null){89 fis2.close();90 }91 } catch(IOException e) {92 e.printStackTrace();93 }94 }95 }96

97

98 /**

99 * 读取excel数据,调用这方法开始100 *101 *@paramis102 *@paramindexNum 至少需要多少列数据103 */

104 public static List readExcelToObj(InputStream is, intindexNum) {105

106 Workbook wb = null;107 List objArrList = null;108 try{109 objArrList = new ArrayList<>();110 wb =WorkbookFactory.create(is);111 int num =wb.getNumberOfSheets();112 readExcel(wb, 0, 0, 0, objArrList, indexNum);113 } catch(Exception e) {114 e.printStackTrace();115 }116 returnobjArrList;117 }118

119 /**

120 * 读取excel文件121 *122 *@paramwb123 *@paramsheetIndex sheet页下标：从0开始124 *@paramstartReadLine 开始读取的行:从0开始125 *@paramtailLine 去除最后读取的行126 */

127 static Long startMills = null;128 static Long endMills = null;129

130 public static void readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine, List objArrList, intindexNum) {131 startMills =System.currentTimeMillis();132 Sheet sheet =wb.getSheetAt(sheetIndex);133 Row row = null;134

135 for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {136 row =sheet.getRow(i);137 int CellNum =row.getLastCellNum();138 List objList = new ArrayList<>();139 for (int j = 0; j < row.getLastCellNum(); j++) {140 //for(Cell c : row) {

141 Cell c =row.getCell(j);142 if (c == null) {143 objList.add("");144 continue;145 }146 Integer isMerge =isMergedRegion(sheet, i, c.getColumnIndex());147 //判断是否具有合并单元格

148 if (isMerge != null) {149 String rs =getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());150 j= j+isMerge;151 objList.add(rs);152 } else{153 objList.add(getCellValue(c));154 }155

156 }157 while (objList.size()

165 /**

166 * 判断指定的单元格是否是合并单元格167 *168 *@paramsheet169 *@paramrow 行下标170 *@paramcolumn 列下标171 *@return

172 */

173 public static Integer isMergedRegion(Sheet sheet, int row, intcolumn) {174 int sheetMergeCount =sheet.getNumMergedRegions();175 /*

176 * 得到所bai有的合并单元格 sourceSheet.getNumMergedRegions();177 * 得到某一个合du并单元格 CellRangeAddress oldRange=sourceSheet.getMergedRegion(i);178 * 起始行 oldRange.getFirstRow() ；179 * zhi 结束行oldRange.getLastRow()180 * 起始列oldRange.getFirstColumn()181 * 结束列oldRange.getLastColumn()*/

182 for (int i = 0; i < sheetMergeCount; i++) {183 CellRangeAddress range =sheet.getMergedRegion(i);184 int firstColumn =range.getFirstColumn();185 int lastColumn =range.getLastColumn();186 int firstRow =range.getFirstRow();187 int lastRow =range.getLastRow();188 if (row >= firstRow && row <=lastRow) {189 if (column >= firstColumn && column <=lastColumn) {190 return lastColumn -firstColumn;191 }192 }193 }194 return null;195 }196

197 /**

198 * 获取合并单元格的值199 *200 *@paramsheet201 *@paramrow202 *@paramcolumn203 *@return

204 */

205 public static String getMergedRegionValue(Sheet sheet, int row, intcolumn) {206 int sheetMergeCount =sheet.getNumMergedRegions();207

208 for (int i = 0; i < sheetMergeCount; i++) {209 CellRangeAddress ca =sheet.getMergedRegion(i);210 int firstColumn =ca.getFirstColumn();211 int lastColumn =ca.getLastColumn();212 int firstRow =ca.getFirstRow();213 int lastRow =ca.getLastRow();214

215 if (row >= firstRow && row <=lastRow) {216 if (column >= firstColumn && column <=lastColumn) {217 Row fRow =sheet.getRow(firstRow);218 Cell fCell =fRow.getCell(firstColumn);219 returngetCellValue(fCell);220 }221 }222 }223

224 return null;225 }226

227 /**

228 * 获取单元格的值229 *230 *@paramcell231 *@return

232 */

233 public staticString getCellValue(Cell cell) {234

235 if (cell == null) return "";236

237 if (cell.getCellType() ==Cell.CELL_TYPE_STRING) {238

239 returncell.getStringCellValue();240

241 } else if (cell.getCellType() ==Cell.CELL_TYPE_BOOLEAN) {242

243 returnString.valueOf(cell.getBooleanCellValue());244

245 } else if (cell.getCellType() ==Cell.CELL_TYPE_FORMULA) {246

247 returncell.getCellFormula();248

249 } else if (cell.getCellType() ==Cell.CELL_TYPE_NUMERIC) {250

251 returnString.valueOf(cell.getNumericCellValue());252

253 }254 return "";255 }256 }