Convert a File with .xls extension but save as xml spreadsheet 2003 to .xls File Formate

Please attach file Test.xls to this thread

Please find the attachment  and let me know if something else you required.please let me know how to convert into xls sheet.when i open that file and save it as excel its working  fine but i don't want to do it manually . i want this to convert by java code please let me know solution.
Demo.xls

Yes, you might well confuse yourself and others with that file extension ;)
You might have to use different software to do that. The 'open office' api might do it. Your file opens as a spreadsheet with LibreOffice so that's a good sign

hi i am not confusing any one once  i am reading file with above my java code its give above error .

You misunderstand my comment

Maybe this help you;

http://cocoon.apache.org/2.1/userdocs/xls-serializer.html

Another alternative found here:

https://github.com/bsorrentino/Excel-Xml-Mapping/blob/master/README.md

The quickest way will probably be to write a macro to do the saving. Your file is in SpreadsheetML format - POI won't handle that

Or, install LibreOffice then (at the command line)

libreoffice --convert-to xlsx Demo.xls

Result attached
Demo.xlsx

Hi CEHJ  file is  Excel Spreadsheet 2003 and saved as .xls extension but when i m uploading the same file into my application it would gives error . so i want to convert it into .xls extension not like Excel spreadsheet 2003 so my application take it . i have one solution but it is manually open the file in Microsoft excel  and change save as type from  Excel Spreadsheet 2003 to excel 97-2003 Workbook the upload my application take the file perfectly but it is the manual process i want this kind of things in java while i m uploading Excel Spreadsheet 2003 it automatically change it to excel 97-2003 Workbook in backed and upload it perfectly . Hope this Clear you what i exactly want .

I'm already clear on the situation and requirement. I've given you the solution that i would use myself

its manual process i want to do it via java can you please give me some code snippet of that how to read and convert into .xls .

It's not manual - you'd write a script to run it.

The alternative is to parse the xml programatically and then use POI to write a spreadsheet - that would be a lot of work

can you please guide me for script i don't have any knowledge  about it and that script run with java code?

http://technojeeves.com/index.php/aliasjava1/13-runtimeexec

will show you how to run a terminal command from Java

can you please provide me some code snippet for above requirement .

Sorry, only as a paid service on Live

There is a java package xelem that can read Excel SpreadsheetML:

ExcelReader reader = new ExcelReader();
Workbook xlWorkbook = reader.getWorkbook("foo/bar/myFile.xml");

Select allOpen in new window

Download the package:
http://xelem.sourceforge.net/download.html

Hi Juan can you please read few nodes and values in my attached file  with xelem it would be very helpful to me .

import java.io.IOException;
import javax.xml.parsers.ParserConfigurationException;

import org.xml.sax.SAXException;

import nl.fountain.xelem.XelemException;
import nl.fountain.xelem.excel.Cell;
import nl.fountain.xelem.excel.Row;
import nl.fountain.xelem.excel.Workbook;
import nl.fountain.xelem.excel.Worksheet;
import nl.fountain.xelem.lex.ExcelReader;


public class demo {

    public static void main(String[] args) throws XelemException, IOException, SAXException, ParserConfigurationException {
    	ExcelReader reader = new ExcelReader();
	 Workbook xlWorkbook = reader.getWorkbook("/Apps/Demo.xls");
	 	Workbook wb = xlWorkbook;
	 	
	 	Worksheet ws = wb.getWorksheetAt(0);
	 	for(Row r : ws.getRows()) {
	 		for(Cell c : r.getCells())
	 		{
	 			if(c.hasData()) {
		 			System.out.print(c.getData().toString() + "\t");
	 			}
	 		}
 			System.out.println();
	 		
	 	}
    }	 	
}

Select allOpen in new window

Results:

