This project has moved. For the latest updates, please go here.

Cells.InsertDataTable Time values are recognized as String

Dec 20, 2013 at 1:47 PM
Hi,

I am using the Cells.InsertDataTable functionality wherein I import a dataTable from a query to a particular worksheet in the Excel file.
        public void ClosedXml(DataTable dt, string usertemplate, string exceltab)
            {
            var workbook = new XLWorkbook(usertemplate);
            var worksheet = workbook.Worksheet(exceltab);
           
            worksheet.Cell("A2").InsertData(dt.AsEnumerable());
            workbook.Save();

            }
But when I opened the Excel file, the results were not the ones I expected.

Image

As you can see, columns P and Q contain integers and thus automatically recognized and has formula that is why they are in the right side of the cell. but columns R to W are time values and therefore are recognized only as strings that is why they are on the left side.

In my previous implementation, I use the QueryTables interface of Excel interop wherein I transfer a CSV file coming from a dataTable to Excel that is why all the cells have formula and are on the right side when I opened the Excel file.

But yeah, Excel Automation using the Interop is not recommended for server-side deployment.

Are there any similar ways to do the same using ClosedXML?

Any ideas are appreciated.

Thanks!
Dec 27, 2013 at 2:10 PM
If you want to align the values in those columns to the right, choose columns 18 through 23 and do the following
worksheet.Columns(18,23).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
Dec 28, 2013 at 12:38 PM
I appreciate your response but not exactly what i am looking for. Aligning the texts to the right will simply have no difference. columns 18-23 are time values and what I want is that when it is inside the cells, it has a formula i.e.

when hours > 24, the formula will be 1/(2++)/1900 then the time.

but if hours < 24, only the time will be idsplayed in the formula. no date of 1/1/1900.


roberttanenbaum wrote:
If you want to align the values in those columns to the right, choose columns 18 through 23 and do the following
worksheet.Columns(18,23).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
Dec 29, 2013 at 8:01 AM
I managed to find the solution to my query. But I did not utilize anymore the Cell.InsertData property. What I did was to parse each and every cell according to its data type before assigning it to a particular Excel cell.

