aspose file tools*
The moose likes Blatant Advertising and the fly likes Convert Workbook with Multiple Worksheets into Text or CSV Format Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Other » Blatant Advertising
Bookmark "Convert Workbook with Multiple Worksheets into Text or CSV Format" Watch "Convert Workbook with Multiple Worksheets into Text or CSV Format" New topic
Author

Convert Workbook with Multiple Worksheets into Text or CSV Format

sherazam khan
Ranch Hand

Joined: Mar 10, 2010
Posts: 251

This technical tip explains how developers can covert entire workbook into text or CSV format . Sometimes, you want to convert/save the entire workbook with multiple worksheets into text format. For text formats (e.g Txt, Tab Delimited, CSV etc), by default both Ms-Excel and Aspose.Cells save the contents of active worksheet only. The following code example explains how you can save your entire workbook into text format. We load the source workbook which could be any Ms-Excel or OpenOffice Spreadsheet (e.g xls, xlsx, xlsm, xlsb, ods etc) file and it could have any number of worksheets. After the execution of the code, it converts the data of all sheets inside the workbook into txt format. You can modify the same example to save your file into CSV format. By default TxtSaveOptions.Separator is comma, so you should not specify any separator if you want save your file in CSV format.

Sample Code for converting Entire Workbook into Text or CSV Format.

[Java]

String filePath = "F:/Downloads/source.xlsx";

//Load your source workbook
Workbook workbook = new Workbook(filePath);

//0-byte array
byte[] workbookData = new byte[0];

//Text save options. You can use any type of separator
TxtSaveOptions opts = new TxtSaveOptions();
opts.setSeparator('\t');

//Copy each worksheet data in text format inside workbook data array
for (int idx = 0; idx < workbook.getWorksheets().getCount(); idx++)
{
//Save the active worksheet into text format
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.getWorksheets().setActiveSheetIndex(idx);
workbook.save(bout, opts);

//Save the worksheet data into sheet data array
byte[] sheetData = bout.toByteArray();

//Combine this worksheet data into workbook data array
byte[] combinedArray = new byte[workbookData.length + sheetData.length];
System.arraycopy(workbookData, 0, combinedArray, 0, workbookData.length);
System.arraycopy(sheetData, 0, combinedArray, workbookData.length, sheetData.length);

workbookData = combinedArray;
}

//Save entire workbook data into file
FileOutputStream fout = new FileOutputStream(filePath + ".out.txt");
fout.write(workbookData);
fout.close();

Overview: Aspose.Cells is a Java

Aspose.Cells is a Java component for spreadsheet reporting without using Microsoft Excel. Other features include creating spreadsheets, opening encrypted excel files, macros, VBA, unicode, formula settings, pivot tables, importing data from JDBC ResultSet and support of CSV, SpreadsheetML, PDF, ODS and all file formats from Excel 97 to Excel 2007. It is compatible with Windows, Linux & Unix and supports all advanced features of data management, formatting, worksheet, charting and graphics.

More about Aspose.Cells is a Java

- Homepage of Aspose.Cells is a Java
- Download Aspose.Cells is a Java
- More Technical Tips by Aspose.Cells is a Java
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Convert Workbook with Multiple Worksheets into Text or CSV Format
 
Similar Threads
Render Sparklines & Display RTL Languages in Excel to PDF Conversion
Group Pivot Table Fields, Sheet to Image & Workbook to Print
Aspose.Cells for Java adds new features for ODS & Excel 2007
Excel 2010 Advanced Data Validation & Header/Footers Parsing Support
Read and Save Excel 2007 XLSM, XLTX, XLTM files using Aspose.Cells