How To Change XLS Column DataType Using .NET

Question

I am trying to export a datatable into an xls file. Below are my code:

using ExcelIt = Microsoft.Office.Interop.Excel;

protected void Export2xlsWorkBook(string filename)
        {
            if (filename != string.Empty)
            {
                if (filename.EndsWith(".xls"))
                    filename = txtNewFileName.Text + ".xls";
            }
            else
                filename = "NewFile.xls";

            filename = "C:\\Documents and Settings\\My Documents\\Exported XLS\\" + filename;

            ExcelIt.Application objXL = new ExcelIt.Application();
            ExcelIt.Workbook oWB;
            ExcelIt.Worksheet oSheet;
            ExcelIt.Range oRange;

            objXL.Visible = true;
            objXL.DisplayAlerts = false;

            oWB = objXL.Workbooks.Add(Missing.Value);

            DataTable dt = CreateDataTable();

            oSheet = (ExcelIt.Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Exported Table";

            int rowCount = 1;
            foreach (DataRow dr in dt.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dt.Columns.Count + 1; i++)
                {
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                }
            }

            oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                          oSheet.Cells[rowCount, dt.Columns.Count]);

            oRange.EntireColumn.AutoFit();

            oRange = oSheet.get_Range(oSheet.Cells[1, 2], oSheet.Cells[rowCount + 10, dt.Columns.Count]);

            oSheet.Protection.AllowEditRanges.Add("NonePK", oRange, Missing.Value);

            oRange = oSheet.get_Range(oSheet.Cells[rowCount + 1, 1], oSheet.Cells[rowCount + 10, 1]);

            oSheet.Protection.AllowEditRanges.Add("PKEmptyRow", oRange, Missing.Value);

            oSheet._Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            oSheet = null;
            oRange = null;

            oWB.SaveAs(filename, ExcelIt.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                ExcelIt.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            objXL.Quit();

            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

For certain reasons, I would like to: Set all columns datatype into text. Currently as I didn't specify anything the datatypes are set into "general" instead.

Can someone advise me on how to achieve this? Regards

Answer 1

you can use Range.NumberFormat Property

Range usedRange = oSheet.UsedRange;
usedRange.NumberFormat = "@";