For the time values, I used regular expressions to match, then used the boolean variable to parse and set the data type of the cell to either TimeSpan or DateTime depending on what I need.
 public void DataTableToExcel(DataTable dt, string usertemplate, string exceltab)
            {

            //open worksheet
            var workbook = new XLWorkbook(usertemplate);
            var worksheet = workbook.Worksheet(exceltab);

            //declare variables
            int totrow = dt.Rows.Count;
            int totcol = dt.Columns.Count;
            int numval;
            double dobval;
            DateTime dateval;
            TimeSpan timeval;

            //loop through each cells

                for (int i = 0; i < totrow; i++)
                    {
                    DataRow dataRow = dt.Rows[i];

                    for (int j = 0; j < dataRow.Table.Columns.Count; j++)
                        {
                        #region Regex 0:00:00
                        string input = dataRow[j].ToString();
                        string pattern = "^\\d{1}:\\d{2}:\\d{2}";
                        Match match = Regex.Match(input, pattern);
                        bool match1;
                        if (match.Success)
                            {
                            match1 = true;
                            }
                        else
                            {
                            match1 = false;
                            }
                        #endregion

                        #region Regex 00:00:00
                        string input2 = dataRow[j].ToString();
                        string pattern2 = "^\\d{2}:\\d{2}:\\d{2}";
                        Match match2 = Regex.Match(input2, pattern2);
                        bool match3;
                        if (match2.Success)
                            {
                            match3 = true;
                            }
                        else
                            {
                            match3 = false;
                            }
                        #endregion

                        if (int.TryParse(dataRow[j].ToString(), out numval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Number;
                            }
                        else if (double.TryParse(dataRow[j].ToString(), out dobval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Number;
                            }
                        else if (TimeSpan.TryParse(dt.Rows[i].ToString(), out timeval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.TimeSpan;
                            continue;
                            }
                        else if (match1 == true) //0:00:00
                            {
                            string[] timeval1 = dataRow[j].ToString().Split(':');
                            TimeSpan t = new TimeSpan(Convert.ToInt16(timeval1[0].ToString()), Convert.ToInt16(timeval1[1].ToString()), Convert.ToInt16(timeval1[2].ToString()));
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = t.ToString();
                            cell.DataType = XLCellValues.TimeSpan;
                            match1 = false;
                            }
                        else if (match3 == true) //00:00:00
                            {
                            string[] timeval1 = dataRow[j].ToString().Split(':');
                            int hour = Convert.ToInt16(timeval1[0].ToString());

                            if (hour <= 23)
                                {
                                TimeSpan t = new TimeSpan(Convert.ToInt16(timeval1[0].ToString()), Convert.ToInt16(timeval1[1].ToString()), Convert.ToInt16(timeval1[2].ToString()));
                                var cell = worksheet.Cell(i + 2, j + 1);
                                cell.Value = t.ToString();
                                cell.DataType = XLCellValues.TimeSpan;
                                }
                            else
                                {
                                WeirdDateTime d = new WeirdDateTime();
                                d = d.Parse(dataRow[j].ToString());
                                object a = d.ReturnValue;
                                var cell = worksheet.Cell(i + 2, j + 1);
                                cell.Value = a.ToString();
                                cell.DataType = XLCellValues.DateTime;
                                cell.Style.NumberFormat.NumberFormatId = 46;
                                }

                            match3 = false;
                            }
                        else if (DateTime.TryParse(dataRow[j].ToString(), out dateval))
                            {
                            var cell = worksheet.Cell(i + 2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.DateTime;
                            }
                        else
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Text;
                            }
                        }
                    }

            workbook.Save();
            }
Feb 5, 2014 at 1:43 AM
Edited Feb 5, 2014 at 1:44 AM
Just realized after long tests that when using this code to fill in many cells, the IIS worker process and therefore the memory consumption shoots up to more than 1GB. Maybe because I used a for loop? Can the developer of this library provide inputs on how to help lessen the memory consumption when I use this code?

I could have just used the Cell.InsertDataTable property instead, but due to certain data types (timestamp variables) I need to use a regular expression to match them then a boolean variable for the if-else statement therefore the use of a for loop.

Much appreciated! :)


small4ward wrote:
I managed to find the solution to my query. But I did not utilize anymore the Cell.InsertData property. What I did was to parse each and every cell according to its data type before assigning it to a particular Excel cell.

For the time values, I used regular expressions to match, then used the boolean variable to parse and set the data type of the cell to either TimeSpan or DateTime depending on what I need.
 public void DataTableToExcel(DataTable dt, string usertemplate, string exceltab)
            {

            //open worksheet
            var workbook = new XLWorkbook(usertemplate);
            var worksheet = workbook.Worksheet(exceltab);

            //declare variables
            int totrow = dt.Rows.Count;
            int totcol = dt.Columns.Count;
            int numval;
            double dobval;
            DateTime dateval;
            TimeSpan timeval;

            //loop through each cells

                for (int i = 0; i < totrow; i++)
                    {
                    DataRow dataRow = dt.Rows[i];

                    for (int j = 0; j < dataRow.Table.Columns.Count; j++)
                        {
                        #region Regex 0:00:00
                        string input = dataRow[j].ToString();
                        string pattern = "^\\d{1}:\\d{2}:\\d{2}";
                        Match match = Regex.Match(input, pattern);
                        bool match1;
                        if (match.Success)
                            {
                            match1 = true;
                            }
                        else
                            {
                            match1 = false;
                            }
                        #endregion

                        #region Regex 00:00:00
                        string input2 = dataRow[j].ToString();
                        string pattern2 = "^\\d{2}:\\d{2}:\\d{2}";
                        Match match2 = Regex.Match(input2, pattern2);
                        bool match3;
                        if (match2.Success)
                            {
                            match3 = true;
                            }
                        else
                            {
                            match3 = false;
                            }
                        #endregion

                        if (int.TryParse(dataRow[j].ToString(), out numval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Number;
                            }
                        else if (double.TryParse(dataRow[j].ToString(), out dobval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Number;
                            }
                        else if (TimeSpan.TryParse(dt.Rows[i].ToString(), out timeval))
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.TimeSpan;
                            continue;
                            }
                        else if (match1 == true) //0:00:00
                            {
                            string[] timeval1 = dataRow[j].ToString().Split(':');
                            TimeSpan t = new TimeSpan(Convert.ToInt16(timeval1[0].ToString()), Convert.ToInt16(timeval1[1].ToString()), Convert.ToInt16(timeval1[2].ToString()));
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = t.ToString();
                            cell.DataType = XLCellValues.TimeSpan;
                            match1 = false;
                            }
                        else if (match3 == true) //00:00:00
                            {
                            string[] timeval1 = dataRow[j].ToString().Split(':');
                            int hour = Convert.ToInt16(timeval1[0].ToString());

                            if (hour <= 23)
                                {
                                TimeSpan t = new TimeSpan(Convert.ToInt16(timeval1[0].ToString()), Convert.ToInt16(timeval1[1].ToString()), Convert.ToInt16(timeval1[2].ToString()));
                                var cell = worksheet.Cell(i + 2, j + 1);
                                cell.Value = t.ToString();
                                cell.DataType = XLCellValues.TimeSpan;
                                }
                            else
                                {
                                WeirdDateTime d = new WeirdDateTime();
                                d = d.Parse(dataRow[j].ToString());
                                object a = d.ReturnValue;
                                var cell = worksheet.Cell(i + 2, j + 1);
                                cell.Value = a.ToString();
                                cell.DataType = XLCellValues.DateTime;
                                cell.Style.NumberFormat.NumberFormatId = 46;
                                }

                            match3 = false;
                            }
                        else if (DateTime.TryParse(dataRow[j].ToString(), out dateval))
                            {
                            var cell = worksheet.Cell(i + 2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.DateTime;
                            }
                        else
                            {
                            var cell = worksheet.Cell(i+2, j + 1);
                            cell.Value = dataRow[j].ToString();
                            cell.DataType = XLCellValues.Text;
                            }
                        }
                    }

            workbook.Save();
            }
Apr 5, 2014 at 3:03 PM
I do hope that the developer of this library will improve the InsertDataTable method so that I won't come up with this kind of code again.

Many thanks in advance. :)