C#导出带有格式的Excel（列宽，合…

Microsoft.Office.Interop.Excel.Application excel1 = newMicrosoft.Office.Interop.Excel.Application();

Worksheet worksheet1= (Worksheet)excel1.Worksheets[1];

Microsoft.Office.Interop.Excel.Range range =worksheet1.Range[worksheet1.Cells[4, 1], worksheet1.Cells[8,1]];//获取Excel多个单元格区域

1.合并单元格

range.Merge(0);//单元格合并动作

worksheet1.Cells[4, 1] = "项目";//为合并的单元格赋值

---------

2.给单元格加边框

//内外边框都显示
range.Borders.LineStyle =Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = 3;//边框线的粗细

//只显示外边框

range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());

---------

3.设置单元格的宽度，和自动调整列宽

excelRange.ColumnWidth = 15;//设置单元格的宽度

rang.EntireColumn.AutoFit();//自动调整列宽

---------

4.设置背景填充颜色

range.Interior.ColorIndex = 8;

---------

5.其他小设置

range.Font.Size = 15; //设置字体大小

range.Font.Underline=true; //设置字体是否有下划线

range.Font.Name="黑体"; 设置字体的种类

range.HorizontalAlignment=XlHAlign.xlHAlignCenter;//设置字体在单元格内的对其方式 range.ColumnWidth=15; //设置单元格的宽度

range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();//设置单元格的背景range.Borders.LineStyle=1; //设置单元格边框的粗细

range.EntireColumn.AutoFit();//自动调整列宽

range.HorizontalAlignment= xlCenter; //文本水平居中方式

range.VerticalAlignment= xlCenter //文本垂直居中方式

range.WrapText=true; //文本自动换行

range.Interior.ColorIndex=39; //填充颜色为淡紫色

range.Font.Color=clBlue; //字体颜色

 

------------------【华丽丽的分割线又来了，哈哈哈】--------------------------------

【代码示例和效果显示，吼吼】

