Geek Answers Handbook

Creating multiple sheets in jExcel API

I need to create multiple excel sheets in Java using the jExcel API if one sheet is full (65536 rows) . Suppose that if one sheet is full, then on the next sheet he should start writing automatically from the place where he remained on the first sheet. I just got stuck on the fact that the logic created it dynamically every time one sheet was filled. Below is the code I've done so far.

 public void write() throws IOException, WriteException { File file = new File(inputFile); WorkbookSettings wbSettings = new WorkbookSettings(); wbSettings.setLocale(new Locale("en", "EN")); WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings); writingToExcel(workbook); } //Logic to create sheet dyanmically if one is full should be done here I guess? private void writingToExcel(WritableWorkbook workbook) { workbook.createSheet("Report", 0); WritableSheet excelSheet = workbook.getSheet(0); try { createLabel(excelSheet); createContent(excelSheet); } catch (WriteException e) { e.printStackTrace(); } finally { try { workbook.write(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } } } private void createLabel(WritableSheet sheet) throws WriteException { WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); times = new WritableCellFormat(times10pt); times.setWrap(true); WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,UnderlineStyle.SINGLE); timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline); timesBoldUnderline.setWrap(true); CellView cv = new CellView(); cv.setFormat(times); cv.setFormat(timesBoldUnderline); cv.setAutosize(true); // Write a few headers addCaption(sheet, 0, 0, "Header 1"); addCaption(sheet, 1, 0, "This is another header"); } private void createContent(WritableSheet sheet) throws WriteException, RowsExceededException { for (int i = 1; i < 70000; i++) { addNumber(sheet, 0, i, i + 10); addNumber(sheet, 1, i, i * i); } } private void addCaption(WritableSheet sheet, int column, int row, String s) throws RowsExceededException, WriteException { Label label; label = new Label(column, row, s, timesBoldUnderline); sheet.addCell(label); } private void addNumber(WritableSheet sheet, int column, int row, Integer integer) throws WriteException, RowsExceededException { Number number; number = new Number(column, row, integer, times); sheet.addCell(number); }

I am not sure how to add this logic to my code.

Any suggestions would be very helpful?

Or in any case, can someone provide me with a simple example, in which, if one sheet is full, he should start writing automatically on another sheet (second sheet)?

+4
source share
2 answers

