Is there a programmatic solution to this that does not involve having Office on the server?
Update: This solution will be deployed in a .Net shop, so for now PHP and Java approaches aren't on the table (though I was impressed with the libraries themselves).
We will be receiving documents in csv, .xls, and .xlsx formats that need to be parsed and their data shoved into a DB. We're planning on using the OpenXML SDK for all of the parsing goodness and want to operate over only one file type.
You can achieve this using the Apache POI library for Java.
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
I've used it to read in a complete mix of .xls and .xlsx files, and I always output .xlsx.
For .csv files, import using the Super CSV library and export using the Apache POI library above.
The main motivation for Super Csv is to be the best, fastest and most programmer friendly free CSV package for Java.
Or use PHPExcel ( http://www.phpexcel.net ) if you want a PHP solution rather than java
For csv files i would recommend a combination of http://kbcsv.codeplex.com/ to read the csv file into a datatable and EPPPLUS to use its .FromDataTable Method to convert it to an xlsx file. I works great for me and is very fast. For reading xls files there is no free Implementation that I know of :(
and you can use for parse columns.
object columnValue = ws.Cells[i, ColIndex, i, ColIndex].Value; // get Specific cell.
you can use below method for .csv, xlsx, .txt files.
public yourReturnType compute()
{
#region .XLSX Section
if (FilePath.FullName.Contains(".xlsx") || FilePath.FullName.Contains(".xls"))
{
// Open and read the XlSX file.
using (var package = new ExcelPackage(FilePath))
{
ExcelWorkbook wb = package.Workbook; // Get the work book in the file
if (wb != null)
{
if (wb.Worksheets.Count > 0)
{
ExcelWorksheet ws = wb.Worksheets.First(); // Get the first worksheet
yourParseCode(ws);
}
} // if End.
} // using end.
}
#endregion
#region .CSV Section
if (FilePath.FullName.Contains(".csv") || FilePath.FullName.Contains(".txt"))
{
CSVParser c = new CSVParser(FilePath);
DataTable dt = c.ReadCSVFile();
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("temporary");
ws.Cells["A1"].LoadFromDataTable(dt, true);
yourParseCode (ws);
////pck.Save(); // no need to save this temporary sheet.
}
}
#endregion
return (yourReturnType );
}
