Excel电子表格中缺少列。

Question

我有一张发票清单，把它们转移到了Excel电子表格中。

所有列都已创建到电子表格中，除了工作日期列。在电子表格中这一列为空。

这是代码：

string Directory = ConfigurationSettings.AppSettings["DownloadDestination"] + Company.Current.CompCode + "\\";
string FileName = DataUtils.CreateDefaultExcelFile(Company.Current.CompanyID, txtInvoiceID.Value, Directory);
FileInfo file = new FileInfo(FileName);
Response.Clear();
Response.ContentType = "application/x-download";
Response.AddHeader("Content-Length", file.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.CacheControl = "public";
Response.TransmitFile(file.FullName);
Response.Flush();
Context.ApplicationInstance.CompleteRequest();

public static string CreateDefaultExcelFile(int CompanyID, string InvoiceNo, string CreateDirectory)
{
        List<MySqlParameter> param = new List<MySqlParameter>{ 
                { new MySqlParameter("CompanyID", CompanyID) },
                { new MySqlParameter("InvoiceNo", InvoiceNo) }
        };

        DataTable result = BaseDisplaySet.CustomFill(BaseSQL, param);

        string FileName = CreateDirectory + "InvoiceFile_" + DateTime.Now.ToString("yyyyMMddhhmmssff") + ".";
        FileName += "xlsx";
        XLWorkbook workbook = new XLWorkbook();
        workbook.Worksheets.Add(result, "Bulk Invoices");
        workbook.SaveAs(FileName);
        return FileName;
}

 private const string BaseSQL = " SELECT q.InvoiceNo AS InvoiceNumber, j.JobNo, j.JobDate AS JobDate, " +
             " (SELECT Name FROM job_address WHERE AddressType = 6 AND JobID = j.ID LIMIT 0,1) AS DebtorName,  " +
             " (SELECT CONCAT(Name,CONCAT(',',Town)) FROM job_address WHERE AddressType = 3 AND JobID = j.ID LIMIT 0,1) AS CollectFrom, " +
             " (SELECT CONCAT(Name,CONCAT(',',Town)) FROM job_address WHERE AddressType = 2 AND JobID = j.ID LIMIT 0,1) AS DeliverTo, " +
             " deladd.Town AS DeliverToTown,  deladd.County AS DeliveryToCounty, " +
             " (SELECT DocketNo FROM job_dockets WHERE JobID = j.ID LIMIT 0,1) AS DocketNo, " +
            " SUM(j.DelAmt) AS DelAmount, " +
             " (SELECT CAST(group_concat(DISTINCT CONCAT(AdvisedQty,' ',PieceType) separator ',') AS CHAR(200)) FROM  job_pieces WHERE JobID = j.ID GROUP BY JobID ) AS PieceBreakDown  " +
            " FROM Invoice q   " +
            " LEFT JOIN customer c ON q.accountcode = c.ID " +
            " INNER JOIN job_new j ON q.JobID = j.ID " +
            " LEFT JOIN job_address coladd ON coladd.JobID = j.ID AND coladd.AddressType = 3 " +
            " LEFT JOIN job_address deladd ON deladd.JobID = j.ID AND deladd.AddressType = 2 " +
            " WHERE q.IsActive = 1 AND q.Company_ID = ?CompanyID AND q.InvoiceNo = ?InvoiceNo " +
            " group by j.id";

该SQL语句返回了所有正确的信息，您可以看到任务日期在这里:

但是当我打开创建后的Excel文件时，任务日期列为空:

- user123456789

---

@Alexander 我发现设置列为链接的代码。我删除了它，并发现在Excel电子表格中工作日期列仍然为空白。我以为原因是它不是链接，但它必须是别的东西。 - user123456789

@Alexander，我已经编辑了我的问题。 - user123456789

@user123456789 你能把下载的Excel文件保存为CSV格式并在文本编辑器中打开它，看看列中是否有任何数据吗？你还导出其他日期时间吗？ - pmeyer

@user123456789 其他日期时间是否有填充？ - pmeyer

@Mark，我该怎么做呢？它从“BaseSQL”查询中获取日期。JobDate在数据库中设置为日期类型，那我该如何更改它呢？ - user123456789

Answer 1

您需要将BaseSQL中的JobDate转换为字符串。

下面给出了一个示例，您可以使用它来了解如何将datetime转换为varchar。

DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

- Venkata Dorisala

---

感谢帮助。我使用了 CAST(j.JobDate AS char) AS JobDate 进行转换，现在它出现在Excel电子表格中。 - user123456789

@user123456789 如果这个答案对您有帮助，请将其标记为“已接受”，以供未来读者参考。谢谢。 - AdmSteck

Answer 2

我不知道你使用什么框架来导出Excel数据以及它的功能强大程度，但我知道Excel不支持日期（惊讶吧！），至少在基于xml的（OpenXml）xlsx文档中不支持。它只能处理字符串和数字（保存在底层文档中作为字符串和数字文字）。
考虑到这一点，你可以使用一个简单的解决方法：通过sql中的cast/convert或C#中的ToString()将日期转换为字符串。显然，你会失去Excel的日期功能（如日期过滤器、自定义格式）。
然而，这不是唯一的方法（欢呼！）。你可以以与Excel相同的方式保存数据。如果你的框架不支持它，你就必须自己完成：步骤与使用DocumentFormat.OpenXml.dll手动创建xlsx文档相同。
实际上，Excel使用“OLE-Automation Date”格式作为日期的内部表示，它被实现为浮点数，其整数部分是从1899年12月30日午夜开始计算的天数，其小数部分代表当天时间除以24后得到的结果。这种表示存储在文档中作为数字文字。Excel通过相应单元格的编号格式区分日期和数字。有了这个想法，你可以使用一个不那么简单的解决方法：
首先，将你的日期转换为数字：

DateTime date = DateTime.Now;
double dateValue = date.ToOADate();
//or
TimeSpan time = DateTime.Now.TimeOfDay;
double timeValue = (DateTime.FromOADate(0) + time).ToOADate();

首先，应将双精度变量设置为Excel单元格的CellValue，您可以在DataTable中创建新列，并使用此转换填充它，然后删除原始的DateTime列。
其次，将日期格式应用于所需的单元格。不幸的是，所需的代码将因框架而异，但原则应该是相同的：

1. 定位相应的单元格范围（CellRange或Cells，可能是Columns）
2. 设置日期格式字符串（通过类似于 range.NumberFormat.Format="dd/mm/yyyy" 或 range.NumberFormatString="dd/mm/yyyy" 的方式）

然而，如果这个框架不支持简化的格式（非常奇怪的框架），您将不得不为标准日期格式设置 range.NumberFormatId=22 或创建新的数字格式。如果您非常不幸，这个框架像DocumentFormat.OpenXml一样简单，您将不得不创建具有相应NumberFormatId（22或自定义NumberFormat的id）的自定义CellFormat，将其添加到样式表中，并为相应的范围设置styleIndex。

- Aloraman

Answer 3

我不确定是否值得一试，但在处理大型数据集和数据表时，我通常使用ClosedXML。 它很容易地将一个数据表传递给它来创建XLSX文件。我在我的Windows Server 2008 r2上运行它，没有问题处理包含多个工作表的大量请求，所以我知道它非常有效。

https://closedxml.codeplex.com/

- Kevin B Burns