Hello Luke, Firstly, I apologise, I did not realise that you were not a programmer and so made a few assumptions about the code; the first one being those hard coded paths/filenames. Will modify the code and post again in a while; gaoing to create an inner class which can be a scary concept for an inexperienced programmer but it really should not be as you will see.
Sorry but I do not really understand your second question. Are you asking whether it is possible to exercise some control over what the class converts from Excel into CSV? This should be possible but you will need to define quite precisely what you want to exclude - for example excluding blank rows or empty cells should be easy. This class cannot be used to modify the content of the cells on a spreadsheet. It could be extended to do that but again, you would ne to quite precisely define exactly what you wnated to convert and just how. Yours Mark B Luke_Devon wrote: > > Hi Mark > > I tested the code. It was fine. I could manage to convert XLS into CSV > without any problem. But i have few more questions. > > In this code , you have been hard coded the path where XLS and CSV > located. and file names also hard coded. > > How it would be pointing to a single directory and convert all XLS into > CSV stored in the folder ? > > In the RAW XLS file , there might be some unwanted data to be converted > into CSV. or some times we need to complete the blank cells in the excel > sheet. > > How can we do it , before convert into CSV ? > > Sorry about all those simple questions.( Since I'm not a programmer) > > Thanks in advance > Luke > > > > > > ________________________________ > From: MSB <[email protected]> > To: [email protected] > Sent: Friday, April 9, 2010 22:18:43 > Subject: Re: Convert XLS into CSV > > > Hello Luke, > > As promised, a bit of code that uses the usermodel to create CSV files. I > have not had the opportunity to test it thoroughly and do expect there to > be > issues so do not use the code in a production environment until you have > put > it through the wringer so to speak. Also, you may find the performance a > little slower than you expect, especially if you are using the newer xml > based file format and have run the eventusermodel code that Nick wrote. > > In essence, 'my' code simplt takes the contents of the workbook and > converts > it into an ArrayList of ArrayLists where each inner ArrayList contains > zero, > one or more Strings that describe the contents of a cell. I used this > approach becuase it allows me to ensure that every row in the finished CVS > file is the same length - with regard to the number of elements it > contains > - even if the input workbook contains rows that have varying numbers of > cells on them. The code as it stands does evaluate any formulae that may > be > contained within cells and I hope will perform pretty much as you require. > Take a look down into the main() method to see how it can be used; this > method only shows the class being used to process a single file but an > instance can be used to process more than one file in this manner; > > ToCSV converter = new ToCSV(); > converter.openWorkbook("C:/temp/To CSV.xls"); > converter.convertToCSV(); > converter.saveCSVFile("C:/temp/First CSV.csv", ";"); > > converter.openWorkbook("C:/temp/Another To CSV.xlsx"); > converter.convertToCSV(); > converter.saveCSVFile("C:/temp/Second CSV.csv", ";"); > > > import org.apache.poi.ss.usermodel.WorkbookFactory; > import org.apache.poi.ss.usermodel.Workbook; > import org.apache.poi.ss.usermodel.Sheet; > import org.apache.poi.ss.usermodel.Row; > import org.apache.poi.ss.usermodel.Cell; > import org.apache.poi.ss.usermodel.DataFormatter; > import org.apache.poi.ss.usermodel.FormulaEvaluator; > import org.apache.poi.openxml4j.exceptions.InvalidFormatException; > > import java.io.File; > import java.io.FileInputStream; > import java.io.FileWriter; > import java.io.BufferedWriter; > import java.io.IOException; > import java.io.FileNotFoundException; > import java.util.ArrayList; > > /** > * Demonstrate one way to convert an Excel spreadsheet into a CSV file. > This > * class makes the following assumptions; > * > * 1. Where the Excel workbook contains more that one worksheet then a > single > * CSV file will contain the data from all of the worksheets. > * 2. The data matrix contained in the CSV file will be square. This means > that > * the number of elements in each row of the CSV file will match the > number > * of cells in the longest row found in the Excel workbook. Any short > rows > * will be 'padded' with empty elements - an empty elements is > represented in > * the CSV file in this way ,,. > * 3. Empty elements will represent missing cells. > * 4. A row consisting of empty elements will be used to represent an empty > row > * in the Excel workbook. > * > * @author Mark B > * @version 1.00 9th April 2010 > */ > public class ToCSV { > > private Workbook workbook = null; > private ArrayList<ArrayList> csvData = null; > private int maxRowWidth = 0; > private DataFormatter formatter = null; > private FormulaEvaluator evaluator = null; > > /** > * Open an Excel workbook readt for conversion. > * > * @param filename An instance of the String class that encapsulates > the > * path to and name of a valid Excel workbook. Note > that > the > * workbook can be either a binary (.xls) or > SpreadsheetML > * (.xlsx) file. > * > * @throws java.io.FileNotFoundException Thrown if the file cannot be > located. > * @throws java.io.IOException Thrown if a problem occurs in the file > system. > * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException > Thrown > * if invalid xml is found whilst parsing an input SpreadsheetML > file. > */ > public void openWorkbook(String filename) throws > FileNotFoundException, > IOException, > InvalidFormatException { > File file = null; > FileInputStream fis = null; > try { > file = new File(filename); > fis = new FileInputStream(file); > this.workbook = WorkbookFactory.create(fis); > this.evaluator = > this.workbook.getCreationHelper().createFormulaEvaluator(); > this.formatter = new DataFormatter(); > } > finally { > if(fis != null) { > fis.close(); > } > } > } > > /** > * Called to convert the contents of the currently opened workbook > into > * a CSV file. > */ > public void convertToCSV() { > Sheet sheet = null; > Row row = null; > int lastRowNum = 0; > this.csvData = new ArrayList<ArrayList>(); > > // Discover how many sheets there are in the workbook.... > int numSheets = this.workbook.getNumberOfSheets(); > > // and then iterate through them. > for(int i = 0; i < numSheets; i++) { > > // Get a reference to a sheet and check to see if it contains > // any rows. > sheet = this.workbook.getSheetAt(i); > if(sheet.getPhysicalNumberOfRows() > 0) { > > // Note down the index number of the bottom-most row and > // then iterate through all of the rows on the sheet > starting > // from the very first row - number 1 - even if it is > missing. > // Recover a reference to the row and then call another > method > // which will strip the data from the cells and build > lines > // for inclusion in the resylting CSV file. > lastRowNum = sheet.getLastRowNum(); > for(int j = 0; j <= lastRowNum; j++) { > row = sheet.getRow(j); > this.rowToCSV(row); > } > } > } > } > > /** > * Called to actually save the data recovered from the Excel workbook > * as a CSV file. > * > * @param filename An instance of the String class that encapsulates > the > * path to and name of the CSV file. > * @param separator An instance of the String class that encapsulates > the > * character or character that ought to be used to > delimit > * elements on the lines of the CSV file. > * @throws java.io.FileNotFoundException Thrown if the file cannot be > found. > * @throws java.io.IOException Thrown to indicate and error occurred > in > the > * underylying file system. > */ > public void saveCSVFile(String filename, String separator) > throws > FileNotFoundException, > IOException { > File file = null; > FileWriter fw = null; > BufferedWriter bw = null; > ArrayList<String> line = null; > StringBuffer buffer = null; > String csvLineElement = null; > try { > // Open a writer onto the CSV file. > file = new File(filename); > fw = new FileWriter(file); > bw = new BufferedWriter(fw); > > // Step through the elements of the ArrayList that was used to > hold > // all of the data recovered from the Excel workbooks' sheets, > rows > // and cells. > for(int i = 0; i < this.csvData.size(); i++) { > buffer = new StringBuffer(); > > // Get an element from the ArrayList that contains the > data > for > // the workbook. This element will itself be an ArrayList > // containing Strings and each String will hold the data > recovered > // from a single cell. The for() loop is used to recover > elements > // from this 'row' ArrayList one at a time and to write > the > Strings > // away to a StringBuffer thus assembling a single line > for > inclusion > // in the CSV file. If a row was empty or if it was short, > then > // the ArrayList that contains it's data will also be > shorter than > // some of the others. Therefore, it is necessary to check > within > // the for loop to ensure that the ArrayList contains data > to be > // processed. If it does, then an element will be > recovered > and > // appended to the StringBuffer. > line = this.csvData.get(i); > for(int j = 0; j < this.maxRowWidth; j++) { > if(line.size() > j) { > csvLineElement = line.get(j); > if(csvLineElement != null) { > buffer.append(csvLineElement); > } > } > if(j < (this.maxRowWidth - 1)) { > buffer.append(separator); > } > } > > // Once the line is built, write it away to the CSV file. > bw.write(buffer.toString().trim()); > > // Condition the inclusion of new line characters so as to > // avoid an additional, superfluous, new line at the end > of > // the file. > if(i < (this.csvData.size() - 1)) { > bw.newLine(); > } > } > } > finally { > if(bw != null) { > bw.flush(); > bw.close(); > } > } > } > > /** > * Called to convert a row of cells into a line of data that can later > be > * output to the CSV file. > * > * Note that no tests have yet been conducted with blank cells or > those > * containing formulae. Such may require latereations to the way this > code > * works. > * > * @param row An instance of either the HSSFRow or XSSFRo classes that > * encapsulates information about a row of cells recovered > from > * an Excel workbook. > */ > private void rowToCSV(Row row) { > Cell cell = null; > int lastCellNum = 0; > ArrayList<String> csvLine = new ArrayList<String>(); > > // Check to ensure that a row was recovered from the sheet as it > is > // possible that one or more rows between other populated rows > could > be > // missing - blank. If the row does contain cells then... > if(row != null) { > > // Get the index for the right most cell on the row and then > // step along the row from left to right recovering the > contents > // of each cell, converting that into a formatted String and > // then storing the String into the csvLine ArrayList. > lastCellNum = row.getLastCellNum(); > for(int i = 0; i <= lastCellNum; i++) { > cell = row.getCell(i); > if(cell == null) { > csvLine.add(""); > } > else { > if(cell.getCellType() != Cell.CELL_TYPE_FORMULA) { > csvLine.add(this.formatter.formatCellValue(cell)); > } > else { > csvLine.add(this.formatter.formatCellValue(cell, > this.evaluator)); > } > } > } > // Make a note of the index number of the right most cell. > This > value > // will later be used to ensure that the matrix of data in the > CSV file > // is square. > if(lastCellNum > this.maxRowWidth) { > this.maxRowWidth = lastCellNum; > } > } > this.csvData.add(csvLine); > } > > /** > * The main() method contains code that demonstrates how to use the > class. > * @param args > */ > public static void main(String[] args) { > try { > ToCSV converter = new ToCSV(); > converter.openWorkbook("C:/temp/To CSV.xls"); > converter.convertToCSV(); > converter.saveCSVFile("C:/temp/First CSV.csv", ";"); > } > catch(Exception ex) { > System.out.println("Caught an: " + ex.getClass().getName()); > System.out.println("Message: " + ex.getMessage()); > System.out.println("Stacktrace follows:....."); > ex.printStackTrace(System.out); > } > > } > } > > Test it out, have a good look through it and if there is anything you want > to know just post to the list. > > Yours > > Mark B > > > Luke_Devon wrote: >> >> Hi Mark, >> >> First of all I would like to thank you for the reply. >> >> Actually , currently I am using MS Office 2002. But I would like to use >> the code for other latest versions also. >> In my case , I dont want to do any validations for the EXCEL file , >> because I just wanted convert entire excel file into CSV. >> Is there any simple java code available for such a basic requirement ? >> >> anyway I'll try to use the code in the link which you have given to me . >> >> Thanks & Regards >> >> Luke. >> >> >> >> >> ________________________________ >> From: MSB <[email protected]> >> To: [email protected] >> Sent: Thursday, April 8, 2010 23:47:07 >> Subject: Re: Convert XLS into CSV >> >> >> Hello Luke, >> >> Which version of the Excel file format are you targetting, the older >> binary >> or newer xml based version? I ask because Nick wrote and contributed some >> code that can be used to convert the older binary files into csv. It uses >> the eventmodel and will seem quite complex on first acquaintance but here >> it >> is; >> >> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java >> >> I know that other users have modified the code to, for example, output >> diffenert worksheets to separate csv files. >> >> Yours >> >> Mark B >> >> >> Luke_Devon wrote: >>> >>> Hi >>> >>> I wanted to convert some XLS files into CSV. I found that apache.poi is >>> the most perfect tool. Since I'm a beginner , i have no idea how to do >>> that . Can some body help me please ? Do you have sample code for >>> convert >>> xls into csv ? >>> >>> Thanks in Advance >>> Luke >>> >>> >>> >>> Get your preferred Email name! >>> Now you can @ymail.com and @rocketmail.com. >>> http://mail.promotions.yahoo.com/newdomains/aa/ >>> >> >> -- >> View this message in context: >> http://old.nabble.com/Convert-XLS-into-CSV-tp28175999p28180503.html >> Sent from the POI - User mailing list archive at Nabble.com. >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> New Email names for you! >> Get the Email name you've always wanted on the new @ymail and >> @rocketmail. >> Hurry before someone else does! >> http://mail.promotions.yahoo.com/newdomains/aa/ >> > > -- > View this message in context: > http://old.nabble.com/Convert-XLS-into-CSV-tp28175999p28191046.html > Sent from the POI - User mailing list archive at Nabble.com. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > Get your new Email address! > Grab the Email name you've always wanted before someone else does! > http://mail.promotions.yahoo.com/newdomains/aa/ > -- View this message in context: http://old.nabble.com/Convert-XLS-into-CSV-tp28175999p28219157.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
