110,476
社区成员
发帖
与我相关
我的任务
分享
public static void ExportNotPassProduct(DataTable table, string templetPath, string savePath)
{
File.Copy(templetPath, savePath, true);//templetPath、模板地址,savePath、保存路径
_Excel.Application app = new _Excel.ApplicationClass();
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
sheet.Columns.Font.Size = 11;
sheet.Columns.Font.Name = "Arial Unicode MS";
sheet.Columns.WrapText = true;
sheet.Rows.WrapText = true; //自动换行
sheet.Rows.EntireRow.AutoFit(); //行高根据内容自动调整
sheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
_Excel.Range rang = null;
for (int j = 0; j < table.Columns.Count; j++)
{
for (int row = 0; row < table.Rows.Count; row++)
{
sheet.Cells[row + 2, j + 1] = table.Rows[row][j].ToString();
}
}
sheet.get_Range("a1", "l" + (table.Rows.Count+1) + "").Borders.LineStyle = BorderStyle.FixedSingle;
sheet.get_Range("a1", "l" + (table.Rows.Count+1) + "").Font.Name = "Times New Roman";
System.Windows.Forms.Application.DoEvents();
sheet = null;
book.Save();
book.Close(sheet, savePath, System.Type.Missing);
app.Quit();
System.GC.Collect();
KillProcess(app);
}
/// <summary>
/// 导出Excel后,杀死Excel进程
/// </summary>
/// <param name="app"></param>
private static void KillProcess(_Excel.Application app)
{
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns></returns>
private DataTable GetData()
{
System.Data.DataTable dtExcel = new DataTable();
//添加dtExcel列
dtExcel.Columns.Add("名称", typeof(string));
dtExcel.Columns.Add("件数", typeof(int));
dtExcel.Columns.Add("数量", typeof(int));
dtExcel.Columns.Add("单价", typeof(decimal));
dtExcel.Columns.Add("金额", typeof(decimal));
for (int colNum = 0; colNum < dgvITList.Rows.Count - 1; colNum++)
{
DataRow dr = dtExcel.NewRow();
//将datagridview中某行某列的值添加到字段中
dr["名称"] = dgvITList.Rows[colNum].Cells["Column3"].Value;
if (dgvITList.Columns["Group1"].Visible == true && dgvITList.Rows[colNum].Cells["Group1"].Value != null)
{
dr["件数"] = ComLibrary.ToInt(dgvITList.Rows[colNum].Cells["Group1"].Value);
}
else
{
dr["件数"] = 0;
}
dr["数量"] = dgvITList.Rows[colNum].Cells["Number"].Value.ToString().Replace(",", "");
dr["单价"] = dgvITList.Rows[colNum].Cells["colRealPrice"].Value.ToString().Replace(",", "");
dr["金额"] = dgvITList.Rows[colNum].Cells["Column17"].Value.ToString().Replace(",", "");
dtExcel.Rows.Add(dr);//将dgvITList的一行添加到dtExcel的行中
}
return dtExcel;
}