List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getSheetName
@Override
public String getSheetName()
From source file:org.talend.repository.ui.wizards.metadata.connection.files.excel.ExcelReader.java
License:Open Source License
private void init() throws BiffException, IOException { // hywang modified for excel 2007 // if (excelPath.endsWith(".xls")) { //$NON-NLS-1$ // isXlsx = false; // } else if (excelPath.endsWith(".xlsx")) { //$NON-NLS-1$ // isXlsx = true; // }/*from w w w .j a v a2s . co m*/ if (!isXlsx) { WorkbookSettings worksetting = new WorkbookSettings(); //worksetting.setEncoding("ISO-8859-15"); //$NON-NLS-1$ worksetting.setCellValidationDisabled(true); worksetting.setSuppressWarnings(true); workbook = Workbook.getWorkbook(new File(excelPath), worksetting); } else { // modify for bug 12174. File file = new File(excelPath); OPCPackage clone = null; try { FileInputStream in = new FileInputStream(file); OPCPackage open = OPCPackage.open(in); clone = PackageHelper.clone(open, createTempFile()); open.close(); // Package createPackage = Package.openOrCreate(file); // clone = PackageHelper.clone(createPackage); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (OpenXML4JException e) { e.printStackTrace(); } if (clone != null) { List<String> sheetlist = new ArrayList<String>(); // modified for bug TDI-26614, Use XSSF and SAX (Event API) to parse excel 2007, only need small memory // footprint if (isXlsx && (EVENT_MODE).equals(generationMode)) { try { XSSFReader xssfReader = new XSSFReader(clone); XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); while (sheets.hasNext()) { sheets.next(); String sheetName = sheets.getSheetName(); sheetlist.add(sheetName); } } catch (OpenXML4JException e) { CommonExceptionHandler.process(e); } } else { xwb = new XSSFWorkbook(clone); for (XSSFSheet sheet : xwb) { sheetlist.add(sheet.getSheetName()); } } sheetNamesForXlsx = new String[sheetlist.size()]; for (int i = 0; i < sheetlist.size(); i++) { sheetNamesForXlsx[i] = sheetlist.get(i); } sheetlist.clear(); } } }
From source file:org.tiefaces.components.websheet.chart.ChartHelper.java
License:MIT License
/** * initial chart map for XSSF format file. XSSF file is actually the only * format in POI support chart object.//from w w w . j av a 2 s . c om * * @param wb * xssf workbook. * @param chartsData * the charts data */ private void initXSSFChartsMap(final XSSFWorkbook wb, final ChartsData chartsData) { initAnchorsMap(wb, chartsData); Map<String, ClientAnchor> anchorMap = chartsData.getChartAnchorsMap(); Map<String, BufferedImage> chartMap = chartsData.getChartsMap(); Map<String, ChartData> chartDataMap = chartsData.getChartDataMap(); chartMap.clear(); chartDataMap.clear(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { XSSFSheet sheet = wb.getSheetAt(i); XSSFDrawing drawing = sheet.createDrawingPatriarch(); List<XSSFChart> charts = drawing.getCharts(); if ((charts != null) && (!charts.isEmpty())) { for (XSSFChart chart : charts) { generateSingleXSSFChart(chart, getChartIdFromParent(chart, sheet.getSheetName()), sheet, anchorMap, chartMap, chartDataMap); } } } }
From source file:org.tiefaces.components.websheet.utility.ChartUtility.java
License:MIT License
/** * Inits the XSSF anchors map for sheet. * * @param anchortMap//from w ww .ja v a 2 s .c o m * the anchort map * @param positionMap * the position map * @param sheet * the sheet */ private static void initXSSFAnchorsMapForSheet(final Map<String, ClientAnchor> anchortMap, final Map<String, String> positionMap, final XSSFSheet sheet) { XSSFDrawing drawing = sheet.createDrawingPatriarch(); CTDrawing ctDrawing = drawing.getCTDrawing(); if (ctDrawing.sizeOfTwoCellAnchorArray() <= 0) { return; } List<CTTwoCellAnchor> alist = ctDrawing.getTwoCellAnchorList(); for (int j = 0; j < alist.size(); j++) { CTTwoCellAnchor ctanchor = alist.get(j); String singleChartId = getAnchorAssociateChartId(ctanchor); if (singleChartId != null) { String chartId = sheet.getSheetName() + "!" + singleChartId; int dx1 = (int) ctanchor.getFrom().getColOff(); int dy1 = (int) ctanchor.getFrom().getRowOff(); int dx2 = (int) ctanchor.getTo().getColOff(); int dy2 = (int) ctanchor.getTo().getRowOff(); int col1 = ctanchor.getFrom().getCol(); int row1 = ctanchor.getFrom().getRow(); int col2 = ctanchor.getTo().getCol(); int row2 = ctanchor.getTo().getRow(); anchortMap.put(chartId, new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)); positionMap.put(WebSheetUtility.getFullCellRefName(sheet.getSheetName(), row1, col1), chartId); } } }
From source file:org.tiefaces.components.websheet.utility.PicturesUtility.java
License:MIT License
/** * save pciture in map with index.//w ww . j a va 2 s . c o m * * @param picMap * pciture map. * @param sheet * sheet. * @param dr * documentme part. */ private static void indexPictureInMap(final Map<String, Picture> picMap, final XSSFSheet sheet, final POIXMLDocumentPart dr) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { if (shape instanceof XSSFPicture) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor anchor = pic.getPreferredSize(); CTMarker ctMarker = anchor.getFrom(); String picIndex = WebSheetUtility.getFullCellRefName(sheet.getSheetName(), ctMarker.getRow(), ctMarker.getCol()); picMap.put(picIndex, pic); } } } }
From source file:org.tuxedoberries.transformo.excel.XLSXTableMetaReader.java
License:Open Source License
private TableMeta generateTableMeta(XSSFSheet sheet) { int totalRows = sheet.getLastRowNum(); if (totalRows < ROW_META_COUNT) { Logger.Warning("Total number of rows less than expected [%d]. %d Rows are expected", totalRows, ROW_META_COUNT);/*from w w w.j av a2s . c om*/ return null; } TableMeta meta = new TableMeta(); meta.TableName = sheet.getSheetName(); if (meta.TableName.startsWith("!")) return null; generateTypes(sheet.getRow(0), meta); generateShortNames(sheet.getRow(1), meta); generateNames(sheet.getRow(2), meta); return meta; }
From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java
License:Open Source License
@RequestMapping(method = RequestMethod.POST) protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile, @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response) throws Exception { // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory? File file = multipartToFile(uploadFile); String mergedResultFileName = "merged_" + file.getName(); FileInputStream fis = new FileInputStream(file); // Finds the workbook instance of XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fis); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); // number of sheets in the workbook int numberOfSheets = workbook.getNumberOfSheets(); // contains all values of the merge column across all sheets ArrayList<String> mergeColumnValues = new ArrayList<String>(); // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>> HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>(); // maps sheet index to the headers in that sheet HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>(); // how many copies of headers need to be created for each sheet HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>(); // loop through the sheets in the workbook and populate the variables for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); int mergeColumnIndex = -1; // index of the merge column in this sheet int rowIteratorIndex = 0; // index of current row iteration // collect all of the merge column rows in each sheet Iterator<Row> rowIterator = sheet.rowIterator(); int maxSheetRowCountForCurrentSheet = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (rowIteratorIndex == 0) { // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders // and add it to sheetIndexToSheetColumnHeaders ArrayList<String> sheetColumnHeaders = new ArrayList<String>(); int rowCellIteratorIndex = 0; Iterator<Cell> topRowCellIterator = row.cellIterator(); while (topRowCellIterator.hasNext()) { Cell topRowCell = topRowCellIterator.next(); String topRowCellString = topRowCell.toString(); if (!topRowCellString.isEmpty()) { sheetColumnHeaders.add(topRowCellString); }//from w w w. j av a 2s . c o m if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) { // this is the mergeColumn. Remember the column index if (mergeColumnIndex == -1) { mergeColumnIndex = rowCellIteratorIndex; } else { // there are multiple mergeColumnTitles in this sheet. Let the user know and exit ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets"); mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle + "\" in worksheet #" + (sheetIndex + 1) + ". You can have only one merge column per worksheet. Please fix and try again."); return mav; } } rowCellIteratorIndex++; } sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders); } else { // for rows that are not the top row (header) // 1. get all the mergeColumnValues // 2. populate mergeColumnValueToSheetRows // 3. calculate sheetIndexToMaxSheetRowCount Cell mergeColumnValueCell = row.getCell(mergeColumnIndex); if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(mergeColumnValueCell); String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell, objFormulaEvaluator); HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows .get(mergeColumnValueString); if (sheetIndexToSheetRows == null) { sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>(); mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows); } ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex); if (sheetRows == null) { sheetRows = new ArrayList<>(); sheetIndexToSheetRows.put(sheetIndex, sheetRows); } sheetRows.add(row); if (sheetRows.size() > maxSheetRowCountForCurrentSheet) { maxSheetRowCountForCurrentSheet = sheetRows.size(); } Iterator<Cell> rowCellIterator = row.cellIterator(); int rowCellIteratorIndex = 0; while (rowCellIterator.hasNext()) { Cell rowCell = rowCellIterator.next(); if (rowCellIteratorIndex == mergeColumnIndex) { // this is a merge column cell, so add its value to mergeColumnValues if (!rowCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(rowCell); String rowCellValueString = objDefaultFormat.formatCellValue(rowCell, objFormulaEvaluator); if (!mergeColumnValues.contains(rowCellValueString)) { mergeColumnValues.add(rowCellValueString); } } } rowCellIteratorIndex++; } } } rowIteratorIndex++; } sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet); } // Now we are ready to make the merge sheet. We will be writing one row at a time. Workbook wb = new XSSFWorkbook(); // new output workbook Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet // make the header row Row headerRow = mergedSheet.createRow(0); // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues. Cell mergeColumnHeaderCell = headerRow.createCell(0); mergeColumnHeaderCell.setCellValue(mergeColumnTitle); // current column index "cursor" where we will be writing to int cellIndexWithoutMergeColumn = 1; // make the header row for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); XSSFSheet sheet = workbook.getSheetAt(sheetIndex); String sheetName = sheet.getSheetName(); for (int i = 0; i < maxSheetRowCount; i++) { for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders .size(); sheetColumnHeaderIndex++) { String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex); if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) { String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) "; Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn); headerCell.setCellValue(newSheetColumnHeader); cellIndexWithoutMergeColumn++; } } } } // now make all the non-header rows for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues .size(); mergeColumnValueIndex++) { String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex); HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows .get(mergeColumnValue); if (mergeColumnValueSheetRow == null) { System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: " + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue); continue; } Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row; // reset current cursor as we make each row cellIndexWithoutMergeColumn = 0; // first column will be the merge column value Cell mergeColumnCell = row.createCell(0); mergeColumnCell.setCellValue(mergeColumnValue); cellIndexWithoutMergeColumn++; for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex); int currentSheetSheetRowIndex = 0; ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); if (sheetRows != null) { for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) { Row sheetRow = sheetRows.get(sheetRowIndex); for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) { String sheetHeader = sheetHeaders.get(sheetHeaderIndex); if (!sheetHeader.equals(mergeColumnTitle)) { Cell cell = sheetRow.getCell(sheetHeaderIndex); Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); objFormulaEvaluator.evaluate(cell); String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); exportCell.setCellValue(cellString); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } // some columns do not have any values to populate, so populate them with empty cells Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); while (currentSheetSheetRowIndex < maxSheetRowCount) { for (int i = 0; i < sheetHeaders.size(); i++) { String sheetHeader = sheetHeaders.get(i); if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) { Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); exportCell.setCellValue(""); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } } // write to response output response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\""); ServletOutputStream outputStream = response.getOutputStream(); wb.write(outputStream); fis.close(); return null; }
From source file:poi.xslf.usermodel.PieChartDemo.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length < 2) { usage();//w w w. j a v a 2 s . c o m return; } BufferedReader modelReader = new BufferedReader(new FileReader(args[1])); String chartTitle = modelReader.readLine(); // first line is chart title XMLSlideShow pptx = new XMLSlideShow(new FileInputStream(args[0])); XSLFSlide slide = pptx.getSlides()[0]; // find chart in the slide XSLFChart chart = null; for (POIXMLDocumentPart part : slide.getRelations()) { if (part instanceof XSLFChart) { chart = (XSLFChart) part; break; } } if (chart == null) throw new IllegalStateException("chart not found in the template"); // embedded Excel workbook that holds the chart data POIXMLDocumentPart xlsPart = chart.getRelations().get(0); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); CTChart ctChart = chart.getCTChart(); CTPlotArea plotArea = ctChart.getPlotArea(); CTPieChart pieChart = plotArea.getPieChartArray(0); //Pie Chart Series CTPieSer ser = pieChart.getSerArray(0); // Series Text CTSerTx tx = ser.getTx(); tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle); sheet.createRow(0).createCell(1).setCellValue(chartTitle); String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString(); tx.getStrRef().setF(titleRef); // Category Axis Data CTAxDataSource cat = ser.getCat(); CTStrData strData = cat.getStrRef().getStrCache(); // Values CTNumDataSource val = ser.getVal(); CTNumData numData = val.getNumRef().getNumCache(); strData.setPtArray(null); // unset old axis text numData.setPtArray(null); // unset old values // set model int idx = 0; int rownum = 1; String ln; while ((ln = modelReader.readLine()) != null) { String[] vals = ln.split("\\s+"); CTNumVal numVal = numData.addNewPt(); numVal.setIdx(idx); numVal.setV(vals[1]); CTStrVal sVal = strData.addNewPt(); sVal.setIdx(idx); sVal.setV(vals[0]); idx++; XSSFRow row = sheet.createRow(rownum++); row.createCell(0).setCellValue(vals[0]); row.createCell(1).setCellValue(Double.valueOf(vals[1])); } numData.getPtCount().setVal(idx); strData.getPtCount().setVal(idx); String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true); val.getNumRef().setF(numDataRange); String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true); cat.getStrRef().setF(axisDataRange); // updated the embedded workbook with the data OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream(); wb.write(xlsOut); xlsOut.close(); // save the result FileOutputStream out = new FileOutputStream("pie-chart-demo-output.pptx"); pptx.write(out); out.close(); }
From source file:py.gov.datos.XlsToCsvConverter.java
License:GNU General Public License
/** * Convierte un nico archivo XLSX a uno o ms archivos CSV. * * @param file el archivo a convertir./* ww w .java2 s . c o m*/ * @param path la ruta del directorio de salida. * @return los archivos generados. */ private List<File> convert(File file, String path) { List<File> result = new ArrayList<File>(); try { XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file)); File csvDir = new File(path + "csv/"); csvDir.mkdir(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); File outputFile = new File(path + "csv/" + sheet.getSheetName() + ".csv"); if (outputFile.createNewFile()) { FileOutputStream out = new FileOutputStream(outputFile); StringBuffer content = this.convertSheet(sheet); //System.out.println(content); out.write(content.toString().getBytes()); result.add(outputFile); out.flush(); out.close(); } else { LOG.error("Can not create output file"); } } } catch (IOException e) { e.printStackTrace(); } return result; }
From source file:testpoi.OldDepttSheet.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {//from ww w .j a v a 2s . c om file1 = new FileInputStream(new File(path + "new.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); fileOldIn = new FileInputStream(new File(path + "old.xlsx")); workbookOld = new XSSFWorkbook(fileOldIn); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Patient Type"); cell = row.createCell(2); cell.setCellValue("CR No."); cell = row.createCell(3); cell.setCellValue("Name"); cell = row.createCell(4); cell.setCellValue("Guardian's Name"); cell = row.createCell(5); cell.setCellValue("Relation"); cell = row.createCell(6); cell.setCellValue("AgeYrs"); cell = row.createCell(7); cell.setCellValue("Gender"); cell = row.createCell(8); cell.setCellValue("Address"); cell = row.createCell(9); cell.setCellValue("City"); cell = row.createCell(10); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; /************************ TO SET AT EVERY RUN **************************/ crNo = 575; deptts = new ArrayList<>(); /* New */ deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 48, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); /* Old */ deptts.add(new Department("Medicine", 15, false)); deptts.add(new Department("Surgery", 13, false)); deptts.add(new Department("Obs & Gynae", 12, false)); deptts.add(new Department("Paediatrics", 9, false)); deptts.add(new Department("Orthopaedics", 11, false)); deptts.add(new Department("Ophthalmology", 16, false)); deptts.add(new Department("ENT", 6, false)); deptts.add(new Department("Dental", 8, false)); // Casualty is only new /***********************************************************************/ //Fill depttToOldSheetsMap Iterator<XSSFSheet> oldSheetsIter = workbookOld.iterator(); //Skip 1st sheet which contains all old patients oldSheetsIter.next(); depttToOldSheetsMap = new HashMap<>(); while (oldSheetsIter.hasNext()) { XSSFSheet oldSheet = oldSheetsIter.next(); depttToOldSheetsMap.put(oldSheet.getSheetName(), new OldDepttSheet(oldSheet)); } try { generateRows(); } catch (IllegalArgumentException e) { System.err.println(e.getMessage()); e.printStackTrace(); } try { FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi_.Department.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {/*from w ww. j a v a 2s . c o m*/ file1 = new FileInputStream(new File(path + "new.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); fileOldIn = new FileInputStream(new File(path + "old.xlsx")); workbookOld = new XSSFWorkbook(fileOldIn); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Patient Type"); cell = row.createCell(2); cell.setCellValue("CR No."); cell = row.createCell(3); cell.setCellValue("Name"); cell = row.createCell(4); cell.setCellValue("Guardian's Name"); cell = row.createCell(5); cell.setCellValue("Relation"); cell = row.createCell(6); cell.setCellValue("AgeYrs"); cell = row.createCell(7); cell.setCellValue("Gender"); cell = row.createCell(8); cell.setCellValue("Address"); cell = row.createCell(9); cell.setCellValue("City"); cell = row.createCell(10); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; /************************ TO SET AT EVERY RUN **************************/ crNo = 1050; deptts = new ArrayList<>(); /* New */ deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 20, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 42, true)); /* Old */ deptts.add(new Department("Medicine", 15, false)); deptts.add(new Department("Surgery", 13, false)); deptts.add(new Department("Obs & Gynae", 12, false)); deptts.add(new Department("Paediatrics", 9, false)); deptts.add(new Department("Orthopaedics", 11, false)); deptts.add(new Department("Ophthalmology", 16, false)); deptts.add(new Department("ENT", 6, false)); deptts.add(new Department("Dental", 8, false)); // Casualty is only new /***********************************************************************/ //Fill depttToOldSheetsMap Iterator<XSSFSheet> oldSheetsIter = workbookOld.iterator(); //Skip 1st sheet which contains all old patients oldSheetsIter.next(); depttToOldSheetsMap = new HashMap<>(); while (oldSheetsIter.hasNext()) { XSSFSheet oldSheet = oldSheetsIter.next(); depttToOldSheetsMap.put(oldSheet.getSheetName(), new OldDepttSheet(oldSheet)); } try { generateRows(); } catch (IllegalArgumentException e) { System.err.println(e.getMessage()); e.printStackTrace(); } try { FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }