First row as column names in C# Export to Excel File

huangapple go评论155阅读模式
英文:

First row as column names in C# Export to Excel File

问题

我写了一个小应用程序,它接收我的SQL查询并将数据输出到Excel文件,然后通过电子邮件发送给我自己。一切都运行得很好,只是我没有得到第一行作为列名。我想象我可能在某处缺少了一个参数,可以执行这个操作,或者也许我需要手动硬编码第一行的列名?以下是我的代码:

英文:

I've written a small application that takes my SQL query and outputs the data to an excel file and emails it to myself. Everything works wonderfully except that I am not getting the first row as column names. I imagine I'm missing a parameter somewhere that would do this or perhaps I need to manually hardcode that first line line of names in it? Here is my code:

namespace MaintenanceReportDBtoExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection cnn;
            string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionString = @"data source=MFG-DB-DEV;Integrated Security=true;initial catalog=OLTP;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = @"SELECT MS.MaintCompletionDate, RF.ObjectType, RD.ResourceName, MRB.MaintenanceReqName, MS.NextDateDue,
                    CASE
                        WHEN

                            CASE MRB.MaintenanceReqName
                                WHEN 'Bi-Annual' THEN DATEADD(year, 2, MS.LastDateDue)
			                    WHEN 'Annual' THEN DATEADD(year, 1, MS.LastDateDue)
			                    WHEN 'Calibration - Annual' THEN DATEADD(year, 1, MS.LastDateDue)
			                    WHEN 'Annual - ASY' THEN DATEADD(year, 1, MS.LastDateDue)
			                    WHEN 'Semi-Annual' THEN DATEADD(month, 6, MS.LastDateDue)
			                    WHEN 'Calibration - Semi-Annual' THEN DATEADD(month, 6, MS.LastDateDue)
			                    WHEN 'Quarterly' THEN DATEADD(month, 3, MS.LastDateDue)
			                    WHEN 'Calibration - Quarterly' THEN DATEADD(month, 3, MS.LastDateDue)
			                    WHEN 'Quarterly - ASY' THEN DATEADD(month, 3, MS.LastDateDue)
			                    WHEN 'Monthly' THEN DATEADD(month, 1, MS.LastDateDue)
			                    WHEN 'Calibration - Weekly' THEN DATEADD(week, 1, MS.LastDateDue)
			                    WHEN 'Weekly - ASY' THEN DATEADD(week, 1, MS.LastDateDue)
			                    WHEN 'Semi-Annual - ASY' THEN DATEADD(month, 6, MS.LastDateDue)
			                    WHEN 'Monthly - ASY' THEN DATEADD(month, 1, MS.LastDateDue)
			                    WHEN 'Bi-Annual - ASY' THEN DATEADD(year, 2, MS.LastDateDue)
			                    WHEN 'Weekly' THEN DATEADD(week, 1, MS.LastDateDue)
			                    END < GETDATE() THEN 'PAST DUE'

                        WHEN MS.LastDateDue IS NULL AND MS.NextDateDue < GETDATE() THEN 'PAST DUE'

                        WHEN MS.LastDateDue IS NULL AND MS.NextDateDue > GETDATE() THEN 'READY'

                        WHEN MS.LastDateDue IS NULL AND MS.NextDateDue IS NULL THEN 'N/A'

                        ELSE 'READY'
                    END AS MaintenanceStatus
                    FROM CamstarSch.ResourceDef RD
                    INNER JOIN CamstarSch.MaintenanceStatus MS ON RD.ResourceId = MS.ResourceId
                    INNER JOIN CamstarSch.ResourceFamily RF ON RF.ResourceFamilyId = RD.ResourceFamilyId
                    INNER JOIN CamstarSch.AssignedMaintReq AMR ON AMR.AssignedMaintReqId = MS.AssignedMaintReqId
                    INNER JOIN CamstarSch.MaintenanceReq MR ON AMR.MaintenanceReqId = MR.MaintenanceReqId
                    INNER JOIN CamstarSch.MaintenanceReqBase MRB ON MRB.MaintenanceReqBaseId = MR.MaintenanceReqBaseId
                    ORDER BY NextDateDue DESC; ";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }

            xlWorkBook.SaveAs(@"C:\Users\mbelmer\Desktop\Helpful Files\Test files\MaintenanceReport.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
            GC.Collect();

            MailHelper.SendMail(new string[] { "mbelmer@rocelec.com" }, $"Maintenance Report {DateTime.Today}", $"Maintenance Report for {DateTime.Today}", new string[] { @"C:\Users\mbelmer\Desktop\Helpful Files\Test files\MaintenanceReport.xls" });
            Thread.Sleep(500);
            File.Delete(@"C:\Users\mbelmer\Desktop\Helpful Files\Test files\MaintenanceReport.xls");
        }
    }
}

答案1

得分: 0

你可以尝试将这段代码替换为以下内容:

for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
    data = ds.Tables[0].Columns[j].ColumnName;
    xlWorkSheet.Cells[1, j + 1] = data;
}

for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
    {
        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        xlWorkSheet.Cells[i + 2, j + 1] = data;
    }
}
英文:

You can try replace this:

for (i = 0; i &lt;= ds.Tables[0].Rows.Count - 1; i++)
        {
            for (j = 0; j &lt;= ds.Tables[0].Columns.Count - 1; j++)
            {
                data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                xlWorkSheet.Cells[i + 1, j + 1] = data;
            }
        }

On this:

for (int j = 0; j &lt;= ds.Tables[0].Columns.Count - 1; j++)
        {
            data = ds.Tables[0].Columns[j].ColumnName;
            xlWorkSheet.Cells[1, j + 1] = data;
        }

for (i = 0; i &lt;= ds.Tables[0].Rows.Count - 1; i++)
    {
        for (j = 0; j &lt;= ds.Tables[0].Columns.Count - 1; j++)
        {
            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
            xlWorkSheet.Cells[i + 2, j + 1] = data;
        }
    }

huangapple
  • 本文由 发表于 2020年1月6日 22:48:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/59614117.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定