Excel导出（二）

    本文是列表导出不规则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; //设置边框