I made changes to the following 2 methods: -

 private void writingToExcel(WritableWorkbook workbook) { try { // don't create a sheet now, instead, pass it in the workbook createContent(workbook); } catch (WriteException e) { e.printStackTrace(); } finally { try { workbook.write(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } } } // instead of taking a sheet, take a workbook because we cannot ensure if the sheet can fit all the content at this point private void createContent(WritableWorkbook workbook) throws WriteException { int excelSheetIndex = 0; int rowIndex = 0; WritableSheet excelSheet = null; for (int i = 1; i < 70000; i++) { // if the sheet has hit the cap, then create a new sheet, new label row and reset the row count if (excelSheet == null || excelSheet.getRows() == 65536) { excelSheet = workbook.createSheet("Report " + excelSheetIndex, excelSheetIndex++); createLabel(excelSheet); rowIndex = 0; } // instead of using i for row, use rowIndex addNumber(excelSheet, 0, rowIndex, i + 10); addNumber(excelSheet, 1, rowIndex, i * i); // increment the sheet row rowIndex++; } }
+4
source

public class DscMigration {

 private WritableCellFormat timesBoldUnderline; private WritableCellFormat times; private String inputFile; public void setOutputFile(String inputFile) { this.inputFile = inputFile; } public void write() throws IOException, WriteException { File file = new File(inputFile); WorkbookSettings wbSettings = new WorkbookSettings(); wbSettings.setLocale(new Locale("en", "EN")); WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings); workbook.createSheet("Report", 0); WritableSheet excelSheet = workbook.getSheet(0); createLabel(excelSheet); createContent(excelSheet); workbook.write(); workbook.close(); } private void createLabel(WritableSheet sheet) throws WriteException { // Lets create a times font WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); // Define the cell format times = new WritableCellFormat(times10pt); // Lets automatically wrap the cells times.setWrap(true); // Create create a bold font with unterlines WritableFont times10ptBoldUnderline = new WritableFont( WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE); timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline); // Lets automatically wrap the cells timesBoldUnderline.setWrap(true); CellView cv = new CellView(); cv.setFormat(times); // cv.setFormat(timesBoldUnderline); // cv.setFormat(cf) cv.setAutosize(true); // Write a few headers addCaption(sheet, 0, 0, "COM_ID"); addCaption(sheet, 1, 0, "OBJECTID"); addCaption(sheet, 2, 0, "GNOSISID"); } private void createContent(WritableSheet sheet) throws WriteException, RowsExceededException { /** * Create a new instance for cellDataList */ List<DataObj> cellDataListA = new ArrayList<DataObj>(); List<DataObj> nonDuplicateA = new ArrayList<DataObj>(); List<DataObj2> cellDataListB = new ArrayList<DataObj2>(); List<DataObj2> nonDuplicateB = new ArrayList<DataObj2>(); List<DataObj> nonDuplicateAB = new ArrayList<DataObj>(); List<DataObj> misMatchAB = new ArrayList<DataObj>(); List<DataObj> copyA = new ArrayList<DataObj>(); List<DataObj2> comID = new ArrayList<DataObj2>(); try { /** * Create a new instance for FileInputStream class */ // input1--> col1 -livelink id ; col2->livlink filename; col3-> gnosis id; col4-> filename FileInputStream fileInputStream = new FileInputStream( "C:/Documents and Settings/nithya/Desktop/DSC/Report/input1.xls"); //input2 --> col1 comid all, col2 -> object id for common FileInputStream fileInputStream2 = new FileInputStream( "C:/Documents and Settings/nithya/Desktop/DSC/Report/input2.xls"); /** * Create a new instance for POIFSFileSystem class */ POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); POIFSFileSystem fsFileSystem2 = new POIFSFileSystem(fileInputStream2); /* * Create a new instance for HSSFWorkBook Class */ HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet hssfSheet = workBook.getSheetAt(0); HSSFWorkbook workBook2 = new HSSFWorkbook(fsFileSystem2); HSSFSheet hssfSheet2 = workBook2.getSheetAt(0); /** * Iterate the rows and cells of the spreadsheet to get all the * datas. */ Iterator rowIterator = hssfSheet.rowIterator(); Iterator rowIterator2 = hssfSheet2.rowIterator(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); if ((hssfRow.getCell((short) 0) != null) && (hssfRow.getCell((short) 1) != null)) { cellDataListA.add(new DataObj(hssfRow.getCell((short) 0) .toString().trim(), hssfRow.getCell((short) 1) .toString().trim())); } if ((hssfRow.getCell((short) 2) != null) && (hssfRow.getCell((short) 3) != null)) { cellDataListB.add(new DataObj2(hssfRow.getCell((short) 2) .toString().trim(), hssfRow.getCell((short) 3) .toString().trim())); } } // Replace Duplicate in Livelink Startd Set set = new HashSet(); List newList = new ArrayList(); nonDuplicateA.addAll(cellDataListA); for (int i = 0; i < nonDuplicateA.size(); i++) { DataObj b = nonDuplicateA.get(i); if (set.add(b.getCol1())) newList.add(nonDuplicateA.get(i)); } nonDuplicateA.clear(); nonDuplicateA.addAll(newList); for (int i = 0; i < nonDuplicateA.size(); i++) { DataObj a = nonDuplicateA.get(i); } System.out.println("nonDuplicateA=="+nonDuplicateA.size()); // Replace Duplicate in Livelink End // Replace Duplicate in Gnosis Startd Set set2 = new HashSet(); List newList2 = new ArrayList(); System.out.println("cellDataListB=="+cellDataListB.size()); nonDuplicateB.addAll(cellDataListB); for (int i = 0; i < nonDuplicateB.size(); i++) { DataObj2 b = nonDuplicateB.get(i); if (set2.add(b.getCol1())) newList2.add(nonDuplicateB.get(i)); } nonDuplicateB.clear(); nonDuplicateB.addAll(newList2); System.out.println("nonDuplicateB=="+nonDuplicateB.size()); // Replace Duplicate in Gnosis End // Common record //System.out.println("------Common----"); for (int i = 0; i < nonDuplicateA.size(); i++) { DataObj a = nonDuplicateA.get(i); for (int j = 0; j < nonDuplicateB.size(); j++) { DataObj2 b = nonDuplicateB.get(j); if((a.getCol2()!=null && b.getCol2()!=null )){ //System.out.println("---------"); if (a.getCol2().equalsIgnoreCase(b.getCol2())) { // System.out.println(a.getCol2() +"--"+i+"--"+b.getCol2()); nonDuplicateAB.add(new DataObj(a.getCol1().toString() .trim(), b.getCol1().toString().trim())); //addLabel(sheet, 0, i, a.getCol1().toString().trim()); // addLabel(sheet, 1, i, b.getCol1().toString().trim()); break; } } } } System.out.println("nonDuplicateAB=="+nonDuplicateAB.size()); TreeMap misA = new TreeMap(); //System.out.println("------Missing----"); for (int i = 0; i < nonDuplicateA.size(); i++) { DataObj a = nonDuplicateA.get(i); for (int j = 0; j < nonDuplicateB.size(); j++) { DataObj2 b = nonDuplicateB.get(j); if((a.getCol2()!=null && b.getCol2()!=null )){ if (!(a.getCol2().equals(b.getCol2()))) { //System.out.println(a.getCol1() +"="+b.getCol1()); //break; if(misA.containsValue(a.getCol2())){ misA.remove(a.getCol1()); } else { misA.put(a.getCol1(), a.getCol2()); } } } } } //System.out.println("SIze mis="+misA); TreeMap misB = new TreeMap(); for (int i = 0; i < nonDuplicateB.size(); i++) { DataObj2 a = nonDuplicateB.get(i); for (int j = 0; j < nonDuplicateA.size(); j++) { DataObj b = nonDuplicateA.get(j); if((a.getCol2()!=null && b.getCol2()!=null )){ if (!(a.getCol2().equals(b.getCol2()))) { //System.out.println(a.getCol1() +"="+b.getCol1()); if(misB.containsValue(a.getCol2())){ misB.remove(a.getCol1()); } else { misB.put(a.getCol1(), a.getCol2()); } } } } } // System.out.println("SIze misB="+misB); //Getting ComID and Object Id from excel start while (rowIterator2.hasNext()) { HSSFRow hssfRow2 = (HSSFRow) rowIterator2.next(); if ((hssfRow2.getCell((short) 0) != null) && (hssfRow2.getCell((short) 1) != null)) { comID.add(new DataObj2(hssfRow2.getCell((short) 0) .toString().trim(), hssfRow2.getCell((short) 1) .toString().trim())); } } System.out.println("Size ComID="+comID.size()); TreeMap hm = new TreeMap(); System.out.println("Please wait...Data comparison.. "); for (int i = 0; i < nonDuplicateAB.size(); i++) { DataObj a = nonDuplicateAB.get(i); for(int j=0;j<comID.size();j++ ){ DataObj2 b = comID.get(j); //System.out.println((b.getCol2()+"---"+a.getCol1())); if(b.getCol2().equalsIgnoreCase(a.getCol1())){ hm.put(b.getCol1(), b.getCol2()); break; } } } System.out.println("Size HM="+hm.size()); //Getting ComID and Object Id from excel End //Data Base Updation Connection conn = null; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection( "jdbc:oracle:thin:@cxxxxx:5487:dev", "", ""); System.out.println("after calling conn"); Set set6 = hm.entrySet(); Iterator i = set6.iterator(); String gnosisNodeId=""; int update=1; while(i.hasNext()) { Map.Entry me = (Map.Entry)i.next(); //System.out.print(me.getKey() + ": "); //System.out.println(me.getValue()); // System.out.println("nonDuplicateAB="+nonDuplicateAB.size()); for(int m=0;m<nonDuplicateAB.size();m++){ DataObj a = nonDuplicateAB.get(m); if(me.getValue().toString().equalsIgnoreCase(a.getCol1())){ gnosisNodeId=a.getCol2().toString(); nonDuplicateAB.remove(m); break; } } //System.out.println("nonDuplicateAB="+nonDuplicateAB.size()); if(gnosisNodeId!=null){ //System.out.println("LOOP"); String s1="UPDATE component SET com_xml = UPDATEXML(com_xml, '*/url/value/text()', '"; String s2="http://dmfwebtop65.pfizer.com/webtop/drl/objectId/"+gnosisNodeId; String s3="') where com_id="+me.getKey().toString().substring(1)+""; Statement stmt1=null; //http://dmfwebtop65.pfizer.com/webtop/drl/objectId/0901201b8239cefb try { String updateString1 = s1+s2+s3; stmt1 = conn.createStatement(); int rows =stmt1.executeUpdate(updateString1); if (rows>0) { addLabel(sheet, 0, update, me.getKey().toString().substring(1)); addLabel(sheet, 1, update, me.getValue().toString().substring(1)); addLabel(sheet, 2, update,gnosisNodeId ); update++; System.out.println("Update Success="+me.getKey().toString().substring(1)+ "-->" +me.getValue().toString().substring(1)); } else { System.out.println("Not Updated="+me.getKey().toString().substring(1)+ "-->" +me.getValue().toString().substring(1)); } } catch (SQLException e) { System.out.println("No Connect" + e); } catch (Exception e) { System.out.println("Error "+e.getMessage()); } } else{System.out.println("No gnosis id found for ObjectID="+me.getKey().toString().substring(1)); } }//While } //try catch (Exception e) { e.printStackTrace(); } }//Main try catch (Exception e) { e.printStackTrace(); } } //method private void addCaption(WritableSheet sheet, int column, int row, String s) throws RowsExceededException, WriteException { Label label; label = new Label(column, row, s, timesBoldUnderline); sheet.addCell(label); } private void addNumber(WritableSheet sheet, int column, int row, Integer integer) throws WriteException, RowsExceededException { Number number; number = new Number(column, row, integer, times); sheet.addCell(number); } private void addLabel(WritableSheet sheet, int column, int row, String s) throws WriteException, RowsExceededException { Label label; label = new Label(column, row, s, times); sheet.addCell(label); } public static void main(String[] args) throws WriteException, IOException { DscMigration test = new DscMigration(); test.setOutputFile("C:/Documents and Settings/nithya/Desktop/DSC/Report/Mapping.xls"); test.write(); System.out .println("Please check the result file under C:/Documents and Settings/nithya/Desktop/DSC/Report/Report.xls "); }

} XXXX Internal Use Based on WTTE-0043 ELC Maintenance Release and Bug Fix Plan Template Version 4.0 Effective Date: July 01, 2010 // Fix Plan Error

Author: 1 Signing Signatures Table of Contents I created this document to document a service release implementation plan or to fix bugs for a project. NAME DATE I created this document to document the implementation plan for a support release or bug fix for XXX Plus v4.5. NAME DATE

I approve this change and agree that this entry is an accurate and complete plan for performing a maintenance release or bug fix and fully supports the implementation, testing, and release steps for this project. NAME DATE I looked at the contents of this entry and found that it meets the applicable BT compliance requirements. NAME DATE

Signature 2 Introduction This project provides documents for the requested change, planned approach, development solution, test plan, test results, and release approval for service release or bug fixes for the project.

3 Change Request Subscriber Name:

XX Object / System Name:

Priority project (high / medium / low):

High Change Request: Not Available Change Request Date:

22-nov-2011

Description of the problem or requested change:

Estimated development time:

1 day (approximately)

4) 4 Maintenance and bug fixes 5 development solutions 8 Supporting links 9 Change log

0
source

More articles:


All Articles