First Name	Last Name	Gender	Country	Age	Date	Id	
Dulce	Abril	Female	United States	Thu Feb 01 00:00:00 COT 1900	15/10/2017	Sun Apr 10 00:00:00 COT 1904	
Mara	Hashimoto	Female	Great Britain	Thu Jan 25 00:00:00 COT 1900	16/08/2016	Sat Apr 30 00:00:00 COT 1904	
Philip	Gent	Male	France	Mon Feb 05 00:00:00 COT 1900	21/05/2015	Wed Jan 30 00:00:00 COT 1907	
Kathleen	Hanner	Female	United States	Thu Jan 25 00:00:00 COT 1900	15/10/2017	Sat Sep 18 00:00:00 COT 1909	
Nereida	Magwood	Female	United States	Tue Feb 27 00:00:00 COT 1900	16/08/2016	Wed Oct 03 00:00:00 COT 1906	
Gaston	Brumm	Male	United States	Wed Jan 24 00:00:00 COT 1900	21/05/2015	Fri Dec 28 00:00:00 COT 1906	
Etta	Hurn	Female	Great Britain	Sun Feb 25 00:00:00 COT 1900	15/10/2017	Sat Nov 06 00:00:00 COT 1909	
Earlean	Melgar	Female	United States	Sat Jan 27 00:00:00 COT 1900	16/08/2016	Fri Sep 21 00:00:00 COT 1906	
Vincenza	Weiland	Female	United States	Fri Feb 09 00:00:00 COT 1900	21/05/2015	Tue Dec 04 00:00:00 COT 

Select allOpen in new window

Packages: javax.xml-1.3.4.jar,  sax.jar, xelem.jar

Thanx a ton juan for your support and answer please guide me in below points as well as -

1) how to read multiple sheets ?
2) how to create again proper .xls having same data and sheets .


Thanx a ton in advance.please provide some sample code for above points.

1. Read multiple  sheerts:

	 	for(Worksheet ws : wb.getWorksheets()) {
 			System.out.print("Worksheet: " + ws.getName());
	 		for(Row r : ws.getRows()) {
		 		for(Cell c : r.getCells())
		 		{
		 			if(c.hasData()) {
			 			System.out.print(c.getData().toString() + "\t");
		 			}
		 		}
	 			System.out.println();
		 		
		 	}
	 	}

Select allOpen in new window

2. You can use POI  to  create a .xlsx and populate it while iterate the .xml file.

HI Juan Thanx for support while using POI how can i create .xls header row like First Name,Last Name etc.please guide. Juan i tried it but i get all result of a single code in a single column my code is below please let me know where i am wrong or i missed anything -

public class ExcelImport  {
      
      public static final String ECB_DATAS ="C:/Users/puneet/Desktop/Demo.xls";
      public static final String OutPutPath ="C:/Users/puneet/Desktop/Test.xls";
      
      
            
             public static void main(String[] args) throws XelemException, IOException, SAXException, ParserConfigurationException {
                      ExcelReader reader = new ExcelReader();
                      Workbook xlWorkbook = reader.getWorkbook(ECB_DATAS);
                         Workbook wb = xlWorkbook;
                         List<String> l1 = wb.getSheetNames();
                         Map<String,List<String>> dataRetrieveMap = new HashMap();
                         ArrayList<String> dataList = new ArrayList<String>();
                         for(Worksheet ws : wb.getWorksheets()) {
                               System.out.println("Worksheet: " + ws.getName());
                               for(Row r : ws.getRows()) {
                                     for(Cell c : r.getCells())
                                     {
                                           if(c.hasData()) {
                                                 dataList.add(c.getData().toString());
                                                 dataRetrieveMap.put(ws.getName(), dataList);
                                           }
                                     }
                                     System.out.println();
                                     
                               }
                         }
                         try {
                              
                              int lenMD = 0;
                              HSSFWorkbook wb1 = new HSSFWorkbook();
                              int recordCnt;
                              for(String key : dataRetrieveMap.keySet()){
                                    HSSFSheet sheet = wb1.createSheet(key);
                                  List<String> values = dataRetrieveMap.get(key);
                                  sheet.setDefaultColumnWidth((short)25);
                                    HSSFCellStyle hssfCellStyle1 = wb1.createCellStyle();
                                    HSSFFont font1 = wb1.createFont();//Creates The Font
                                    font1.setFontName("Times New Roman");
                                  int row = 0;
                                  for(String value : values){
                                      sheet.createRow(row).createCell(0).setCellValue(value);
                                      row++;
                                  }
                              try {
                              FileOutputStream outputStream = new FileOutputStream(OutPutPath);
                              wb1.write(outputStream);
                              //((Closeable) workbook).close();
                          } catch (FileNotFoundException e) {
                              e.printStackTrace();
                          } catch (IOException e) {
                              e.printStackTrace();
                          }
                        
                         }
                         }
                         catch (Exception e)
                         {
                               
                         }
                }
}

