Missing column in Excel spreedshet


user123456789

I have a list of invoices and transfer it into an Excel spreadsheet.

enter image description here

All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet.

Here is 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 you can see the working date in it:

enter image description here

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

enter image description here

Vinki

You should convert JobDate in BaseSQL to string.

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

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

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

Related


Missing column in Excel spreedshet

user123456789 I have a list of invoices and transfer it to an Excel spreadsheet. All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet. Here is the code: string Directory = ConfigurationSettings.AppSettings["Dow

Missing column in Excel spreedshet

user123456789 I have a list of invoices and transfer it to an Excel spreadsheet. All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet. Here is the code: string Directory = ConfigurationSettings.AppSettings["Dow

Missing column in Excel spreedshet

user123456789 I have a list of invoices and transfer it into an Excel spreadsheet. All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet. Here is the code: string Directory = ConfigurationSettings.AppSettings["D

Missing column in Excel spreedshet

user123456789 I have a list of invoices and transfer it to an Excel spreadsheet. All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet. Here is the code: string Directory = ConfigurationSettings.AppSettings["Dow

Missing column in Excel spreedshet

user123456789 I have a list of invoices and transfer it to an Excel spreadsheet. All columns have been created into the spreadsheet except the "Work Date" column. Blank in spreadsheet. Here is the code: string Directory = ConfigurationSettings.AppSettings["Dow

MongoDB and FosUserBundle - missing column

Blake When I use data to load data-fixturesinto mymongoDB private function loadAdminUser() { $userManager = $this->container->get('fos_user.user_manager'); // Create our user and set details $user = $userManager->createUser(); $user->setUserna

Missing "'into'" in separate column (tidyr)

Zhao Hongfeng I constructed metadata for 10 papers. The dput()results are presented as follows: > dput(itemlist) structure(list(title = c("钱学森工程科学思想的实践者 [科普文章]", "超高周疲劳裂纹萌生与初始扩展的特征尺度 [科普文章]", "Proceedings of International conference on Airworthiness & Fatigue

Column names in vector are missing

username I have a dataframe with this format: DF1 <- DF0[i,6:12] (DF0 is the original data frame I read from) The resulting DF1 has the following names: DF1: home - train -bus -car .. - male - female 1 3 0 0 1 What I want to

Missing data for column xxx

PJ1992 I have downloaded multiple metro extracts from openstreetmap as PBF files, it works on the first one and creates the table when I try to import them using osm2pgsql. Then I want to add a column with cityID in planet_osm_ways so that after I try to impor

Android and SQlite - missing a column?

Gabriu Here is my DataBase.java. However, when queried in MainActivity, LogCat shows "No such column: Event_id" static final String tableName="EventList"; static final String colID="Event_id"; static final String colTitle="Title"; static final String colDetail

Missing column in PHPMyAdmin export

Collins I exported a table from the server that looks like this... CREATE TABLE IF NOT EXISTS `actions` ( `aid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique actions ID.', `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The object that tha

hibernate: missing column count

bart van hecklom Here is my entity: @Entity @Table(name="log_shop") public class LogShop { @Id @GeneratedValue private int id; private String platform; @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime") private Dat

Missing column in PHPMyAdmin export

Collins I exported a table from the server that looks like this... CREATE TABLE IF NOT EXISTS `actions` ( `aid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique actions ID.', `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The object that tha

Deliciouspie django, missing column

Bayek So I'm trying to interact with my yummy django api. I have created the following models in Django: #/models.py from django.db import models from tastypie.utils.timezone import now from django.contrib.auth.models import User class Project(models.Model):

Column names in vector are missing

username I have a dataframe with this format: DF1 <- DF0[i,6:12] (DF0 is the original data frame I read from) The resulting DF1 has the following names: DF1: home - train -bus -car .. - male - female 1 3 0 0 1 What I want to

MongoDB and FosUserBundle - missing column

Blake When I use data to load data-fixturesinto mymongoDB private function loadAdminUser() { $userManager = $this->container->get('fos_user.user_manager'); // Create our user and set details $user = $userManager->createUser(); $user->setUserna

Missing minutes in column

blog I'm analyzing data in a csv file sorted by a date and time column (Sdate) as follows (note: this is all one column): Sdate 01/01/2016 00:00 01/01/2016 01:00 01/ 01/2016 02:00 etc. However, when the data to be analyzed is divided into 15 minute intervals.

Column names in dataframe are missing

Claire I am developing a Shiny application. Part of the user interface involves using dropdown menus, checkboxes, etc. to select which of the columns of data to plot. I use grepl() with user input to shrink the large dataframe to the last column to plot. Speci

Deliciouspie django, missing column

Bayek So I'm trying to interact with my yummy django api. I have created the following models in Django: #/models.py from django.db import models from tastypie.utils.timezone import now from django.contrib.auth.models import User class Project(models.Model):

Insert query missing column

Ed Lentz I have a form that uses $_post to go to the insert.php page. When I do print_r the $_post info looks like this Array ( [extension] => Array ( [0] => 100 [1] => 101 [2] => 102 ) [secret] => Array (

MongoDB and FosUserBundle - missing column

Blake When I use data to load data-fixturesinto mymongoDB private function loadAdminUser() { $userManager = $this->container->get('fos_user.user_manager'); // Create our user and set details $user = $userManager->createUser(); $user->setUserna

Column names in vector are missing

username I have a dataframe with this format: DF1 <- DF0[i,6:12] (DF0 is the original data frame I read from) The resulting DF1 has the following names: DF1: home - train -bus -car .. - male - female 1 3 0 0 1 What I want to

Fill missing data in Excel

in Nakyo I've been working very hard on this recently. Let me give you an example to start with. Date Name Score Percentage Grade 3/23/2015 A 20 40 C 3/23/2015 B 15 30

Excel query for missing rows

Know almost I'm trying to setup a table in a spreadsheet using query, from file, xlsx The source data has 16,149 rows and after importing I only get 16,107 rows. I think I reduced the imports as much as possible: let Source = Excel.Workbook(File.Contents("