Worksheet export to C# Datatable

[trehanson]

Hello, I have a question about export sheet value to C# datatable.

xlSheet.ExportDataTable(RowStart, 1, RowCnt, MaxCol, false);

In this case, the C# datatable has generated.
But, there are missing column "Column8", when there is no data value in Excel Worksheet.

Is it possible to make a full columns datatable using ExportDataTable function?

* Here is the generated Xml format.

<NewDataSet>
<Sheet1>
<Column1>I</Column1>
<Column2>T-13728</Column2>
<Column3>I0034</Column3>
<Column4>42551</Column4>
<Column5>USD</Column5>
<Column6>7.739</Column6>
<Column7>tax002</Column7>
<Column9>NM2</Column9>
<Column10>42613</Column10>
<Column12>110030</Column12>
<Column13>1</Column13>
<Column14>400010</Column14>
<Column15>GEN</Column15>
<Column16>-20.75</Column16>
<Column17>-160.58</Column17>
<Column18>0</Column18>
</Sheet1>
</NewDataSet>

[Betsy.jiang]

Dear trehanson,

Thanks for your inquiry.
The function ExportDataTable returns full columns datatable. I suppose the reason is that the way you used to convert the datatable to xml cannot convert the colum with null value. I have a solution can implement it, please try it.

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"F:\10180.xlsx", ExcelVersion.Version2013);
            Worksheet sheet = workbook.Worksheets[0];
            DataTable dataTable = sheet.ExportDataTable(sheet.FirstRow,sheet.FirstColumn,sheet.LastRow,sheet.LastColumn,false);

            DataTable dtCloned = dataTable.Clone();
            foreach (DataColumn dc in dtCloned.Columns)
                dc.DataType = typeof(string);
            foreach (DataRow row in dataTable.Rows)
            {
                dtCloned.ImportRow(row);
            }
            foreach (DataRow row in dtCloned.Rows)
            {
                for (int i = 0; i < dtCloned.Columns.Count; i++)
                {
                    dtCloned.Columns[i].ReadOnly = false;
                    if (string.IsNullOrEmpty(row[i].ToString()))
                        row[i] = string.Empty;
                }
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(dtCloned);
            string xml = ds.GetXml();

Hope this helps. If there is any question, please let me know.

Sincerely,
Betsy
E-iceblue support team

[trehanson]

Thanks for you suggestion, It's work!

[Jane.Bai]

Hello,

Thanks for your feedback.
Please do not hesitate to contact us if you need any help.

Sincerely,
Jane
E-iceblue support team