Column missing from excel spreedshet

Question

I have a list of invoices that and I transferred them to an Excel spreadsheet.

All the columns are created into the spreadsheet except for the Job Date column. That is blank in the spreadsheet.

Here's the code:

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";

The sql returns all the correct information and as you can see the job date is there:

But when I open the Excel file after it is created, the job date column is blank:

Please clear up a few things. You say "downloading to an excel" and "downloading the link columns to excel" and "download into the spreadsheet", when I see your code a) creating an Excel sheet and b) serving the Excel file as a download. You have trouble creating the spreadsheet properly, right? — Alexander, May 05 '16 at 10:54
@Alexander maybe creating or converting would have been a better word to use? Yes the problem is with creating the excel. The excel creates fine with all the columns from the image in the question except of the job date column. — user123456789, May 05 '16 at 11:36
Did you try converting the "Job Date" field to a string type (`varchar`, `nvarchar`, etc.) in your query before writing the result to the spreadsheet? — ConnorsFan, May 05 '16 at 15:16
At this point, I'd be interested in seeing debug information of the `DataTable result`. What's inside, what does it look like, is the job date column there? — Alexander, May 06 '16 at 18:46
@Alexander Yes the job date column is there. It just seems that the issue is because the job date isn't a link it isn't going into the excel spreadsheet. — user123456789, May 09 '16 at 08:14
@Alexander I found that code that was setting the columns as links. I removed this and found that the job date column is still blank in the excel spreedsheet. I thought the reason was because it wasn't a link but it must something else — user123456789, May 09 '16 at 10:01
Really need to see more code. `BaseDisplaySet.CustomFill` for example. You're sure that `result` contains the value? — Alexander, May 09 '16 at 10:16
Please read this answer from a different question and see if it helps. http://stackoverflow.com/a/15243069/2413794 — Alexander, May 09 '16 at 11:13
@Alexander No sorry that did not help. I not using Microsoft.Jet.OLEDB provider — user123456789, May 09 '16 at 11:26
@user123456789 Can you save the downloaded excel as a CSV and open it in a text editor see if there's any data in the columns at all? Are you exporting any other datatimes? — pmeyer, May 09 '16 at 15:35
@pmeyer There is no data in the job date columns in the text editor. No that is the only datetime I am trying to export — user123456789, May 09 '16 at 15:54
What happens if before data injection and file save you change the date column to Text type in the excel? — , May 10 '16 at 08:26
@Mark how would I do that? It gets the date from the `BaseSQL` query. JobDate is set as Date type in the database, so how would I change it? — user123456789, May 10 '16 at 11:01
@user123456789 "hack" a little: create an excel manually and open the workbook (programmatically) then inject the data like this http://stackoverflow.com/questions/21659069/writing-data-to-an-existing-excel-file-in-c-sharp — , May 10 '16 at 11:07
Try formatting JobDate as a string. It is also possible that it got interpreted as a number and is less then zero and not displayed. — Jehy, May 10 '16 at 12:16

score 11 · Accepted Answer · edited May 11 '16 at 21:34

11

You should convert JobDate in BaseSQL to string.

A sample example is given below. You can use it to get an idea how to convert datetime to varchar.

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

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

edited May 11 '16 at 21:34

SierraOscar

17,507
6
40
68

answered May 10 '16 at 14:42

Venkata Dorisala

4,783
7
49
90

2

thanks for the help. I used `CAST(j.JobDate AS char) AS JobDate` to convert it and it appears in the excel spreadsheet now – user123456789 May 10 '16 at 15:50
@user123456789 If this answer worked for you, you should mark it as "accepted" for future readers. Thanks. – AdmSteck Feb 07 '17 at 15:01

score 4 · Answer 2 · answered May 11 '16 at 20:53

I don't know what framework do you use to export data to excel and how powerful it is, but I do know that Excel does not directly support dates (surprise!), at least not in xml-based (OpenXml) xlsx documents. It works only with strings and numbers (which are saved in underlying document as string and number literals)
Considering that, you can use simple workaround: convert your dates to strings via either cast/convert in sql or ToString() in C#. You will loose Excel date functionality (like date filters, custom formats), obviously.
However, it is not an only way (cheers!). You can save your data in the same way Excel stores it. If your framework does not support it, you will have to do it yourself: the recipe will be the same as with creation xlsx documents by hand with DocumentFormat.OpenXml.dll.
Actually, Excel uses "OLE-Automation Date" format as internal representation for dates, which is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. This representation is stored in document as number literal. Excel distinguishes dates and numbers by numbering format of corresponding cell. With that in mind, you can use not so simple workaround:
First, convert your dates to numbers:

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

Then double variable should be set to CellValue of Excel Cell, you can create new column with double datatype in DataTable, fill it using this transformation, then drop original DateTime column.
Second, apply date format to desired cells. Unfortunately, required code will differ between frameworks, but the principle should be the same:

Locate corresponding cell range (either CellRange or Cells, maybe Columns)
Set date format string (via something like range.NumberFormat.Format="dd/mm/yyyy" or range.NumberFormatString="dd/mm/yyyy")

If, however, this framework does not support simplified formatting (very strange framework that will be), you will have to either set range.NumberFormatId=22 for standard date format or create new number format. If you are rather unlucky and this framework is as simple as DocumentFormat.OpenXml, you will have to create custom CellFormat with correspoding NumberFormatId (22 or id of custom NumberFormat), add it to stylesheet and set styleIndex for corresponding range.

score 3 · Answer 3 · answered May 11 '16 at 13:41

I don't know if it's worth checking out, but when working with large datasets and datatables in the past I usually use ClosedXML to get it done. It's easy to just pass a datatable and let it handle creating the XLSX for it. I have it running my my Windows Server 2008 r2 without issue handling large requests with multiple sheets so I know it works really well.

https://closedxml.codeplex.com/

Column missing from excel spreedshet

3 Answers3