ASP.NET导出到EXCEL(控件式样)
来源:互联网 发布:广东省网络医院官网 编辑:程序博客网 时间:2024/06/08 02:34
DateTime dtime2 = dtime1.AddMonths(1);
string d2 = dtime2.ToString("yyyy年M月");
string d1 = dtime1.ToString("yyyy年M月");
string title1 = d1 + "份隐患治理及" + d2 + "份隐患排查情况统计表";
SeftyAnalyse sefty1 = new SeftyAnalyse();
IList<Model.yhTjModel> ilist_yhtj = sefty1.HiddenRiskStats(dtime1.Year.ToString(), dtime1.Month.ToString());
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 22]].MergeCells = true;//合并列
worksheet.Cells[1, 1] = "2017年8月份隐患治理及2017年9月份隐患排查情况统计表";//单元格赋值
worksheet.Range[worksheet.Cells[2,1],worksheet.Cells[4,1]].MergeCells = true;
worksheet.Cells[2, 1] = "单位";
worksheet.Range[worksheet.Cells[2,2],worksheet.Cells[2,9]].MergeCells = true;
worksheet.Cells[2, 2] = "2017年8月隐患治理";
worksheet.Range[worksheet.Cells[2, 10], worksheet.Cells[2, 22]].MergeCells = true;
worksheet.Cells[2, 10] = "2017年9月隐患排查";
worksheet.Range[worksheet.Cells[3, 2], worksheet.Cells[3, 3]].MergeCells = true;
worksheet.Cells[3, 2] = "A级";
worksheet.Range[worksheet.Cells[3, 4], worksheet.Cells[3, 5]].MergeCells = true;
worksheet.Cells[3, 4] = "B级";
worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 7]].MergeCells = true;
worksheet.Cells[3, 6] = "C级";
worksheet.Range[worksheet.Cells[3, 8], worksheet.Cells[3, 9]].MergeCells = true;
worksheet.Cells[3, 8] = "完成合计";
worksheet.Range[worksheet.Cells[3, 10], worksheet.Cells[3, 11]].MergeCells = true;
worksheet.Cells[3, 10] = "顶板";
worksheet.Range[worksheet.Cells[3, 12], worksheet.Cells[3, 13]].MergeCells = true;
worksheet.Cells[3, 12] = "防治水";
worksheet.Range[worksheet.Cells[3, 14], worksheet.Cells[3, 15]].MergeCells = true;
worksheet.Cells[3, 14] = "通防";
worksheet.Cells[3, 16] = "机电运输";
worksheet.Cells[3, 17] = "综合";
worksheet.Cells[3, 18] = "其他";
worksheet.Range[worksheet.Cells[3, 19], worksheet.Cells[3, 21]].MergeCells = true;
worksheet.Cells[3, 19] = "合计";
worksheet.Cells[3, 22] = "完成";
worksheet.Range[worksheet.Cells[3, 22], worksheet.Cells[4, 22]].MergeCells = true;
worksheet.Cells[4, 2] = "排查";
worksheet.Cells[4, 3] = "治理完成";
worksheet.Cells[4, 4] = "排查";
worksheet.Cells[4, 5] = "治理完成";
worksheet.Cells[4, 6] = "排查";
worksheet.Cells[4, 7] = "治理完成";
worksheet.Cells[4, 8] = "排查";
worksheet.Cells[4, 9] = "治理";
worksheet.Cells[4, 10] = "A级";
worksheet.Cells[4, 11] = "B级";
worksheet.Cells[4, 12] = "A级";
worksheet.Cells[4, 13] = "B级";
worksheet.Cells[4, 14] = "A级";
worksheet.Cells[4, 15] = "B级";
worksheet.Cells[4, 16] = "B级";
worksheet.Cells[4, 17] = "B级";
worksheet.Cells[4, 18] = "B级";
worksheet.Cells[4, 19] = "A级";
worksheet.Cells[4, 20] = "B级";
worksheet.Cells[4, 21] = "C级";
int z = 5;
int z1 = ilist_yhtj.Count;
foreach (var item in ilist_yhtj)
{
worksheet.Cells[z, 1] = item.KjName;
worksheet.Cells[z, 2] = item.A_PC;
worksheet.Cells[z, 3] = item.A_WC;
worksheet.Cells[z, 4] = item.B_PC;
worksheet.Cells[z, 5] = item.B_WC;
worksheet.Cells[z, 6] = item.C_PC;
worksheet.Cells[z, 7] = item.C_WC;
worksheet.Cells[z, 8] = item.PC;
worksheet.Cells[z, 9] = item.WC;
worksheet.Cells[z, 10] = item.A_DB;
worksheet.Cells[z, 11] = item.B_DB;
worksheet.Cells[z, 12] = item.A_Fzs;
worksheet.Cells[z, 13] = item.B_Fzs;
worksheet.Cells[z, 14] = item.A_TF;
worksheet.Cells[z, 15] = item.B_TF;
worksheet.Cells[z, 16] = item.B_JD;
worksheet.Cells[z, 17] = item.B_ZH;
worksheet.Cells[z, 18] = item.B_QT;
worksheet.Cells[z, 19] = item.A_HJ;
worksheet.Cells[z, 20] = item.B_HJ;
worksheet.Cells[z, 21] = item.C_HJ;
worksheet.Cells[z, 22] =(item.A_HJ+item.B_HJ+item.C_HJ).ToString();
z++;
}
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[z1+4, 22]];
range.Borders.LineStyle = XlLineStyle.xlContinuous;//区域内的单元格带边框线
range.HorizontalAlignment = XlVAlign.xlVAlignCenter;//水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
range.EntireColumn.AutoFit();//自动列宽
xlApp.Visible = true;
workbook.Saved = true;
DateTime dtime3 = DateTime.Now;
string f1 = dtime3.ToString("yyyyMMddhhmmssffffff");
f2 = "/Upload/YDYH_temp/" + f1 + ".xlsx";
workbook.SaveCopyAs(Server.MapPath("/Upload/YDYH_temp/"+f1+".xlsx"));
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
xlApp.Quit();
xlApp = null;
return f2;
其他页面的参考:下面的代码有部分错误
public void Exportdatagridviewtoexcel(string Textname)
{
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.DefaultExt = "xlsx";
savedialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
savedialog.FilterIndex = 0;
savedialog.RestoreDirectory = true;
savedialog.Title = "导出数据到excel表格";
savedialog.ShowDialog();
if (savedialog.FileName.IndexOf(":") < 0) return; //被点了取消
//Microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
if (xlapp == null)
{
MessageBox.Show("可能您的机子未安装excel,无法创建excel对象!", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
string ADD = "盘点录入明细表 时间:" + DateTime.Now.ToShortDateString();
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).MergeCells = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).Font.Bold = true;//
worksheet.Cells[1, 1] = " " + Textname;
worksheet.Cells[2, 1] = "" + "使用科室";
worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[2, 3]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[2, 7]).MergeCells = true;
//worksheet.Cells[2, 5] = "" + "年月日";
worksheet.Cells[3, 1] = "患者姓名";
worksheet.Cells[3, 2] = " ";
worksheet.Cells[3, 3] = "性别";
worksheet.Cells[3, 4] = " ";
worksheet.Cells[3, 5] = "年龄";
worksheet.Cells[4, 1] = "住院号";
worksheet.Cells[4, 2] = " ";
worksheet.Cells[4, 3] = "床位号";
worksheet.Cells[4, 4] = " ";
worksheet.Cells[4, 5] = "手术名称";
worksheet.Cells[5, 1] = "使用部位";
worksheet.Cells[5, 2] = " ";
worksheet.Cells[5, 3] = "手术日期";
worksheet.Cells[5, 4] = " ";
worksheet.Cells[5, 5] = "医保类型";
worksheet.get_Range(worksheet.Cells[3, 6], worksheet.Cells[3, 7]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[4, 6], worksheet.Cells[4, 7]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[5, 6], worksheet.Cells[5, 7]).MergeCells = true;
worksheet.Cells[5, 6] = " ";
worksheet.Cells[4, 6] = " ";
worksheet.Cells[3, 6] = " ";
worksheet.Cells[6, 1] = "生产厂家";
worksheet.get_Range(worksheet.Cells[6, 2], worksheet.Cells[6, 3]).MergeCells = true;
worksheet.Cells[6, 4] = "注册证号";
worksheet.get_Range(worksheet.Cells[6, 5], worksheet.Cells[6, 7]).MergeCells = true;
worksheet.Cells[7, 1] = "生产日期";
worksheet.Cells[7, 2] = " ";
worksheet.Cells[7, 3] = "灭菌日期";
worksheet.Cells[7, 4] = " ";
worksheet.Cells[7, 5] = "有效日期";
worksheet.get_Range(worksheet.Cells[7, 6], worksheet.Cells[7, 7]).MergeCells = true;
worksheet.Cells[7, 6] = " ";
worksheet.Cells[8, 1] = "供货单位";
worksheet.get_Range(worksheet.Cells[8, 2], worksheet.Cells[8, 3]).MergeCells = true;
worksheet.Cells[8, 4] = "供货商签字";
worksheet.Cells[8, 5] = " ";
worksheet.Cells[8, 6] = "联系电话";
worksheet.Cells[8, 7] = " ";
worksheet.Cells[9, 1] = "产品名称";
worksheet.Cells[9, 2] = "规格型号";
worksheet.Cells[9, 3] = "产品批号";
worksheet.Cells[9, 4] = "单价";
worksheet.Cells[9, 5] = "数量";
worksheet.Cells[9, 6] = "单位";
worksheet.Cells[9, 7] = "金额 ";
worksheet.Cells[10, 1] = " ";
worksheet.Cells[10, 2] = " ";
worksheet.Cells[10, 3] = " ";
worksheet.Cells[10, 4] = " ";
worksheet.Cells[10, 5] = " ";
worksheet.Cells[10, 6] = " ";
worksheet.Cells[10, 7] = " ";
worksheet.Cells[11, 1] = " ";
worksheet.Cells[11, 2] = " ";
worksheet.Cells[11, 3] = " ";
worksheet.Cells[11, 4] = " ";
worksheet.Cells[11, 5] = " ";
worksheet.Cells[11, 6] = " ";
worksheet.Cells[11, 7] = " ";
worksheet.Cells[12, 1] = " ";
worksheet.Cells[12, 2] = " ";
worksheet.Cells[12, 3] = " ";
worksheet.Cells[12, 4] = " ";
worksheet.Cells[12, 5] = " ";
worksheet.Cells[12, 6] = " ";
worksheet.Cells[12, 7] = " ";
worksheet.Cells[13, 1] = " ";
worksheet.Cells[13, 2] = " ";
worksheet.Cells[13, 3] = " ";
worksheet.Cells[13, 4] = " ";
worksheet.Cells[13, 5] = " ";
worksheet.Cells[13, 6] = " ";
worksheet.Cells[13, 7] = " ";
worksheet.Cells[14, 1] = "高值耗材使用医生签字";
worksheet.Cells[14, 5] = "手术护士";
worksheet.Cells[14, 6] = " ";
worksheet.Cells[14, 7] = " ";
worksheet.get_Range(worksheet.Cells[14, 6], worksheet.Cells[14, 7]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[14, 1], worksheet.Cells[14, 3]).MergeCells = true;
worksheet.Cells[15, 1] = "收费人员(记账人员)签字";
worksheet.Cells[15, 5] = "收费价格";
worksheet.Cells[15, 6] = " ";
worksheet.Cells[15, 7] = " ";
worksheet.get_Range(worksheet.Cells[15, 6], worksheet.Cells[15, 7]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[15, 1], worksheet.Cells[15, 3]).MergeCells = true;
worksheet.Cells[16, 1] = "科室负责人签字";
worksheet.Cells[16, 3] = " ";
worksheet.Cells[16, 4] = " ";
worksheet.Cells[16, 5] = "设备采购签字";
worksheet.Cells[16, 6] = " ";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).Text = ADD;
worksheet.get_Range(worksheet.Cells[16, 1], worksheet.Cells[16, 2]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[16, 5], worksheet.Cells[16, 6]).MergeCells = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 8]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
worksheet.Cells[17, 1] = "高值耗材唯一性标示";
//worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[17, 1]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[27, 1]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[17, 2], worksheet.Cells[27, 7]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[27, 1]).WrapText = true;//
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[27, 7]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
if (savedialog.FileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(savedialog.FileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!..." + ex.Message, "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//GC.Collect();//强行销毁
MessageBox.Show("数据导出成功! ", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
按 Ctrl+C 复制代码
2.单元格样式设置
复制代码
Excel.Range r =mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
r.NumberFormat="@"; //设置单元格格式为文本类型,文本类型可设置上下标
r.NumberForma="0.00_ "//设置单元格格式为数值类型,小数点后2位
r.NumberForma="¥#,##0.00;¥-#,##0.00"//设设置单元格格式为货币类型,小数点后2位
r.NumberForma=_"_ ¥*#,##0.00_;_ ¥*-#,##0.00_ ;_ ¥*""-""??_;_ @_ "//置单元格格式为会计专用类型,小数点后2位
r.NumberForma="yyyy-m-d"//设置单元格格式为日期类型
r.NumberForma="[$-F400]h:mm:ss AM/PM"//设置单元格格式为时间类型
r.NumberForma="0.00%"//设置单元格格式为百分比类型,小数点后2位
r.NumberForma="# ?/?"设置单元格格式为分数类型,分母为一位数
r.NumberForma="0.00E+00"//设置单元格格式为科学技术类型,小数位数为2
r.NumberForma="000000"//设置单元格格式为特殊类型
B、上下标格式的控制;
mySheet.get_Range(mySheet.Cells[i+3, DT.Columns.Count - 3],mySheet.Cells[i + 3, DT.Columns.Count -3]).get_Characters(a.Length+ 1,b.Length).Font.Subscript =true;//控制输出样式为下标
mySheet.get_Range(mySheet.Cells[i+3, DT.Columns.Count - 3],mySheet.Cells[i + 3, DT.Columns.Count-3]).get_Characters(a.Length + b.Length+ 1, c.Length).Font.Superscript =true; //控制输出样式为上标
C、单元格水平垂直对齐方式;
//单元格水平,垂直居中
r.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;
r.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;
上面代码中,枚举XLHAlign的值还有:
// 右对齐
xlHAlignRight,
// 左对齐.
xlHAlignLeft,
// 两端对齐.
xlHAlignJustify,
// 分散对齐(缩进)
xlHAlignDistributed,
// 居中对齐
xlHAlignCenter,
// 依照数据类型对齐,常规
xlHAlignGeneral,
// 填充
xlHAlignFill,
// 跨列对齐.
xlHAlignCenterAcrossSelection=7,
枚举XLVAlign的值还有:
// 靠上对齐
xlVAlignTop,
//两端对齐.
xlVAlignJustify=-4130,
//分散对齐.
xlVAlignDistributed,
//居中对齐.
xlVAlignCenter,
//靠下对齐.
xlVAlignBottom=-4107,
D、 单元格边框设置;
//设置边框
Excel.Range r =mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
r.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
枚举XlLineStyle中还有下面线形:
//没边框线
xlLineStyleNone,
//双线.
xlDouble,
//点状线.
xlDot,
//虚线.
xlDash,
//连续线.
xlContinuous,
//点线交互型
xlDashDot,
//两点一线型
xlDashDotDot,
//斜线.
xlSlantDashDot,
E、单元格合并
用get_Range方法获取要合并的单元格,再设置MergeCells属性的值进行合并。
代码:
//合并单元格
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,4]).MergeCells =true;
F、字体设置
先用get_Range方法选中要设置字体的某个单元格或者或者直接用get_Characters方法直接选中要设置的字符进行设置;
代码:
//加粗字体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Bold =true;
//设置字体大小
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Size=16;
//设置字体的颜色
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.ColorIndex = 3;
//设置字体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Name ="隶书";
//设置成斜体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Italic =true;
//设置下划线
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Underline =true;
G、冻结行列
用get_Range方法获取单元格,再设置其Freezepanes属性为true,就把该单元格右上方的行和列都冻结了,取消冻结将其值设为false即可。
//冻结行列
myExcel.get_Range(myExcel.Cells[3,1],myExcel.Cells[3,1]).Activate();
myExcel.ActiveWindow.FreezePanes=true;
H、 公式输入栏的隐藏
//设置是否显示Excel公式输入栏,默认为true
myExcel.DisplayFormulaBar=false;
I、 列标题与行标题的隐藏
//设置是否显示行和列的标题,默认为true
myExcel.ActiveWindow.DisplayHeadings=false;
J、网格的隐藏
//设置是否显示网格,默认为true
myExcel.ActiveWindow.DisplayGridlines=false;
K、 水平、垂直滚动条的隐藏
//设置是否显示水平滚动条
myExcel.ActiveWindow.DisplayHorizontalScrollBar=false;
//设置是否显示垂直滚动条
myExcel.ActiveWindow.DisplayVerticalScrollBar=false;
L、页的隐藏Sheet
//设置是否显示Sheet页
myExcel.ActiveWindow.DisplayWorkbookTabs=false;
M、 电子表格外观控制:
Sheet.DisplayToolbar= false;//是否显示工具栏默认值为 true
Sheet.DisplayOfficeLogo= false;// 取消显示Office图标
Sheet.DisplayColumnHeadings= false;// 是否显示列标题,默认是true
Sheet.DisplayRowHeadings= false;// 是否显示行标题,默认是true
Sheet.DisplayHorizontalScrollBar= false;// 是否显示水平滚动条,默认为true
Sheet.DisplayVerticalScrollBar= false;// 是否显示垂直滚动条,默认为true
Sheet.DisplayGridlines= false;// 电子表格是否显示网格,默认为true
for (int i = 1; i < DT.Columns.Count- 2; i += 2)
{
Sheet.get_Range(Sheet.Rows.Cells[i,1],Sheet.Rows.Cells[i, DT.Columns.Count - 3]).Interior.set_ColorIndex(2); //设置行的颜色
}
worksheet.Cells[1, 1]= "Excel单元格赋值";//Excel单元格赋值
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.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());//给单元格加边框
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置单元格上边框为无边框
range.EntireColumn.AutoFit(); //自动调整列宽
Range.HorizontalAlignment= xlCenter; // 文本水平居中方式
Range.VerticalAlignment= xlCenter //文本垂直居中方式
Range.WrapText=true; //文本自动换行
Range.Interior.ColorIndex=39; //填充颜色为淡紫色
Range.Font.Color=clBlue; //字体颜色
xlsApp.DisplayAlerts=false; //保存Excel的时候,不弹出是否保存的窗口直接进行保存
向Excel中填充数据,并保存为临时文件
Excel.ApplicationmyExcel =newExcel.Application();
//打开模板文件
myExcel.Application.Workbooks.Open(mode.FullName,missing,missing,missing, missing, missing, missing, missing, missing,missing, missing,missing,missing, missing, missing);
//选中有数据的Cells
Excel.WorkbookmyBook = myExcel.Workbooks[1];
Excel.WorksheetmySheet = (Excel.Worksheet)myBook.Worksheets[1];
Excel.Ranger = mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
r.Select();
//不单独显示Excel,最后在IE中显示
myExcel.Visible=false;
//第一行为报表的标题
myExcel.Cells[1,1]= "用模板导出的报表";
//逐行写入数据,数组中第一行为报表的列标题
for (int i = 0; i <DT.Columns.Count - 3; i++)
{
myExcel.Cells[2,1+ i] =DT.Columns[i].Caption; ;
}
//在当前目录下指定一个临时文件
string FileName =Server.MapPath("~")+"""Temp.xls";
if (File.Exists(FileName))
{
File.Delete(FileName);
}
myExcel.Save(FileName);
mySheet.Cells.Clear();
//设置不出现保存提示框
myBook.Saved=true;
myExcel.Application.Workbooks.Close();
复制代码
- ASP.NET导出到EXCEL(控件式样)
- asp.net控件导出Excel
- ASP.NET 导出Panel、GridView控件到Excel或Word
- asp.net 导出到Excel
- asp.net 导出到excel
- asp.net 导出GridView、其它控件到Excel(防止中文乱码)
- asp.net 导出 excel (含控件)
- ASP.Net 用Infragstics控件导出Excel
- ASP.NET导出数据到Excel
- ASP.NET导出数据到Excel!
- ASP.NET导出数据到Excel
- ASP.NET导出数据到Excel
- ASP.NET导出数据到Excel
- ASP.NET导出数据到Excel
- ASP.NET导出数据到Excel
- Asp.net GridView 导出到Excel
- ASP.NET 数据导出到Excel
- ASP.NET 数据导出到Excel
- qt creator中添加自定义的类后需要执行qmake
- ubuntn 常用命令和快捷键汇总
- 2.1线性表综述
- 区块链解读3-密码学
- JAVA线程中同步锁(synchronized)的使用
- ASP.NET导出到EXCEL(控件式样)
- 士兵杀敌(一) 【nyoj-108】【线段树】
- eCognition基础操作
- HBase 中RowKey的设计原则
- 汉诺塔系列2
- 百度之星初赛-(A) A小C的倍数问题
- JZOJ1260. 牛棚的安排(2017.8B组)
- 【2017/8/12】Linux7 keepalived+nginx配置
- allocator空间分配接口类实现Vector