У меня есть список счетов, которые я передал в электронную таблицу 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 после его создания, столбец даты задания пуст:
Вы должны преобразовать JobDate в BaseSQL в строку.
Пример примера приведен ниже. Вы можете использовать его, чтобы получить представление о том, как преобразовать datetime в varchar.
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
Я не знаю, какие рамки вы используете для экспорта данных в excel и насколько они эффективны, но я знаю, что Excel напрямую не поддерживает даты (сюрприз!), по крайней мере, не в xml-based (OpenXml) документы xlsx. Он работает только со строками и числами (которые сохраняются в базовом документе как строковые и числовые литералы)
Учитывая это, вы можете использовать простой обходной путь: конвертировать ваши даты в строки с помощью cast/convert в sql или ToString() на С#. Очевидно, вы потеряете функциональность даты Excel (например, фильтры даты, пользовательские форматы).
Однако это не единственный способ (приветствия!). Вы можете сохранять свои данные так же, как Excel хранит его. Если ваша инфраструктура не поддерживает его, вам придется сделать это самостоятельно: рецепт будет таким же, как при создании документов xlsx вручную с помощью DocumentFormat.OpenXml.dll.
Фактически, Excel использует формат даты OLE-Automation Date как внутреннее представление для дат, которое реализуется как число с плавающей запятой, интегральным компонентом которого является количество дней до или после полуночи, 30 декабря 1899 года, и чья дробная составляющая представляет время в этот день делится на 24. Это представление хранится в документе как литерал числа. Excel различает даты и номера по нумерации формата соответствующей ячейки. Имея это в виду, вы можете использовать не так просто обходной путь:
Сначала преобразуйте свои даты в номера:
DateTime date = DateTime.Now;
double dateValue = date.ToOADate();
//or
TimeSpan time = DateTime.Now.TimeOfDay;
double timeValue = (DateTime.FromOADate(0) + time).ToOADate();
Затем двойную переменную следует установить в CellValue из Excel Cell, вы можете создать новый столбец с типом данных double в DataTable, заполнить его с помощью этого преобразования, а затем отбросить исходный столбец DateTime.
Во-вторых, примените формат даты к желаемым ячейкам. К сожалению, требуемый код будет отличаться между фреймворками, но принцип должен быть одним и тем же:
range.NumberFormat.Format="dd/mm/yyyy" или range.NumberFormatString="dd/mm/yyyy")Если, однако, эта структура не поддерживает упрощенное форматирование (очень странная структура, которая будет), вам придется либо установить range.NumberFormatId=22 для стандартного формата даты, либо создать новый формат номера. Если вам не повезло, и эта структура так же проста, как DocumentFormat.OpenXml, вам нужно будет создать собственный CellFormat с корреляцией NumberFormatId (22 или идентификатор пользовательского NumberFormat), добавить его в таблицу стилей и установить styleIndex для соответствующего диапазона.
Я не знаю, стоит ли проверять, но когда я работал с большими наборами данных и datatables в прошлом, я обычно использую ClosedXML, чтобы сделать это. Легко просто передать datatable и позволить ему обрабатывать создание XLSX для него. Я запускаю свой Windows Server 2008 r2 без обработки больших запросов с несколькими листами, поэтому я знаю, что он работает очень хорошо.