62,046
社区成员
发帖
与我相关
我的任务
分享
GC.Collect();
string excelTemplateDPath = templatepath; //EXCEL模板默认服务器物理存放路径
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
Excel.Workbooks workbooks; //工作簿集合
Excel.Sheets sheets; //SHEET页集合
try
{
#region
//启动excel进程并加载模板
//启动EXCEL进程
excel = new Excel.Application();
excel.EnableEvents = false;
excel.Visible = false;
excel.UserControl = true;
excel.DisplayAlerts = false;
//加载读取模板
workbooks = excel.Workbooks;
insertintoTest(excelTemplateDPath);
xBk = workbooks.Add(excelTemplateDPath);
sheets = xBk.Worksheets;
#endregion
#region output2
if (dt2.Rows.Count > 0)
{
xSt = (Excel._Worksheet)sheets["supply plan product view"];
int rowCount = 0;
int colCount = 0;
//保存数据
//表头
rowCount = Head2.Rows.Count;
colCount = Head2.Columns.Count;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = Head2.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 11;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Bold = true;
xSt.Columns.AutoFit();
//数据
rowCount = dt2.Rows.Count;
colCount = dt2.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = dt2.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//将excel的列宽设置为固定长度
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).ColumnWidth = 13;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
}
#endregion
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
// (Microsoft.Office.Interop.Excel.Worksheet)xBk.Close(false,null,null);
xBk.Close(false, null, null);
excel.DisplayAlerts = true; //注意一定要加上这句
excel.EnableEvents = true;
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
throw e;
}