c# - Excel 电子表格中缺少列

Question

我有一张发票 list ，我将它们转移到 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 文件时，工作日期列是空白的:

Answer 1

您应该将 BaseSQL 中的 JobDate 转换为字符串。

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

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

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