英文:
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 <= 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;
}
}
On this:
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 (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 + 2, j + 1] = data;
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论