How can we specify the columns DataType to string, number Datetime in Excel/SpreadSheet when Export from c#

Question

am using the following code to export the data to Excel from c#. I will Provide one sample image with this to know better what i needed.

NOt only for a single column we cannot say this type is coming to a particular column in excel. during exporting of diffrent data

the code am using is

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                    Create(savingFileName, SpreadsheetDocumentType.Workbook);

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
                var bold1 = new System.Windows.Documents.Bold();
                DocumentFormat.OpenXml.Spreadsheet.CellFormat cf = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();

                // Add Sheets to the Workbook.
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets;
                sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                // Append a new worksheet and associate it with the workbook.
                var sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                        GetIdOfPart(worksheetPart),
                    SheetId = sheetId,
                    Name = "Sheet" + sheetId
                };
                sheets.Append(sheet);

                //Add Header Row. 
                var headerRow = new Row();
                headerRow.RowIndex = 10;
                var cell=new Cell();
                foreach (DataColumn column in dtCollection.Columns)
                {
                    cell = new Cell
                    {
                        DataType = CellValues.String, CellValue = new CellValue(column.ColumnName.ToUpper())
                    };
                    headerRow.AppendChild(cell);

                }
                sheetData.AppendChild(headerRow);

                foreach (DataRow row in dtCollection.Rows)
                {
                    var newRow = new Row();
                    foreach (DataColumn col in dtCollection.Columns)
                    {
                        cell = new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(row[col].ToString())
                        };
                        newRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(newRow);
                }

                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();

The image is as Follows

in this above pic you can see Debit and credit column it should be number but with my code it can be only string
Column Type

Credit-Number
Debit-Number
Date- Date-time
Code-String
opening Balance-Number

with the above requirement i need to export the datatable to excel in excel each column should be differnt datatype. how can i do this can any one help me to complete my work

Possible duplicate of How to make correct date format when writing data to Excel — stratovarius
i need not only date all of types needed .. in a single excel. here we cannot specify which type of column is coming each time of exporting in different methods. — Dona Susan Issac
That NumberFormat property is the solution for your actual problem. It covers all types of formats, numbers etc. But now you are asking another problem, 'how to map primitive column types dynamically on the export' am I right? Because the columns requested may differ in each export? — stratovarius
s , it is correct each time the column may be change during export so i need it dynamically for each column. — Dona Susan Issac

stratovarius stratovarius · Accepted Answer · 2017-02-09T09:26:22

Ok then here are two options I can come up quickly.

If you have few options of column choice, then map columnName <--> NumberFormat
If you have too much column possibility, then map primitiveType <--> NumberFormat (However you can only have one floating format for i.e. all Decimal columns, regardless of the business need of the specific column)

Here is an example for first option: Use a dictionary to lookup which number format you should inject into each column.

var headerFormatMap = new Dictionary<string, string>(); // <columnHeader, NumberFormat>
// i.e fill it like this
headerFormatMap.Add("TransactionDate", "MM/DD/YYYY");
headerFormatMap.Add("Debit", "##.000"); // Display 37.75 as 37.750.

Give formatting of all your possible columns like this. And when the time comes to export, get the correct numberFormat and inject it to its column:

// Assuming your first row is header. (I remember excel indexes start from 1)
// Do this foreach column
Range range = (Excel.Range)worksheetobject.Cells[1,1];
var header = (string)range.Value;
range.EntireColumn.NumberFormat = headerFormatMap[header];

How can we specify the columns DataType to string, number Datetime in Excel/SpreadSheet when Export from c#

1 Answers