使用的OpenXML插入一个DataTable到Excel [英] Using OpenXML to insert a datatable into excel
本文介绍了使用的OpenXML插入一个DataTable到Excel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个DataTable的 - 这取决于用户的选择 - 将产生与任意数量的行和列的动态数据表。我目前使用的OpenXML操纵所述S preadsheet。我怎么会去插入一个DataTable?
I have a datatable that - depending on the user selection - will generate a dynamic datatable with any number of rows and columns. I'm currently using OpenXml to manipulate said spreadsheet. How would I go about inserting a datatable?
感谢
斯图
推荐答案
我发现了一些code这点我是可以修改,以满足我的需求。希望有人认为这是有用的。
I found some code which I was able to modify to suit my needs. Hope someone finds this useful.
public void ExportDataTable(System.Data.DataTable exportData, SheetData sheetData)
{
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)42;
SheetData sheetData2 = new SheetData();
foreach (DataColumn column in exportData.Columns)
{
Cell headerCell = createTextCell(exportData.Columns.IndexOf(column) + 1, Convert.ToInt32(header.RowIndex.Value), column.ColumnName);
header.AppendChild(headerCell);
}
sheetData.AppendChild(header);
//loop through each data row
DataRow contentRow;
int startRow = 43;
for (int i = 0; i < exportData.Rows.Count; i++)
{
contentRow = exportData.Rows[i];
sheetData.AppendChild(createContentRow(contentRow, i + startRow));
}
}
private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private Row createContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
private string getColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
这篇关于使用的OpenXML插入一个DataTable到Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文