从数据表创建 Excel 工作表
本文关键字:工作 Excel 创建 数据表 | 更新日期: 2023-09-27 18:34:34
我正面临一个奇怪的现象。我编写了一个将值从数据表复制到 excel 工作表的方法。问题是行被复制到列,列被复制到行。
例如:
数据表:
Item Quantity UnitPrice TotalPrice
A 10 2 20
B 3 15 45
C 100 0.5 50
Excel工作表:
Item Quantity UnitPrice TotalPrice
A B C
10 3 100
2 15 0.5
20 45 50
我的代码:
private void PopulateDataWorkSheet()
{
xl.Workbooks xlWorkbooks = _xlApp.Workbooks;
xl.Workbook xlWorkbook = xlWorkbooks.Open(_excelFileName);
xl.Sheets xlSheets = xlWorkbook.Sheets;
const int startRowIndex = 2; // first row for columns title
const int startColumnIndex = 1;
xl.Worksheet xlDataWorkSheet = xlSheets[DATA_SHEET];
xl.Range xlCells = xlDataWorkSheet.Cells;
for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
{
for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
{
xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
}
}
xlWorkbook.Save();
xlWorkbook.Close();
xlWorkbooks.Close();
ReleaseExcelObject(xlCells);
ReleaseExcelObject(xlDataWorkSheet);
ReleaseExcelObject(xlSheets);
ReleaseExcelObject(xlWorkbook);
ReleaseExcelObject(xlWorkbooks);
}
我可以通过更改以下内容来简单地解决它,但我不清楚出了什么问题:
xlCells[startColumnIndex + columnindex][startRowIndex + rowindex] = _datatable.Rows[rowindex][columnindex];
我是否在代码中遗漏了某些内容?
我正面临一个奇怪的现象。我是否在代码中遗漏了某些内容?
欢迎来到Excel Range
,动态,可选参数和COM对象Default
成员概念的世界。
Excel Range
是一件奇怪的事情。几乎每个方法/属性都返回另一个范围。使用的索引器如下所示
dynamic this[object RowIndex, object ColumnIndex = Type.Missing] { get; set; }
即具有所需的行索引和可选的列索引。
让我们接听这个电话
xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = ...;
相当于这样
var range1 = (Excel.Range)xlCells[startRowIndex + rowindex];
var range2 = (Excel.Range)range1[startColumnIndex + columnindex];
// ^^^^^^
// this is treated as row index offset from the range1
range2.Value2 = ...;
很快,您需要将其更改为
xlCells[startRowIndex + rowindex, startColumnIndex + columnindex] = ...;
您需要更改访问 Excel 单元格的方式。 cells[StartRowIndex+ rowindex, StartColumnIndex + columnindex]
先行,后列。您所做的是将列作为第一个参数的cells[StartRowIndex + rowindex][StartColumnIndex + columnindex]
。
循环的顺序根本不重要......所以正确的解决方案是:
for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
{
for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
{
xlCells[startRowIndex + rowindex, startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
}
}
干杯!
你做错了,你需要这样做:
for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
{
for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
{
xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
}
}
您需要列,然后是行。
您可以使用以下代码来解决此问题。它对我来说效果很好。
xlApplication = new Microsoft.Office.Interop.Excel.Application();
// Create workbook .
xlWorkBook = xlApplication.Workbooks.Add(Type.Missing);
// Get active sheet from workbook
xlWorkSheet = xlWorkBook.ActiveSheet;
xlWorkSheet.Name = "Report";
xlWorkSheet.Columns.ColumnWidth = 30;
xlApplication.DisplayAlerts = false;
for (int rowIndex = 0; rowIndex < gridViewDataTable.Rows.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < gridViewDataTable.Columns.Count; colIndex++)
{
if (rowIndex == 0)
{
xlWorkSheet.Cells[rowIndex + 1, colIndex + 1] = gridViewDataTable.Columns[colIndex].ColumnName;
xlWorkSheet.Cells[rowIndex + 1, colIndex + 1].Interior.Color = System.Drawing.Color.LightGray;
}
xlWorkSheet.Cells[rowIndex + 2, colIndex + 1].Borders.Color = Color.Black;
xlWorkSheet.Cells[rowIndex + 2, colIndex + 1].Interior.Color = System.Drawing.Color.White;
xlWorkSheet.Cells[rowIndex + 2, colIndex + 1] = gridViewDataTable.Rows[rowIndex][colIndex];
}
}
xlWorkBook.SaveAs(filename);
xlWorkBook.Close();
xlApplication.Quit();