please have look my code and please correct it i m getting all values in a single  column.one more thing we don't have any specific template where we can sure that each time we will get this count of column some time it may be increase some time may be decrease . please guide me in this scenario as well as .

Thank you in advance.

This create  a xlsx file. Does not include cell format conversion.

package demo.demoexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;

import nl.fountain.xelem.XelemException;
import nl.fountain.xelem.excel.Cell;
import nl.fountain.xelem.excel.Row;

import nl.fountain.xelem.excel.Workbook;
import nl.fountain.xelem.excel.Worksheet;
import nl.fountain.xelem.lex.ExcelReader;

public class App {

	
	public static void main(String[] args) throws IOException, ParserConfigurationException, SAXException, XelemException {

		String fileName = "/Apps/DemoXLSX.xlsx";

		File outFile = new File(fileName);
        FileOutputStream fis = new FileOutputStream(outFile);

        XSSFWorkbook outWb = new XSSFWorkbook ();

		
		ExcelReader reader = new ExcelReader();
		Workbook wb = reader.getWorkbook("/Apps/Demo.xls");

		for (Worksheet ws : wb.getWorksheets()) {
			System.out.print("Worksheet: " + ws.getName());
			XSSFSheet sheet = outWb.createSheet(ws.getName());
			org.apache.poi.ss.usermodel.Cell  ce;
			org.apache.poi.ss.usermodel.Row ro;
			for (Row r : ws.getRows()) {
				ro = sheet.createRow(r.getIndex()-1);
				
				for (Cell c : r.getCells()) {
					
					if (c.hasData()) {
						ce = ro.createCell(c.getIndex()-1);
						ce.setCellValue(c.getData().toString());
						System.out.print(c.getData().toString() + "\t");
					}
				}
				System.out.println();

			}

		}
		outWb.write(fis);
		outWb.close();
		
		
	}

	
	

}
	

Select allOpen in new window

Versión with DateTime fields

package demo.demoexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;

import nl.fountain.xelem.XelemException;
import nl.fountain.xelem.excel.Cell;
import nl.fountain.xelem.excel.Row;

import nl.fountain.xelem.excel.Workbook;
import nl.fountain.xelem.excel.Worksheet;
import nl.fountain.xelem.lex.ExcelReader;

public class App {

	
	public static void main(String[] args) throws IOException, ParserConfigurationException, SAXException, XelemException {

		String fileName = "/Apps/DemoXLSX.xlsx";

		File outFile = new File(fileName);
        FileOutputStream fis = new FileOutputStream(outFile);

        XSSFWorkbook outWb = new XSSFWorkbook ();

		
		ExcelReader reader = new ExcelReader();
		Workbook wb = reader.getWorkbook("/Apps/Demo.xls");
		    CellStyle cellStyle = outWb.createCellStyle();
		    CreationHelper createHelper = outWb.getCreationHelper();
		    short dateFormat = createHelper.createDataFormat().getFormat("yyyy-dd-MM");
		    cellStyle.setDataFormat(dateFormat);
		    
		for (Worksheet ws : wb.getWorksheets()) {
			System.out.print("Worksheet: " + ws.getName());
			XSSFSheet sheet = outWb.createSheet(ws.getName());
			org.apache.poi.ss.usermodel.Cell  ce;
			org.apache.poi.ss.usermodel.Row ro;
			for (Row r : ws.getRows()) {
				ro = sheet.createRow(r.getIndex()-1);
				
				for (Cell c : r.getCells()) {
					
					if (c.hasData()) {
						
						ce = ro.createCell(c.getIndex()-1);
						if(c.getXLDataType().compareToIgnoreCase(Cell.DATATYPE_DATE_TIME)==0) {
							ce.setCellStyle(cellStyle);
							Calendar cal = Calendar.getInstance();
							cal.setTime((java.util.Date)c.getData());
							ce.setCellValue(cal);
						}
						else {
						ce.setCellValue(c.getData$());
						}
						System.out.print(c.getData$() + "\t");
					}
				}
				System.out.println();

			}

		}
		outWb.write(fis);
		outWb.close();
		
		
	}

	
	

}

Select allOpen in new window

Hi juan Thanx for your support one more thing here when i am generating .xls sheet how can i bold the header cells . so i can differentiate in header row and other rows please help me out on this . thanks for your valuable support.

Hi jaun please let me know how to check thats .xls file is based on xml or word file because in both extension is .xls because if i check extension its always come like .xls file.

Thanx a ton fro support