C# EXCEL, EXCEL的各种设置

今天尝试了下用C#制作EXCEL表格，是项目的一个需求，为了提高审核人员的工作效率。

程序的实现很简单，只是里面的很多设置，在网上找了很久才找到。

现将代码贴下，或许能给有同样需求的人带来帮助。

 

 public void make()    //2012.8.6  XXX增加
        {
            Workbook wb;
            Worksheet ws;
            Range range;
            int numOfSheet = 0;
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            //增加workbook
            wb = excelApp.Workbooks.Add(true);
            //增加worksheet
            numOfSheet = 1;       //要制作表的张数
            excelApp.Worksheets.Add(Type.Missing, Type.Missing, numOfSheet, Type.Missing);

            for (int i = 1; i <= numOfSheet + 1; i++)
            {
                ws = (Worksheet)excelApp.Sheets.get_Item(i);
               
                //设置边框
                range = ws.get_Range("A2", "D21");
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                ws.Cells[1, 1] = "保单号：";
                range = (Range)ws.get_Range("A1", "D1");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.RowHeight = 45;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;  //设置字体在单元格内的对齐方式
                range.Font.Bold = true;

                for (int j = 2; j < 23; j++)  //设置2-22行的高度     ---------------
                {
                    range = (Range)ws.get_Range("A" + j.ToString(), "A" + j.ToString());
                    range.RowHeight = 30;
                    for (int k = 1; k < 5; k++)
                    {
                        range = (Range)ws.get_Range(ws.Cells[j, k], ws.Cells[j, k]);
                        range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        range.Font.Size = 17;
                        range.Font.Name = "宋体";
                        range.Font.Bold = true;                    

                        //设置小数点后保留两位
                        if (k == 3)
                        {
                            //"#.00";设置千元显示且四舍五入保留两位小数要求
                            //#,##0.00满足千分号隔开且小数点后两位保留 
                            range.NumberFormatLocal = "#,##0.00";   
                        }
                    }
                }
                //最外边的边框
                //range = (Range)ws.get_Range("A2", "D22");
                //range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());

                range = (Range)ws.get_Range("A2", "A2");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "索赔案例数";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.ColumnWidth = 24.63;
               

                range = (Range)ws.get_Range("B2", "B2");                   //计算索赔案例数
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.ColumnWidth = 18.50;
                range.Formula = "=SUM(B4:B12)";
                range.Calculate();
               

                range = (Range)ws.get_Range("C2", "C2");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "索赔案例金额";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.ColumnWidth = 20.38;
               

                range = (Range)ws.get_Range("D2", "D2");                //计算索赔案例金额
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.ColumnWidth = 18.50;
                range.Formula = "=sum(c4:c12)";
                range.Calculate();
               

                range = (Range)ws.get_Range("A3", "A3");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "案件涉及地区";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("B3", "B3");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "索赔案例数";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("C3", "C3");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "索赔案例金额";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("A13", "A13");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "赔案金额大写";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("b13", "d13"); //赔案金额大写的输入
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Formula = "=IF(ROUND(d2,2)=0,\"零元整\",IF(d2<0,\"负\",\"\")&IF(ABS(d2)>=1,TEXT(INT(ROUND(ABS(d2),2)),\"[dbnum2]\")&\"元\",\"\")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(d2,2),2),\"[dbnum2]0角0分;;整\"),\"零角\",IF(d2^2<1,,\"零\")),\"零分\",\"整\"))";
                range.Calculate();
               

                range = (Range)ws.get_Range("A14", "A14");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "服务案例数";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                //计算服务案例数               
                range = (Range)ws.get_Range("B14", "B14");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.Formula = "=B2"; //服务案例数 == 索赔案例数
                range.Calculate();
               

                range = (Range)ws.get_Range("C14", "C14");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "服务案例金额";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;          

                range = (Range)ws.get_Range("d14", "d14");                //计算服务案例金额
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.Formula = "=(B14 * 80)";   //---   服务案例金额=索赔案件例数之和乘以80
                range.Calculate();
               
                range = (Range)ws.get_Range("A15", "A15");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "服务案例金额大写";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;     

                range = (Range)ws.get_Range("b15", "d15"); //服务案例金额大写
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Formula = "=IF(ROUND(D14,2)=0,\"零元整\",IF(D14<0,\"负\",\"\")&IF(ABS(D14)>=1,TEXT(INT(ROUND(ABS(D14),2)),\"[dbnum2]\")&\"元\",\"\")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(D14,2),2),\"[dbnum2]0角0分;;整\"),\"零角\",IF(D14^2<1,,\"零\")),\"零分\",\"整\"))";
                range.Calculate();    

                range = (Range)ws.get_Range("A16", "A16");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "金额合计";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;

                range = (Range)ws.get_Range("b16", "d16");          //计算金额合计
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Formula = "=d2+d14";
                range.Calculate();    

                range = (Range)ws.get_Range("A17", "A17");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "金额合计大写";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true; 

                range = (Range)ws.get_Range("b17", "d17"); //金额合计大写
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Formula = "=IF(ROUND(b16,2)=0,\"零元整\",IF(b16<0,\"负\",\"\")&IF(ABS(b16)>=1,TEXT(INT(ROUND(ABS(b16),2)),\"[dbnum2]\")&\"元\",\"\")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(b16,2),2),\"[dbnum2]0角0分;;整\"),\"零角\",IF(b16^2<1,,\"零\")),\"零分\",\"整\"))";
                range.Calculate();
               

                range = (Range)ws.get_Range("A18", "A18");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "缮制人员综合报告";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("b18", "d18");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "属于保险责任";
               

                range = (Range)ws.get_Range("A19", "A19");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "理赔经理意见";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("b19", "d19");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
               

                range = (Range)ws.get_Range("A20", "A20");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "公司经理意见";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
            
                range = (Range)ws.get_Range("b20", "d20");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
               

                range = (Range)ws.get_Range("A21", "A21");
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Value2 = "备注";
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
               

                range = (Range)ws.get_Range("b21", "d21");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
               

                range = (Range)ws.get_Range("b22", "d22");
                range.Merge(0);
                range.Font.Size = 17;
                range.Font.Name = "宋体";
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                string str = "理算日期：";
                string year, month, day;
                year = DateTime.Today.Year.ToString();
                year = year + " 年 ";
                month = DateTime.Today.Month.ToString();
                month = month + " 月 ";
                day = DateTime.Today.Day.ToString();
                day = day + " 日";
                str = str + year + month + day;
                range.Value2 = str;
               

                excelApp.DisplayAlerts = false;
                excelApp.Visible = false;        //屏蔽弹出excel表
            }
            //保存这些sheet
            string path;

            path = this.textBox1.Text.ToString();

            MessageBox.Show(path);

            if (File.Exists(path)) //检查这个文件，如果文件存在，先删除它
            {
                File.Delete(path);
            }
            Object nothing = Type.Missing;
            Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault;
            wb.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);
            wb.Close(nothing, nothing, nothing); //关闭工作簿
            excelApp.Quit(); //退出程序
        }