c#中格式化导出Excel数据
在项目开发过程中经常会遇到数据导出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原格式模版:
-------------------------------------------------------------------------------------------------------------------------------------------------
导出结果数据: