[C#] DataTable to XLS (Excel File)

iLovEi said:

Hey,
i Tried looking for tutorials about exporting/importing Datatables for excel file format. I Couldnt find any (free) library for this.
the datatable i have have lots of columns/rows so im trying to export it to excel so its easy to edit, because i need translate some columns.
so what i am asking is if theres any tutorial? or any library i could use?

thanks

Click to expand...

Are you kidding me? Microsoft has a library for that, which I'm pretty sure would be your first Google search result.


[/URL]

couldnt find it .___. thanks
This is for Export, any idea how to import it to same dt?

iLovEi said:

couldnt find it .___. thanks
This is for Export, any idea how to import it to same dt?

Code:

01 /// <summary> 
02 /// 
03 /// </summary> 
04 /// <param name="dt" /> 
05 protected void ExportExcel(DataTable dt) 
06 { 
07     if (dt == null||dt.Rows.Count==0) return; 
08     Microsoft.Office.Interop.Excel.Application xlApp = 
        new Microsoft.Office.Interop.Excel.Application(); 
09   
10     if (xlApp == null) 
11     { 
12         return; 
13     } 
14     System.Globalization.CultureInfo CurrentCI = 
        System.Threading.Thread.CurrentThread.CurrentCulture; 
15     System.Threading.Thread.CurrentThread.CurrentCulture = 
            new System.Globalization.CultureInfo("en-US"); 
16     Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; 
17     Microsoft.Office.Interop.Excel.Workbook workbook = 
    workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); 
18     Microsoft.Office.Interop.Excel.Worksheet worksheet = 
        (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; 
19     Microsoft.Office.Interop.Excel.Range range; 
20     long totalCount = dt.Rows.Count; 
21     long rowRead = 0; 
22     float percent = 0; 
23     for (int i = 0; i < dt.Columns.Count; i++) 
24     { 
25         worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; 
26         range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; 
27         range.Interior.ColorIndex = 15; 
28         range.Font.Bold = true; 
29     } 
30     for (int r = 0; r < dt.Rows.Count; r++) 
31     { 
32         for (int i = 0; i < dt.Columns.Count; i++) 
33         { 
34             worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); 
35         } 
36         rowRead++; 
37         percent = ((float)(100 * rowRead)) / totalCount; 
38     } 
39     xlApp.Visible = true; 
40 }

Click to expand...

There's several ways to do it as described here: . OleDB would be your fastest bet I guess.

fyi using Microsoft.Office.Interop will force the target maschine to have office installed.
a good alternative is OpenXml SDK that allows u to deserialize and serialize word, excel and powerpoint documents, and even allows to create new documents ofc to serialize them.

anyway working with this SDK will force u to write a static DataTable to DocumentSheet method (this object really sucks, cuz excel documents got a string table for all strings, in the data sections there is one xml parameter in the OuterXml property that tells u the current cell is a numeric, formula or string, in case of string the value is a ID referencing to your string table, annoying poop)