在项目开发过程中经常会遇到数据导出Excel。如果只是导出数据就好办了。但往往用户会有各种格式要求。加粗、边框、合并单元格、汇总等功能。
以下的方法是基于Excel模版方式写入数据导出的功能。可以最大满足用户的格式需求。可以提前所相应的数据列设置好对齐方式，是否加粗、数据格式、保留小数位。对文本型的数据一定要设置成文本格式，不然像数字型的字符串就会出现类似于3.4E+17这格式。
注意Excel中下标是从1,1开始的。
合并单元格方法：
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Sheets[1]; Microsoft.Office.Interop.Excel.Range range = ws.get_Range(ws.Cells[x0, y0], ws.Cells[x1, y1]); range.Merge();
单元格增加边框：
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Sheets[1]; Microsoft.Office.Interop.Excel.Range range = ws.get_Range(ws.Cells[x0, y0], ws.Cells[x1, y1]); range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
DataTable dt = getdata();//数据来源
string strFileName = Application.StartupPath + "//file//ExportEmployeeSalary.xls";
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
saveFileDialog1.Filter = "Excel 97-2003(*.xls)|*.xls|Excel 2007-2010(*.xls)|*.xlsx|AllFiles|*.*";
saveFileDialog1.FileName = "工资" + fileName;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = excel.Application.Workbooks.Add(strFileName);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Sheets[1];
Microsoft.Office.Interop.Excel.Range range;
int rows = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
rows = i + 4;//从第行开始写数据
ws.Cells[rows, 1] = (i + 1).ToString();//序号
ws.Cells[rows, 2] = dt.Rows[i]["CompanyInfoName"].ToString();//单位名称
ws.Cells[rows, 3] = dt.Rows[i]["EmployeeRealName"].ToString();//姓名
ws.Cells[rows, 4] = dt.Rows[i]["IDCard"].ToString();//身份证号码
ws.Cells[rows, 5] = dt.Rows[i]["AccruedSalary"].ToString();//应发工资
ws.Cells[rows, 6] = dt.Rows[i]["DeductSocial"].ToString();//代扣社保
ws.Cells[rows, 7] = dt.Rows[i]["DeductAccumulation"].ToString();//代扣公积金
ws.Cells[rows, 8] = dt.Rows[i]["DeductTax"].ToString();//代扣个税
ws.Cells[rows, 9] = dt.Rows[i]["DeductOther"].ToString();//代扣其它
ws.Cells[rows, 10] = dt.Rows[i]["GrantBonus"].ToString();//代发奖励
ws.Cells[rows, 11] = dt.Rows[i]["GrantAchievements"].ToString();//代发绩效
ws.Cells[rows, 12] = dt.Rows[i]["GrantdOvertime"].ToString();//代发加班费
ws.Cells[rows, 13] = dt.Rows[i]["GrantOther"].ToString();//代发其他
ws.Cells[rows, 14] = dt.Rows[i]["RealSalary"].ToString();//实发工资
ws.Cells[rows, 15] = dt.Rows[i]["SalaryMonth"].ToString();//工资月份
ws.Cells[rows, 16] = dt.Rows[i]["ReMark"].ToString();//备注
}
range = ws.get_Range(ws.Cells[4, 1], ws.Cells[dt.Rows.Count + 3, 16]);
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.Visible = false;
excel.DisplayAlerts = false;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string localFilePath = saveFileDialog1.FileName.ToString();
xlBook.SaveCopyAs(localFilePath);
}
xlBook.Close();
excel.Quit();
excel = null;
GC.Collect();
Excel原格式模版：
-------------------------------------------------------------------------------------------------------------------------------------------------
导出结果数据：