Convert a Datatable to .xls,.xlsx,.csv by the given delimeter in input

Question

I want a method to write the datatable data to .xls,.xlsx or.csv based on the input provided along with the delimiter as input

public class DataTableExtensions 
{
    /*Input Params : Datatable input 
                    fileFormat(.xls,.csv,.xlsx)
                   delimeter('\t' (tabSpace) or  ,(comma) or  | (pipe Symbol)  
                    filepath - Any local folder*/
    public void WriteToCsvFile(DataTable dataTable,string fileFormat,string delimeter, string filePath) 
    {
        //Code to convert file based on the input

       //Code to create file         
        System.IO.File.WriteAllText(filePath, fileContent.ToString());
    }
}

Your DBMS of choice should have a "export to CSV" function. My advise regarding bulk operations is to **always** do them in the DBMS. Doing this stuff in code will just make it slower, more memory demanding and more prone to errors. Whatever code you write, will be inferior to the code that already exists and a ton of extra work. — Christopher, Feb 28 '19 at 03:28
@Christopher -It is a console job which runs frequently for a time frame of 2 hours once. Based on the processed data and configuration in table, I need to export based on the configuration i.e., the input provided to the method. We even do have limitation of data ,the max data we can receive to process is 1000 rows. — Rupesh, Feb 28 '19 at 04:21
There are plenty of examples for writing to excel: https://stackoverflow.com/questions/23041021/how-to-write-some-data-to-excel-file-xlsx/45759956 — peeyush singh, Feb 28 '19 at 05:54

score 0 · Answer 1 · answered Feb 28 '19 at 12:56

You said it is only 1000 rows every 2 hours in the comments. That is a acceptable amount of data for a C# programm. I would say the big question left is wich output format you use.

.CSV is the simplest one. This format can be done with a File.WriteLine() and some string concaction. There is no build in CSV parser or writer code I am aware off in C#, but there is plenty of 3rd party code.

.XLS requires the (t)rusty Office COM Interop. That requires office to be installed and does not work from a non-interactive session (like a Windows Service). On top of all the normal issues for using COM interop.

There is the odd "export to XLS" function on existing classses, but those are rare, far inbetween and about everything you get. Unfortunately as we always had COM Interop as fallback, we never quite developed a standalone library for working with .XLS. Ironically working with this old format is harder from C#/.NET then it would be from Java.

.XLSX however is easier. It can be written using the OpenXML SDK. Or the XML writer and ZipArchive class: At their core all the ???x formats are a bunch of .XML files in a renamed .ZIP container. There should even be 3rd party code out there to make using the SDK easier.

.CSV is the lowest common denominator and propably the easiest to create. However if a user is supposed to open this document, the lack for formating might become an issue.

.XSLX would be my choice if you need a user to open it.

.XSL I would avoid like a swarm of angry bees.

Thank you for your response. It helps, let me jump into the code and try these.I though there would be a common service which converts a data table to all excel formats(CSV,.xls,.xlsx) — Rupesh, Mar 01 '19 at 04:38

Rupesh · Answer 2 · 2019-03-01T16:38:17.883

    I have written this  Program to convert Xls,XLSx using console application with 
    Datatable as input and for text file I have written a simple stream writer logic.This works good. Initially I have installed package manage console  and below code 
    using expertXLs package.I am not sure wheather I can share the key of that 
    or not.Please search the key and give in config before running it


     Package Manage Console - Install-Package ExpertXls.ExcelLibrary -Version 5.0.0


     Code :
      --------

private static void GenerateTxtFileFromDataTable(DataTable sampleDataTable,string delimiter)
 {
                 var _expertxlsLK = ConfigurationManager.AppSettings["ExpertxlsLK"];
                 //GetKey Value from config

                 // Create the workbook in which the data from the DataTable will be loaded 0 for 2003 Excel(xls),1 for 2007 Excel(xlsx)
                ExcelWorkbookFormat workbookFormat = ExcelWorkbookFormat.0;

                // create the workbook in the desired format with a single worksheet
                ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);
                workbook.EnableFormulaCalculations();

                workbook.LicenseKey = _expertxlsLK;

               // get the first worksheet in the workbook
               ExcelWorksheet worksheet = workbook.Worksheets[0];

             // set the default worksheet name
              worksheet.Name = "ClaimInformation";

            // load data from DataTable into the worksheet
           worksheet.LoadDataTable(sampleDataTable, 1, 1, true);
           worksheet.Workbook.EnableFormulaCalculations();
          workbook.Save(@"M:\Rupesh\test.xlsx");
          workbook.Close();

}

Convert a Datatable to .xls,.xlsx,.csv by the given delimeter in input

2 Answers2