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);
}
显示的效果图如下:
![C#导出带有格式的Excel(列宽,合并单元格,显示边框线,加背景颜色等) C#导出带有格式的Excel(列宽,合并单元格,显示边框线,加背景颜色等)](http://blog.oujinpei.info/wp-content/uploads/auto_save_image/2013/09/114937RN4.jpg)
------------------【华丽丽的分割线又来了,哈哈哈】--------------------------------
如果遇到“检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败。 ”错误:
1.在"开始"->"运行"中输入dcomcnfg.exe启动"组件服务"
2.依次双击"组件服务"->"计算机"->"我的电脑"->"DCOM配置"
3.在"DCOM配置"中找到"Microsoft Excel 应用程序",在它上面点击右键,然后点击"属性",弹出"Microsoft Excel 应用程序属性"对话框
4.点击"标识"标签,选择"交互式用户"
5.点击"安全"标签,在"启动和激活权限"上点击"自定义",然后点击对应的"编辑"按钮,在弹出的"安全性"对话框中填加一个"NETWORK SERVICE"用户(注意要选择本计算机名),并给它赋予"本地启动"和"本地激活"权限
6.依然是"安全"标签,在"访问权限"上点击"自定义",然后点击"编辑",在弹出的"安全性"对话框中也填加一个"NETWORK SERVICE"用户,然后赋予"本地访问"权限.
如果是64位版本的机器上,因为打开DCOMCNFG之后,找不到项“Microsoft Excel 应用程序”,因为DCOMcnfg是32配置,在64位系统下能完全被支持,于是查阅到了打开32位的DCOMCNFG的方式:
1.运行:mmc -32
2.打开“文件”-“添加或删除管理单元”-选择组件服务-点击添加-确定。
3.可以在DCOM组件中找到Microsoft Excel Application了。
于是用最上面的方式配置完成,跑网站,一样出错。
再检查,还有内容没有完成设置。
检查IIS7的设置,最后确定还需要设置应用程序池的标识,把此网站的AppPool的标识要设置为LocalSystem或者NetworkService,问题解决。