C# - convert xls file to xlsx without office components

Question

I have a server that can't have any office installed on it, so I'm using ClosedXML to do some manipulations on excel files. But ClosedXML only works on .xlsx file and I also have xls file to handle. I spend almost a full day searching for a way to convert .xls files to .xlsx files without using any office libraries (since there is no office installed on my designated server...)

Can someone PLEASE tell me how can I convert these .xls files to .xlsx files ? I can't use Microsoft.Office.Interop because it requires having office installed on the server.

For Microsoft.Office.Interop you need to have office installed. You could use `OleDb` to read it, then a library like [`EPPlus`](http://epplus.codeplex.com/) or [`OpenXml`](http://www.codeproject.com/Articles/670141/Read-and-Write-Microsoft-Excel-with-Open-XML-SDK) to write the xlsx file. — Tim Schmelter, Mar 24 '15 at 13:43
I thought so... do you know how to convert .xls to .xlsx without having office installed ? — Liran Friedman, Mar 24 '15 at 13:46
To use the Office.Interop you do need office installed but there are other libraries you can use. [Here](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp) is a question, who's answer talks about a library that would probably do what you need. — CalebB, Mar 24 '15 at 13:49
Try this: https://stackoverflow.com/questions/46937604/how-to-convert-xls-file-to-xlsx-file-using-c-sharp/49746655#49746655 — Yawar Murtaza, Jan 09 '19 at 10:35

Tim Schmelter · Answer 1 · 2015-03-24T13:53:41.557

9

For Microsoft.Office.Interop you need to have office installed.

You could use OleDb to read it(f.e. into a DataTable). Then use a library like EPPlus or OpenXml to write the xlsx file. Both don't need to have office installed.

Creating an xlsx file from a DataTable with EPPLus is easy:

using (ExcelPackage pck = new ExcelPackage(newFile))
{
  ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Name of Worksheet");
  ws.Cells["A1"].LoadFromDataTable(dataTable, true);
  pck.Save();
}

The first link above shows how to get a DataTable from an xls-file.

edited Mar 24 '15 at 13:53

answered Mar 24 '15 at 13:49

Tim Schmelter

450,073
74
686
939

@LiranFriedman: i have shown how to create an excel file with epplus, it's simple as that once you have a `DataTable`. The first link shows how to extract a `DataTable` from a xls file. – Tim Schmelter Mar 24 '15 at 13:54
@LiranFriedman: i thought it was clear since i have mentioned it at the end of my answer. Use the first link to get a `DataTable` from the xls file, then use `EPLus` to write the xlsx file(code like above). – Tim Schmelter Mar 24 '15 at 14:11

score 8 · Accepted Answer · answered Mar 24 '15 at 14:26

Thanks to @Tim Schmelter. Here is the code I got from the links:

private static string GetConnectionString()
    {
        Dictionary<string, string> props = new Dictionary<string, string>();

        // XLSX - Excel 2007, 2010, 2012, 2013
        props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
        props["Extended Properties"] = "Excel 12.0 XML";
        props["Data Source"] = @"D:\data\users\liran-fr\Desktop\Excel\Received\Orbotech_FW__ARTEMIS-CONTROL-AG__223227__0408141043__95546.xls";

        // XLS - Excel 2003 and Older
        //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
        //props["Extended Properties"] = "Excel 8.0";
        //props["Data Source"] = "C:\\MyExcel.xls";

        StringBuilder sb = new StringBuilder();

        foreach (KeyValuePair<string, string> prop in props)
        {
            sb.Append(prop.Key);
            sb.Append('=');
            sb.Append(prop.Value);
            sb.Append(';');
        }

        return sb.ToString();
    }

    private static DataSet ReadExcelFile()
    {
        DataSet ds = new DataSet();

        string connectionString = GetConnectionString();

        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            // Get all Sheets in Excel File
            DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            // Loop through all Sheets to get data
            foreach (DataRow dr in dtSheet.Rows)
            {
                string sheetName = dr["TABLE_NAME"].ToString();

                //if (!sheetName.EndsWith("$"))
                //    continue;

                // Get all rows from the Sheet
                cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                DataTable dt = new DataTable();
                dt.TableName = sheetName;

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);

                ds.Tables.Add(dt);
            }

            cmd = null;
            conn.Close();
        }

        return ds;
    }
using (ExcelPackage epackage = new ExcelPackage())
        {
            ExcelWorksheet excel = epackage.Workbook.Worksheets.Add("ExcelTabName");
            DataSet ds = ReadExcelFile();
            DataTable dtbl = ds.Tables[0];
            excel.Cells["A1"].LoadFromDataTable(dtbl, true);
            System.IO.FileInfo file = new System.IO.FileInfo(@"D:\data\users\liran-fr\Desktop\Excel\Received\test.xlsx");
            epackage.SaveAs(file);
        }

@Kiquenet I had the same issue...you need to install the following https://www.microsoft.com/en-us/download/details.aspx?id=13255 — TsTeaTime, Feb 26 '19 at 06:18
Microsoft Access Database Engine 2010 Redistributable applies to 32bit (x86) or 64bit (x64) ? — Kiquenet, Feb 26 '19 at 10:32

C# - convert xls file to xlsx without office components

2 Answers2

Linked