0

I have been trying to convert .xls file to .xlsx without having dependecy over Office Component. I followed c-sharp-convert-xls-file-to-xlsx-without-office-components

It converts the file from xls to xlsx but all the formatting gets lost. I have tried other libraries such as Spire.XLS, NPOI etc. But none can do the exact copy of .xls to .xlsx. Please help me with the formatting thing. Below is the code from the link I used for conversion:

XLWorkbook workbook = new XLWorkbook();
        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();
        }

        workbook.AddWorksheet(ds);
return ds;
Jayant Usrete
  • 69
  • 1
  • 4
  • 13
  • This simply moves the data across it does not include any meta data. So yes you will loose the formatting. You cannot query excel formatting using OleDb. This seems to be using a sledge hammer to crack a nut, whats wrong with `save as...`? – Liam Sep 21 '18 at 12:52
  • @Liam `save as` won't work here as I have used closedxml to work with worksheets, workbooks and stuff which doesn't support `.xls` file format. Also about other libraries like Spire.XLS, NPOI etc as I mentioned above doesn't do the job entirely. Is there any other way to get all the data without losing any formatting? – Jayant Usrete Sep 21 '18 at 13:13
  • How do you use spire.xls to convert xls to xlsx? I saw the formatting is maintained during conversion in this tutorial: https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Conversion/NET-Excel-Conversion-between-Excel-97-2003.xls-and-Excel-2007.xlsx.html – Dheeraj Malik Sep 28 '18 at 06:49
  • @DheerajMalik Yes it does but not all the formatting is maintained, specially the conditional formatting. – Jayant Usrete Oct 16 '18 at 09:15

0 Answers0