如何设置自定义格式的单元格，如datetime在inffragistics excel导出数据到excel时

Question

我正在使用inffragistics excel导出excel。我想将excel的列类型设置为与db的列类型相同。意味着如果列类型是datetime，它应该在inffragistics excel工作表中创建datetime列。

我试过下面的代码。

 Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
        // Create the worksheet to represent this data table
        Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add(dtReportData.TableName);
        // Create column headers for each column
        for (int columnIndex = 0; columnIndex < dtReportData.Columns.Count; columnIndex++)
        {
            worksheet.Rows[0].Cells[columnIndex].Value = dtReportData.Columns[columnIndex].ColumnName;
        }
        // Starting at row index 1, copy all data rows in
        // the data table to the worksheet
        int rowIndex = 1;
        foreach (DataRow dataRow in dtReportData.Rows)
        {
            Infragistics.Documents.Excel.WorksheetRow row = worksheet.Rows[rowIndex++];
            for (int columnIndex = 0; columnIndex < dataRow.ItemArray.Length; columnIndex++)
            {
                row.Cells[columnIndex].Value = dataRow.ItemArray[columnIndex];
                if (dtReportData.Columns[columnIndex].DataType == Type.GetType("System.Decimal"))
                {
                    //Here column should be of type decimal.   
                }
                else if (dtReportData.Columns[columnIndex].DataType == Type.GetType("System.DateTime"))
                {
                   //Here column type should be of type datetime.                        
                }
            }

有人能帮我一下吗?

Answer 1

我不认为你可以直接设置列类型，正如我所记得的，它是由单元格值决定的，这里是一个示例，对于double，我们以字符串格式传递，希望它有帮助。

    if (col.Definition.Type == DataType.Double)
    {
            .....
                worksheet.Rows[r].Cells[column].CellFormat.FormatString = format;
    }
    else if (col.Definition.Type == DataType.DateTime)
    {
        ...
            worksheet.Rows[r].Cells[column].Value = col[row];
    }
    else if (col.Definition.Type == DataType.Boolean)
    {
        ...
        worksheet.Rows[r].Cells[column].Value = (bool) col[row];
    }
    else
    {
        // use string value, because some cell in the table is not a valid type in excel
        worksheet.Rows[r].Cells[column].Value = col[row].ToString();
    }