Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.cms.utils; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.TreeSet; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; 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.CellRangeAddress; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; /** * * @author vtsoft */ public class ExcelReader { public static List importExcel(File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack) throws FileNotFoundException { List lst = new ArrayList(); FileInputStream flieInput = new FileInputStream(file); HSSFWorkbook workbook; try { workbook = new HSSFWorkbook(flieInput); HSSFSheet worksheet = workbook.getSheetAt(iSheet); int irowBack = 0; for (int i = iBeginRow; i <= worksheet.getLastRowNum(); i++) { Object[] obj = new Object[iToCol - iFromCol + 1]; Row row = worksheet.getRow(i); if (row != null) { int iCount = 0; int check = 0; for (int j = iFromCol; j <= iToCol; j++) { Cell cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj[iCount] = cell.getStringCellValue().trim(); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); obj[iCount] = DateTimeUtils.convertDateToString(date, "dd/MM/yyyy"); } else { Double doubleValue = (Double) cell.getNumericCellValue(); //String.format("%.0f", doubleValue); List<String> lstValue = DataUtil.splitDot(String.valueOf(doubleValue)); if (lstValue.get(1).matches("[0]+")) { obj[iCount] = lstValue.get(0); } else { obj[iCount] = String.format("%.2f", doubleValue).trim(); } } break; case Cell.CELL_TYPE_BLANK: check++; break; } } else { obj[iCount] = null; } iCount += 1; } if (check != (iToCol - iFromCol + 1)) { lst.add(obj); } } else { irowBack += 1; } if (irowBack == rowBack) { break; } } } catch (IOException ex) { lst = null; } return lst; } /** * TiepNv6 */ public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) { copySheets(newSheet, sheet, true); } public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } } public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } } public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } } public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = (CellRangeAddress) sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; } } return null; } private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Collection<CellRangeAddress> mergedRegions) { return !mergedRegions.contains(newMergedRegion); } }