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

最新推荐文章于 2025-04-07 17:05:27 发布

寒冰的暖

最新推荐文章于 2025-04-07 17:05:27 发布

阅读量2.3k

点赞数

分类专栏： .NET学习之旅

本文链接：https://blog.youkuaiyun.com/hanbingdenuan/article/details/59040617

版权

.NET学习之旅专栏收录该内容

13 篇文章

订阅专栏

Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.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 = new Microsoft.Office.Interop.Excel.Application();
try
{
     DateTime startTime = startDateTime.Value;
     DateTime endTime = endDateTime.Value;
     string filename = "D:\\Mini充值对账明细" + startTime.ToString("yyyyMMdd") + "-" + endTime.ToString("yyyyMMdd") + ".xlsx";
     //创建
     Workbook workbook1 = excel1.Workbooks.Add(true);
     Worksheet worksheet1;
     //判断Excel是否存在
     if (File.Exists(filename))
     {
         //如果excel已经存在，就打开excel,在里面重新新建一选项卡
         Workbook xBook = 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 --------------不一致的情况

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

                        worksheet1.Cells[startRows + 3, 1] = "不一致的情况";
                        Microsoft.Office.Interop.Excel.Range range5 = worksheet1.Range[worksheet1.Cells[startRows + 3, 1], worksheet1.Cells[startRows + 3, 1]];
                        range5.Interior.ColorIndex = 8;
                        //项目合并单元格
                        Microsoft.Office.Interop.Excel.Range range5_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.Range range5_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.Range range5_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.Range range5_4 = worksheet1.Range[worksheet1.Cells[startRows + 4, 1], worksheet1.Cells[startRows + 5, 5]];
                        range5_4.Interior.ColorIndex = 15;
                        int startRange5 = startRows + 4;
                        startRows = startRows + 6;

                        int _rows1 = 0;
                        //支付公司总计
                        float paySum1 = 0;
                        float paySum2 = 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();
                                }
                                //显示支付公司的记录
                                DataSet dsNotSame2 = 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] = "合计";
                            DataSet dsSumNotSame1 = 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.Range range5_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.Range range5_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);
}

显示的效果图如下：