Microsoft.Office.Interop.Excel.Application excel1 = newMicrosoft.Office.Interop.Excel.Application();
try
{
     DateTimestartTime = startDateTime.Value;
     DateTimeendTime = endDateTime.Value;
     stringfilename = "D:\\Mini充值对账明细" + startTime.ToString("yyyyMMdd") + "-"+ endTime.ToString("yyyyMMdd") + ".xlsx";
     //创建 
     Workbookworkbook1 = excel1.Workbooks.Add(true);
     Worksheetworksheet1;
     //判断Excel是否存在
     if(File.Exists(filename))
     {
         //如果excel已经存在，就打开excel,在里面重新新建一选项卡
         WorkbookxBook = excel1.Workbooks._Open(filename, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value);
          worksheet1=(Microsoft.Office.Interop.Excel.Worksheet)xBook.Worksheets.Add(Missing.Value,Missing.Value, 1, Missing.Value);

     }
     else
     {

         //不存在的话，直接选择第一个选项卡进行操作
         worksheet1= (Worksheet)excel1.Worksheets[1];

      }

worksheet1.Name = "汇总";//选项卡的名称

//-----------------------写入内容

#region --------------不一致的情况

                    //腾讯
                    DataSetdsNotSame1 = TencentMiniManager.GetV_TenMethod(startTime, endTime,"v_NotSameTen");
                    if(dsNotSame1.Tables[0].Rows.Count > 0)
                    {

                        worksheet1.Cells[startRows+ 3, 1] = "不一致的情况";
                        Microsoft.Office.Interop.Excel.Rangerange5 = worksheet1.Range[worksheet1.Cells[startRows + 3, 1],worksheet1.Cells[startRows + 3, 1]];
                        range5.Interior.ColorIndex= 8;
                        //项目合并单元格
                        Microsoft.Office.Interop.Excel.Rangerange5_1 = worksheet1.Range[worksheet1.Cells[startRows + 4, 1],worksheet1.Cells[startRows + 5, 1]];
                        range5_1.Merge(0);
                        worksheet1.Cells[startRows+ 4, 1] = "项目";

                        Microsoft.Office.Interop.Excel.Rangerange5_2 = worksheet1.Range[worksheet1.Cells[startRows + 4, 2],worksheet1.Cells[startRows + 4, 3]];
                        range5_2.Merge(0);
                        worksheet1.Cells[startRows+ 4, 2] = "腾讯";

                        Microsoft.Office.Interop.Excel.Rangerange5_3 = worksheet1.Range[worksheet1.Cells[startRows + 4, 4],worksheet1.Cells[startRows + 4, 5]];
                        range5_3.Merge(0);
                        worksheet1.Cells[startRows+ 4, 4] = "支付公司";
                        worksheet1.Cells[startRows+ 5, 2] = "笔数";
                        worksheet1.Cells[startRows+ 5, 3] = "面额";
                        worksheet1.Cells[startRows+ 5, 4] = "笔数";
                        worksheet1.Cells[startRows+ 5, 5] = "订单金额";
                        Microsoft.Office.Interop.Excel.Rangerange5_4 = worksheet1.Range[worksheet1.Cells[startRows + 4, 1],worksheet1.Cells[startRows + 5, 5]];
                        range5_4.Interior.ColorIndex= 15;
                        intstartRange5 = startRows + 4;
                        startRows= startRows + 6;

                        int_rows1 = 0;
                        //支付公司总计
                        floatpaySum1 = 0;
                        floatpaySum2 = 0;
                        if(dsNotSame1 != null && dsNotSame1.Tables[0].Rows.Count >0)
                        {
                            _rows1= dsNotSame1.Tables[0].Rows.Count;
                            for(int i = 0; i < _rows1; i++)
                            {
                                for(int j = 0; j < 3; j++)
                                {
                                    worksheet1.Cells[startRows+ i, 1 + j] = dsNotSame1.Tables[0].Rows[i][j].ToString();
                                }
                                //显示支付公司的记录
                                DataSetdsNotSame2 = PaymentComManager.GetV_PayMethod(startTime, endTime,"v_NotSameCom", dsNotSame1.Tables[0].Rows[i][0].ToString());
                                for(int m = 0; m < 2; m++)
                                {
                                    worksheet1.Cells[startRows+ i, 4 + m] = dsNotSame2.Tables[0].Rows[0][m].ToString();
                                }
                                paySum1= paySum1 +float.Parse(dsNotSame2.Tables[0].Rows[0][0].ToString());
                                paySum2= paySum2 +float.Parse(dsNotSame2.Tables[0].Rows[0][1].ToString());
                            }
                            //合计
                            worksheet1.Cells[startRows+ _rows1, 1] = "合计";
                            DataSetdsSumNotSame1 = TencentMiniManager.GetV_TenMethod(startTime,endTime, "v_SumNotSameTen");
                            if(dsSumNotSame1.Tables[0].Rows.Count > 0)
                            {
                                for(int k = 0; k < 2; k++)
                                {
                                    worksheet1.Cells[startRows+ _rows1, 2 + k] =dsSumNotSame1.Tables[0].Rows[0][k].ToString();
                                }
                                //显示支付公司的总计
                                worksheet1.Cells[startRows+ _rows1, 4] = Math.Round(paySum1,1);
                                worksheet1.Cells[startRows+ _rows1, 5] = Math.Round(paySum2,1);
                                Microsoft.Office.Interop.Excel.Rangerange5_5 = worksheet1.Range[worksheet1.Cells[startRows + _rows1,1], worksheet1.Cells[startRows + _rows1, 5]];
                                range5_5.Interior.ColorIndex= 6;
                            }
                        }
                        startRows= startRows + _rows1;
                        //边框显示
                        Microsoft.Office.Interop.Excel.Rangerange5_7 = worksheet1.Range[worksheet1.Cells[startRange5, 1],worksheet1.Cells[startRows, 5]];
                        //内外边框
                        range5_7.Borders.LineStyle= Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                        range5_7.Borders.Weight= 3;
                        range5_7.EntireColumn.AutoFit();//自动调整列宽

                    }
                    //总计

                    #endregion

 

//保存Excel不让弹出替换的对话框
excel1.DisplayAlerts=false;
worksheet1.SaveAs(filename, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value);
excel1.DisplayAlerts = true;
//清空内存
excel1.Quit();
excel1 = null;
//GC.Collect();
MessageBox.Show("导出汇总表成功！");
}
catch (Exception ex)
{

 excel1.Quit();
 MessageBox.Show("导出失败：" +ex.Message);
}

显示的效果图如下：

转自http://blog.sina.com.cn/s/blog_74f702e60101au55.html