给位高手,请指教!
现在有一个有数据的DataTable 和一个已经建好的Excel(包括3个Sheet 其中Sheet1中有数据,其余Sheet2和Sheet3没有数据) 我想把DataTable 中的数据写入到Excel 中的Sheet2或Sheet3中,在不破坏Sheet1原有内容和格式的同时! 初来乍到,希望路过的高手展现出您的高招,感恩不尽了! 注:最好是源码,有待我进一步学习和研究!
现在有一个有数据的DataTable 和一个已经建好的Excel(包括3个Sheet 其中Sheet1中有数据,其余Sheet2和Sheet3没有数据) 我想把DataTable 中的数据写入到Excel 中的Sheet2或Sheet3中,在不破坏Sheet1原有内容和格式的同时! 初来乍到,希望路过的高手展现出您的高招,感恩不尽了! 注:最好是源码,有待我进一步学习和研究!
xApp.Visible = true;
//得到WorkBook对象, 下面的是打开已有的文件
Excel.Workbook xBook = xApp.Workbooks._Open(@"D:\Tomin.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//指定要操作的Sheet,两种方式:
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1]; //先定你要操作的哪一个Sheet再按要求操作这个xSheet 就行了
Excel.Workbook workbookData;
Excel.Worksheet worksheetData; System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); workbookData = appExcel.Workbooks.Open(strTempFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); worksheetData = (Excel.Worksheet)workbookData.Sheets[1];//改成你要导入的sheet编号就行了
余下的导入Excel可以参考http://blog.csdn.net/lsj_zrp/archive/2009/07/29/4390944.aspx
xApp.Visible = true;
//得到WorkBook对象, 下面的是打开已有的文件
Excel.Workbook xBook = xApp.Workbooks._Open(@"D:\Tomin.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//指定要操作的Sheet,两种方式:
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1]; int rowCount = dataTable.Rows.Count;
int ColCount = dataTable.Columns.Count;
object[,] dataTableObject = new object[rowCount,ColCount]; int rowIndex = 0;
int colIndex = 0;
foreach (DataColumn col in dataTable.Columns) //将所得到的表的列名,赋值给单元格
{
dataTableObject[0, colIndex] = col.ColumnName;
colIndex++;
} foreach (DataRow row in dataTable.Rows) //同样方法处理数据
{
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
dataTableObject[rowIndex, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
rowIndex++;
}
Range range = xSheet.get_Range(xSheet.Cells[1, 1], xSheet.Cells[1 + rowCount, ColCount]); range.Value2 = dataTableObject; xBook.SaveAs(@"D:\Tomin.xls",
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value); //释放所有资源 释放COM