SQL查询到Excel在c#使用Epplus

Question

我一直在努力解决这个问题。我试图写一个字符串和列名从sql查询到excel文档。但是我只写了行。我如何修复代码写所有行和列?

var Template = new FileInfo(@"C:TempXLSNew.xlsx");
var xlPackage = new ExcelPackage(Template);
var wsCards = xlPackage.Workbook.Worksheets.Add(NAME_WORKSHEET);
using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
{
sqlConn.Open();
using (SqlCommand command = new SqlCommand(SQL_WORKSHEET, sqlConn))
{
var reader = command.ExecuteReader();
int row = 1;
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow rw in schemaTable.Rows)
{
foreach (DataColumn column in schemaTable.Columns)
{
if (column.ColumnName == "ColumnName")
{
wsCards.Cells["A1"].Value = rw[column];
}
}
}
while (reader.Read())
{
row++;
for (int col = 1; col <= reader.FieldCount; col++)
{
wsCards.Cells[row, col].Value = reader.GetValue(col - 1);
}
}
xlPackage.SaveAs(Template);
xlPackage.Dispose();
}
}

Answer 1

var Template = new FileInfo(@"C:TempXLSNew.xlsx");
var xlPackage = new ExcelPackage(Template);
var wsCards = xlPackage.Workbook.Worksheets.Add(NAME_WORKSHEET);
using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
{
sqlConn.Open();
using (SqlCommand command = new SqlCommand(SQL_WORKSHEET, sqlConn))
{
var reader = command.ExecuteReader();
int row = 1,col=1;

DataTable schemaTable = reader.GetSchemaTable();
//foreach (DataRow rw in schemaTable.Rows)
//{
// Write the headers to the first row
foreach (DataColumn column in schemaTable.Columns)
{
// Condition Will only be required if you want to write 
// specific column names
//if (column.ColumnName == "ColumnName")
//{ 

wsCards.Cells[1,col].Value = rw[column].ColumnName;
col++;
//}
}
//}
while (reader.Read())
{
row++;
for ( col = 1; col <= reader.FieldCount; col++)
{
wsCards.Cells[row, col].Value = reader.GetValue(col - 1);
}
}
xlPackage.SaveAs(Template);
xlPackage.Dispose();
}
}

这段代码将在excel工作表的第一行写入模式的标题