本文是列表导出不规则Excel文档(动态读取数据,行列合并)的示例。
首先在项目中添加“Microsoft.Office.Interop.Excel”。
创建一个工作表,如下所示:
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.Filter = "Excel文档(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
if (sfd.FileName != "")
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机器未安装Excel");
}
else
{
try
{
kkxj = "0.00";
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
worksheet.Cells[1, 1] = "薪資明細";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).RowHeight = 50; //行高
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).MergeCells = true; //合并单元格
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).RowHeight = 30; //行高
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).ColumnWidth = 12; //列宽
worksheet.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 2]).ColumnWidth = 15; //列宽
worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]).ColumnWidth = 12; //列宽
worksheet.get_Range(worksheet.Cells[1, 4], worksheet.Cells[1, 4]).ColumnWidth = 15; //列宽
worksheet.get_Range(worksheet.Cells[1, 5], worksheet.Cells[1, 5]).ColumnWidth = 12; //列宽
worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 6]).ColumnWidth = 15; //列宽
worksheet.get_Range(worksheet.Cells[1, 7], worksheet.Cells[1, 7]).ColumnWidth = 15; //列宽
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
//range.Interior.ColorIndex = 15;
range.Borders.LineStyle = 0;
range.Font.Bold = true;
//基本資料
if (dtEmp != null && dtEmp.Tables[0].Rows.Count > 0)
{
string empinfo = "員工編號:" + dtEmp.Tables[0].Rows[0]["EMP_NUMBER"].ToString() + " 部 門:" + dtEmp.Tables[0].Rows[0]["DEPT_NAME"].ToString() + " 員工姓名:" + dtEmp.Tables[0].Rows[0]["EMP_NAME"].ToString() + " 職 等:" + dtEmp.Tables[0].Rows[0]["DUTY_NO"].ToString();
Dept = dtEmp.Tables[0].Rows[0]["DEPT_NAME"].ToString();
Duty = dtEmp.Tables[0].Rows[0]["DUTY_NO"].ToString();
Option = dtEmp.Tables[0].Rows[0]["OPSITION_NAME"].ToString();
worksheet.Cells[2, 1] = empinfo;
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 7]).MergeCells = true; //合并单元格
//worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 7]).RowHeight = 40; //行高
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1];
range.Font.Size = 11;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
}
//薪資明細
if (dtSalaryinfo != null && dtSalaryinfo.Tables[0].Rows.Count > 0)
{
baseSalary = dtSalaryinfo.Tables[0].Rows[0]["BASIC_SALARY"].ToString();
aptitudeMoney = dtSalaryinfo.Tables[0].Rows[0]["APTITUDE_ALLOWANCE"].ToString();
dutyMoney = dtSalaryinfo.Tables[0].Rows[0]["DUTY_ALLOWANCE"].ToString();
fljt = dtSalaryinfo.Tables[0].Rows[0]["BOON_ALLOWANCE"].ToString();
overFee = dtSalaryinfo.Tables[0].Rows[0]["OVERTIME_FEE"].ToString();
gdPer = dtSalaryinfo.Tables[0].Rows[0]["SITE_FEE"].ToString();
Insurance = dtSalaryinfo.Tables[0].Rows[0]["INSURANCE_FEE"].ToString();
bxzsMoney = dtSalaryinfo.Tables[0].Rows[0]["CONVERTFEE"].ToString();
chargefee = dtSalaryinfo.Tables[0].Rows[0]["CHARGE_FEE"].ToString();
tax = dtSalaryinfo.Tables[0].Rows[0]["PERSON_TAX"].ToString();
ylje = dtSalaryinfo.Tables[0].Rows[0]["OUGHT_FEE"].ToString();
LeaveMoney = dtSalaryinfo.Tables[0].Rows[0]["LEAVE_FEE"].ToString();
kkxj = Convert.ToDecimal(kkxj) + Convert.ToDecimal(Insurance) + Convert.ToDecimal(LeaveMoney) + Convert.ToDecimal(tax) + "";
}
worksheet.Cells[3, 1] = "薪資加項";
worksheet.Cells[3, 2] = "金 額";
worksheet.Cells[3, 3] = "薪資減項";
worksheet.Cells[3, 4] = "金 額";
worksheet.Cells[3, 5] = "人事及薪資異動通知表";
worksheet.get_Range(worksheet.Cells[3, 5], worksheet.Cells[3, 7]).MergeCells = true; //合并单元格
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.Cells[4, 1] = "底 薪";
worksheet.Cells[4, 2] = baseSalary;
worksheet.Cells[4, 3] = "所 得 稅";
worksheet.Cells[4, 4] = tax;
worksheet.Cells[4, 5] = "項 目 別";
worksheet.Cells[4, 6] = "調 整 前";
worksheet.Cells[4, 7] = "調 整 后";
worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[4, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
if (!chargefee.Equals("0.00") && !chargefee.Equals("0"))
{
worksheet.Cells[5, 1] = "主管加給";
worksheet.Cells[5, 2] = chargefee;
}
worksheet.Cells[5, 3] = "公積金代扣";
worksheet.Cells[5, 4] = Insurance;
worksheet.Cells[5, 5] = "部 門";
if (!oldDept.Equals(""))
{
worksheet.Cells[5, 6] = oldDept;
worksheet.Cells[5, 7] = newDept;
}
worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.Cells[6, 1] = "福利津貼";
worksheet.Cells[6, 2] = fljt;
worksheet.Cells[6, 3] = "請假扣款";
worksheet.Cells[6, 4] = LeaveMoney;
worksheet.Cells[6, 5] = "職 稱";
if (!oldOption.Equals(""))
{
worksheet.Cells[6, 6] = oldOption;
worksheet.Cells[6, 7] = newOption;
}
worksheet.get_Range(worksheet.Cells[6, 1], worksheet.Cells[6, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.get_Range(worksheet.Cells[row + 1, 5], worksheet.Cells[row + 1, 7]).MergeCells = true; //合并单元格
worksheet.get_Range(worksheet.Cells[row + 1, 1], worksheet.Cells[row + 1, 4]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
MessageBox.Show("导出Excel完成!");
}
catch
{
MessageBox.Show("保存出错,请检查文件是否被正使用!", "系统信息");
}
finally
{
GC.Collect();
}
}
}
}
上面的代码只是一部分,有几个小细节总结如下:
1、worksheet.get_Range(worksheet.Cells[row + 3, 1], worksheet.Cells[row + 7, 1]).MergeCells = true; //合并多行多列,合并后的单元格只能作为一列来应用,不能再该列换行。
2、 worksheet.get_Range(worksheet.Cells[row + 3, 1], worksheet.Cells[row + 7, 7]).RowHeight = 50; //设置行高
3、range.WrapText = true; //换行
4、 worksheet.get_Range(worksheet.Cells[row + 7, 1], worksheet.Cells[row + 7, 7]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框
5、worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置对齐方式
6、worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[row + 7, 7]).Borders.LineStyle = 1; //设置边框