List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:testpoi.Department.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {/*from w ww. ja v a 2 s . c o m*/ file1 = new FileInputStream(new File(path + "all.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); } 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 = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 203, true)); deptts.add(new Department("Surgery", 113, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, 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)); /***********************************************************************/ generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); 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.OldDepttSheet.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {// w ww . j a v a 2 s . co 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 = 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 {/* ww w. j ava 2 s. 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 = 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(); } }
From source file:tr.org.liderahenk.liderconsole.core.utils.SWTResourceManager.java
License:Open Source License
private static XSSFWorkbook createWorkbookFromTable(TableViewer tableViewer, String sheetName) { // Create workbook & sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName == null ? "Sheet1" : sheetName); // Shade the background of the header row XSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setAlignment(HorizontalAlignment.CENTER); // Add header row Table table = tableViewer.getTable(); TableColumn[] columns = table.getColumns(); int rowIndex = 0; int cellIndex = 0; XSSFRow header = sheet.createRow((short) rowIndex++); for (TableColumn column : columns) { XSSFCell cell = header.createCell(cellIndex++); cell.setCellValue(column.getText()); cell.setCellStyle(headerStyle);/* w w w. ja va2s . c o m*/ } // Add data rows TableItem[] items = tableViewer.getTable().getItems(); for (TableItem item : items) { // create a new row XSSFRow row = sheet.createRow((short) rowIndex++); cellIndex = 0; for (int i = 0; i < columns.length; i++) { // Create a new cell XSSFCell cell = row.createCell(cellIndex++); String text = item.getText(i); // Set the horizontal alignment (default to RIGHT) XSSFCellStyle cellStyle = wb.createCellStyle(); if (LiderCoreUtils.isInteger(text)) { cellStyle.setAlignment(HorizontalAlignment.RIGHT); } else if (LiderCoreUtils.isValidDate(text, ConfigProvider.getInstance().get(LiderConstants.CONFIG.DATE_FORMAT))) { cellStyle.setAlignment(HorizontalAlignment.CENTER); } else { cellStyle.setAlignment(HorizontalAlignment.LEFT); } cell.setCellStyle(cellStyle); // Set the cell's value cell.setCellValue(text); } } // Auto-fit the columns for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn((short) i); } return wb; }
From source file:trei_big.criar_planilha_excel.java
public criar_planilha_excel(String caminho_salvar, String nome_da_tabela) { //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet(nome_da_tabela); //This data needs to be written (Object[]) Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>(); Vector<Vector<String>> dados = banco.obter_dados_da_tabela(nome_da_tabela); data.put(0, banco.nome_colunas_consulta.toArray(new Object[] {})); for (int i = 0; i < dados.size(); i++) { Vector<String> get = dados.get(i); data.put((i + 1), get.toArray(new Object[] {})); }//ww w. jav a 2s .c o m //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Integer) { cell.setCellValue((Integer) obj); } } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(caminho_salvar, nome_da_tabela + extensao)); workbook.write(out); out.close(); aviso.mensagem_sucesso("Planilha '" + nome_da_tabela + extensao + "' criada com sucesso!"); } catch (Exception e) { e.printStackTrace(); } }
From source file:tubessc.Dataset.java
public void calculateFluctuation(String InputFile, String OutputFile) throws FileNotFoundException, IOException { FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFWorkbook output = new XSSFWorkbook(); XSSFSheet sheetOutput = output.createSheet("new sheet"); FileOutputStream fileOut = new FileOutputStream(OutputFile); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); for (int i = rowStart; i <= rowEnd - 1; i++) { Row rowIn1 = sheet.getRow(i);//from w w w . j a v a 2 s .co m Cell cellIn1 = rowIn1.getCell(0); Row rowIn2 = sheet.getRow(i + 1); Cell cellIn2 = rowIn2.getCell(0); double value1 = Double.parseDouble(String.valueOf(cellIn1.getNumericCellValue())); double value2 = Double.parseDouble(String.valueOf(cellIn2.getNumericCellValue())); Row rowOut = sheetOutput.createRow(i); Cell cellOut = rowOut.createCell(0); cellOut.setCellValue(value2 - value1); } output.write(fileOut); fileOut.close(); }
From source file:tubessc.Dataset.java
public void normalization(String InputFile, String outputFile, double minValue, double maxValue) throws FileNotFoundException, IOException { this.minValue = minValue; this.maxValue = maxValue; FileInputStream file = new FileInputStream(new File(InputFile)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFWorkbook output = new XSSFWorkbook(); XSSFSheet sheetOutput = output.createSheet("new sheet"); FileOutputStream fileOut = new FileOutputStream(outputFile); int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); Row row = sheet.getRow(rowStart);/*from w w w.j a v a2s .co m*/ Cell cell = row.getCell(0); max = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); min = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); for (int i = rowStart + 1; i <= rowEnd; i++) { row = sheet.getRow(i); cell = row.getCell(0); double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); if (value > max) { max = value; } if (value < min) { min = value; } } for (int i = rowStart; i <= rowEnd; i++) { row = sheet.getRow(i); cell = row.getCell(0); double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue())); double newValue = minValue + ((value - min) * (maxValue - minValue) / (max - min)); Row rowOut = sheetOutput.createRow(i); Cell cellOut = rowOut.createCell(0); cellOut.setCellValue(newValue); } output.write(fileOut); fileOut.close(); }
From source file:tubessc.TubesSC.java
public static void main(String[] args) { // TODO code application logic here try {// w w w. j av a2s. com int x = 0; XSSFWorkbook output = new XSSFWorkbook(); XSSFSheet sheetOutput = output.createSheet("new sheet"); FileOutputStream fileOut = new FileOutputStream("F:\\JST\\dataset\\goldPrice\\HasilFINAL.xlsx"); ds = new Dataset(); ds.calculateFluctuation("F:\\JST\\dataset\\goldPrice\\training.xlsx", "F:\\JST\\dataset\\goldPrice\\trainingFluctuation.xlsx"); ds.normalization("F:\\JST\\dataset\\goldPrice\\trainingFluctuation.xlsx", "F:\\JST\\dataset\\goldPrice\\trainingNormalization.xlsx", 0.1, 0.9); ds.calculateFluctuation("F:\\JST\\dataset\\goldPrice\\testing.xlsx", "F:\\JST\\dataset\\goldPrice\\testingFluctuation.xlsx"); ds.normalization("F:\\JST\\dataset\\goldPrice\\testingFluctuation.xlsx", "F:\\JST\\dataset\\goldPrice\\testingNormalization.xlsx", 0.1, 0.9); int totalCombination = varInput.length * varHidden.length * varPop.length * times; int progress = 0; for (int i = 0; i < varInput.length; i++) { input = varInput[i]; ds.clearDatasetTraining(); ds.clearDatasetTesting(); ds.addDataSetTrainingExcel("F:\\JST\\dataset\\goldPrice\\trainingNormalization.xlsx", TubesSC.input); ds.addDataSetTestingExcel("F:\\JST\\dataset\\goldPrice\\testingNormalization.xlsx", TubesSC.input); for (int j = 0; j < varHidden.length; j++) { neuron = varHidden[j]; nhidden = ((input + bias) * neuron); noutput = (neuron + bias); total = nhidden + noutput; batas = total - noutput; for (int k = 0; k < varPop.length; k++) { numOfGen = varGen[k]; numOfPop = varPop[k]; for (int l = 0; l < times; l++) { Row r = sheetOutput.createRow(progress); Kromosom fittest = null; double fittestBefore = 0; Populasi pop = new Populasi(numOfPop, true, ds); for (int y = 0; y < numOfGen; y++) { pop = EP.Evolution(pop); fittest = pop.getFittest(); System.out.println(fittest.getFitness()); } JST jst = new JST(fittest.getW1(), fittest.getW2(), fittest.getB1(), fittest.getB2(), ds); double MAPE = jst.getMape(true); System.out.println(MAPE); //System.out.println("final MAPE = " + MAPE); for (int z = 0; z < 6; z++) { Cell c = r.createCell(z); switch (z) { case 0: c.setCellValue(input); break; case 1: c.setCellValue(neuron); break; case 2: c.setCellValue(numOfGen); break; case 3: c.setCellValue(numOfPop); break; case 4: c.setCellValue(fittest.getFitness()); break; case 5: c.setCellValue(MAPE); break; } } x++; progress++; System.out.println("completed: " + progress + "/" + totalCombination); } } } } output.write(fileOut); fileOut.close(); } catch (IOException ex) { Logger.getLogger(TubesSC.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:uk.gov.ofwat.fountain.api.table.POITableRenderer.java
License:Open Source License
public Workbook renderTable(XSSFWorkbook workBook, TableStructure tableStructure, DataTable table) { this.workBook = workBook; creationHelper = workBook.getCreationHelper(); sheet = workBook.createSheet(table.getCompany().getCode() + " Table " + tableStructure.getTableName()); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); inputDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>(); copyCellDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>(); calcDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>(); yellow = new XSSFColor(new java.awt.Color(255, 255, 0)); lightYellow = new XSSFColor(new java.awt.Color(255, 255, 224)); lightBlue = new XSSFColor(new java.awt.Color(224, 255, 255)); pink = new XSSFColor(new java.awt.Color(255, 204, 204)); // Styles/*from w ww . ja v a2 s . c o m*/ // Row header style rowHeaderStyle = workBook.createCellStyle(); // Col header style colHeaderStyle = workBook.createCellStyle(); colHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); colHeaderStyle.setFillForegroundColor(yellow); Font colHeaderFont = workBook.createFont(); colHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD); colHeaderStyle.setFont(colHeaderFont); // Copycell text data cell style copyCellTextStyle = workBook.createCellStyle(); copyCellTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); copyCellTextStyle.setFillForegroundColor(pink); // Input text data cell style inputDataTextStyle = workBook.createCellStyle(); inputDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); inputDataTextStyle.setFillForegroundColor(lightYellow); // Calc text data cell style calcDataTextStyle = workBook.createCellStyle(); calcDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); calcDataTextStyle.setFillForegroundColor(lightBlue); // Input CG style inputCGStyle = workBook.createCellStyle(); inputCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); inputCGStyle.setFillForegroundColor(lightYellow); // Input CG style copyCellCGStyle = workBook.createCellStyle(); copyCellCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); copyCellCGStyle.setFillForegroundColor(pink); // Calc CG style calcCGStyle = workBook.createCellStyle(); calcCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); calcCGStyle.setFillForegroundColor(lightBlue); // data format DataFormat format = workBook.createDataFormat(); int rownum = 1; // starting point Row infoRow1 = sheet.createRow(rownum); CellStyle style = workBook.createCellStyle(); Font font = workBook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); Cell titleCell1 = infoRow1.createCell(0); titleCell1.setCellType(Cell.CELL_TYPE_STRING); String DATE_FORMAT = "dd MMM yyyy h:mm"; SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT); Calendar c1 = Calendar.getInstance(); // today String today = sdf.format(c1.getTime()); RichTextString dateRts = creationHelper.createRichTextString(today + ": " + tableStructure.getModelPage().getModel().getCode() + " for " + table.getCompany().getName()); titleCell1.setCellValue(dateRts); titleCell1.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 9)); rownum++; Row infoRow2 = sheet.createRow(rownum); Cell titleCell = infoRow2.createCell(0); titleCell.setCellType(Cell.CELL_TYPE_STRING); RichTextString rts = creationHelper.createRichTextString(tableStructure.getModelPage().getTable().getName() + " - " + tableStructure.getModelPage().getTableDescription()); titleCell.setCellValue(rts); titleCell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 4)); rownum++; rownum++; if (tableStructure.getModelPage().isGroupSelect()) { // group dropdown groupSelectTable(tableStructure, table, workBook, sheet, format, rownum); } else { tableWithoutGroupSelect(tableStructure, table, workBook, sheet, format, rownum); } return workBook; }
From source file:Utility.CSV_File_Generator.java
public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) { FileOutputStream out = null;// ww w . j a v a2 s.c o m try { out = new FileOutputStream(new File(file_details("Excel_Traffic_Rows"))); int col_index; //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet intermediate = workbook.createSheet("Test Data"); //Create row object Row row; XSSFRow intermediate_row; XSSFRow actual_row = original_sheet.getRow(index); //This data needs to be written (Object[]) TreeMap<String, TreeMap<String, Cell>> row_map = new TreeMap<String, TreeMap<String, Cell>>(); if (index == 0) { TreeMap<String, Cell> cols = new TreeMap<String, Cell>(); XSSFRow temp = intermediate.createRow(index); Iterator<Cell> cellIterator = actual_row.cellIterator(); int i = 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cols.put(Integer.toString(i++), cell); } row_map.put("0", cols); Set<String> keyid = row_map.get("0").keySet(); int cellid = 0; for (String key : keyid) { Cell original = cols.get(key); Cell cell = temp.createCell(cellid++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(original.getStringCellValue()); break; } } workbook.write(out); row_map.clear(); } else { int count = 0; while (count < index) { Iterator<Row> rowIterator = resultSheet.iterator(); while (rowIterator.hasNext()) { //Reading a row from the existing result sheet TreeMap<String, Cell> data_row = new TreeMap<String, Cell>(); row = rowIterator.next(); Iterator<Cell> cell = row.cellIterator(); col_index = 0; while (cell.hasNext()) { Cell c = cell.next(); data_row.put(Integer.toString(col_index++), c); } row_map.put(Integer.toString(count), data_row); count++; } //writing the row read into the new workbook(intermediate) Set<String> keyid = row_map.keySet(); for (String key : keyid) { int column_counter = 0; intermediate_row = intermediate.createRow(Integer.parseInt(key)); TreeMap<String, Cell> map = row_map.get(key); Set<String> row_data = map.keySet(); for (String cell_data : row_data) { Cell original = map.get(cell_data); Cell new_cell = intermediate_row.createCell(column_counter++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: new_cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: new_cell.setCellValue(original.getStringCellValue()); break; } } } } XSSFRow temp = intermediate.createRow(index); Iterator<Cell> cellIterator = actual_row.cellIterator(); TreeMap<String, Cell> required_data = new TreeMap<String, Cell>(); int i = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); required_data.put(Integer.toString(i++), cell); } row_map.put(Integer.toString(index), required_data); required_data = row_map.get(Integer.toString(index)); Set<String> keyid = required_data.keySet(); int cellid = 0; for (String key : keyid) { Cell original = required_data.get(key); Cell cell = temp.createCell(cellid++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(original.getStringCellValue()); break; } } workbook.write(out); out.flush(); row_map.clear(); } out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Traffic Data is: " + index + " row."); }