/// <summary> /// 把数据插入到Excel表中 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="Tablename">sheet名或者表名</param> /// <returns></returns> public void InsertDataToExcel() { object missing = System.Reflection.Missing.Value; Excel.Application app = new Excel.ApplicationClass(); app.Application.Workbooks.Add(true); Excel.Workbook book = (Excel.Workbook)app.ActiveWorkbook; Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet; // 赋死值 for (int t = 1;t <= 5; t++) { for (int i = 1; i <= 20; i++) { Excel.Range rang1; rang1 = sheet.get_Range(sheet.Cells[t, i], sheet.Cells[t, i]); rang1.Merge(System.Reflection.Missing.Value); rang1.ColumnWidth = 15; rang1.Font.Size = 13; rang1.Font.Bold = true; rang1.Borders.ColorIndex = "1"; if (t == 3) { rang1.Font.ColorIndex = "3"; } else { rang1.Font.ColorIndex = "5"; } } } sheet.Cells[1, 1] = "航空公司"; sheet.Cells[1, 2] = "适用行程"; sheet.Cells[1, 3] = "出发机场"; sheet.Cells[1, 4] = "到达机场"; sheet.Cells[1, 5] = "排除出发机场"; sheet.Cells[1, 6] = "排除到达机场"; sheet.Cells[1, 7] = "适用航班"; sheet.Cells[1, 8] = "不适用航班"; sheet.Cells[1, 9] = "班期限制"; sheet.Cells[1, 10] = "适用舱位"; sheet.Cells[1, 11] = "返佣政策"; sheet.Cells[1, 12] = "乘机开始日期"; sheet.Cells[1, 13] = "乘机截止日期"; sheet.Cells[1, 14] = "出票开始日期"; sheet.Cells[1, 15] = "出票截止日期"; sheet.Cells[1, 16] = "适用票证"; sheet.Cells[1, 17] = "是否需要换PNR"; sheet.Cells[1, 18] = "是否自动出票"; sheet.Cells[1, 19] = "备注信息"; sheet.Cells[1, 20] = "政策发布"; sheet.Cells[2, 1] = "MU"; sheet.Cells[2, 2] = "单程"; sheet.Cells[2, 3] = "PEK/SHA"; sheet.Cells[2, 4] = "XXX"; sheet.Cells[2, 5] = "PEK"; sheet.Cells[2, 6] = "SZX"; sheet.Cells[2, 7] = "5167/5233"; sheet.Cells[2, 8] = "5166/5231"; sheet.Cells[2, 9] = "1|3|5|6"; sheet.Cells[2, 10] = "F/C/Y/B"; sheet.Cells[2, 11] = "7.5"; sheet.Cells[2, 12] = "3.5"; sheet.Cells[2, 13] = "2010-3-1"; sheet.Cells[2, 14] = "2010-3-21"; sheet.Cells[2, 15] = "2010-3-1"; sheet.Cells[2, 16] = "2010-3-31"; sheet.Cells[2, 17] = "BSP"; sheet.Cells[2, 18] = "是"; sheet.Cells[2, 19] = "是"; sheet.Cells[2, 20] = "1|2|3"; sheet.Cells[3, 1] = "1、必须填写 2、只限填写某一个航空公司代码"; sheet.Cells[3, 2] = "1、必须填写 2、1）单程；2）往返；3）单程及往返"; sheet.Cells[3, 3] = "1、必须填写 2、只限填写机场三字代码，可填写多个，多个时以英文的/隔开,并且支持XXX（全国）"; sheet.Cells[3, 4] = "1、必须填写 2、只限填写机场三字代码，可填写多个，多个时以英文的/隔开"; sheet.Cells[3, 5] = "1、必须填写 2、只限填写机场三字代码，可填写多个，多个时以英文的/隔开"; sheet.Cells[3, 6] = "1、非必填项 2、各个航班号中间用英文的“/”隔开。例：CA5166,CA5231 输入 5166/5231，如果无限制，则不填写"; sheet.Cells[3, 7] = "1、非必填项 2、各个航班号中间用英文的“/”隔开。例：CA5166,CA5231 输入 5166/5231，如果无限制，则不填写"; sheet.Cells[3, 8] = "1、非必填项 2、班期以“|”隔开，如果无限制，则不填写"; sheet.Cells[3, 9] = "1、必须填写 2、直接填写舱位代码，多个之间用“/”隔开"; sheet.Cells[3, 10] = "1、必须填写 2、2.5-50之间 3、直接填写政策值即可（政策值只支持小数点后1位，即不支持7.55），不支持添加％，即不支持7.5 %"; sheet.Cells[3, 11] = "1、必须填写 2、2.5-50之间 3、直接填写政策值即可（政策值只支持小数点后1位，即不支持7.55），不支持添加％，即不支持7.5 %"; sheet.Cells[3, 12] = "1、必须填写 2、格式为YY-MM-DD 3、开始日期小于等于截止日期"; sheet.Cells[3, 13] = "1、必须填写 2、格式为YY-MM-DD 3、开始日期小于等于截止日期"; sheet.Cells[3, 14] = "1、必须填写 2、格式为YY-MM-DD 3、开始日期小于等于截止日期"; sheet.Cells[3, 15] = "1、必须填写 2、格式为YY-MM-DD 3、开始日期小于等于截止日期"; sheet.Cells[3, 16] = "1、必须填写 2、BSP或者B2B"; sheet.Cells[3, 17] = "1、必须填写 2、BSP或者B2B"; sheet.Cells[3, 18] = "1、必须填写 2、填写“是”或者“否”"; sheet.Cells[3, 19] = "1、必须填写 2、填写“是”或者“否”"; sheet.Cells[3, 20] = "1、非必填项 2、填写下面“备注参考数据”的编号，中间以“|”隔开 3、若“备注参考数据”无数据，则说明未给当前供应商设置备注权限"; sheet.Cells[4, 1] = "政策备注参考数据"; sheet.Cells[5, 1] = "编号"; sheet.Cells[5, 2] = "备注"; //将DataTable赋值给excel List<PolicyRemark> list= BPolicyMangeBLL.GetSupplyPolicyRemark(agentCode).ToList(); for (int k = 0; k < list.Count; k++) { PolicyRemark pol = new PolicyRemark(); pol = list[k]; sheet.Cells[k + 6, 1] = pol.prid; sheet.Cells[k + 6, 2] = pol.prRemark; Excel.Range rang1; for (int t = 1; t <= 2; t++) { rang1 = sheet.get_Range(sheet.Cells[k + 6, t], sheet.Cells[k + 6, t]); rang1.Merge(System.Reflection.Missing.Value); rang1.ColumnWidth = 15; rang1.Borders.ColorIndex = "1"; } } //保存excel文件 string path = Request.MapPath(@"ExcelFile\model.xls"); book.SaveCopyAs(path); //关闭文件 book.Close(false, missing, missing); //退出excel app.Quit(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment; filename=model.xls;filetype=excel"); Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Response.WriteFile(path); Response.Flush(); }
/*
52
53 无色 = -4142, 自动 = -4105, 黑色
= 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49,
54 深蓝 =
11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 =
10,
55 青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 =
3, 浅橙色 = 45, 酸橙色 = 43,
56 海绿 = 50, 水绿色 = 42, 浅蓝 =
41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7,
57 金色 = 44,
黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 =
15,
58 玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 =
34, 淡蓝 = 37, 淡紫 = 39,
59 白色 =
2
60 */
这两行代码是将excel的输出格式为xls
string path = Request.MapPath(@"ExcelFile\model_" + agentCode + ".xls"); if (System.IO.File.Exists(path)) System.IO.File.Delete(path); Excel.XlFileFormat version = Excel.XlFileFormat.xlWorkbookNormal;//Excel 2007版本 book.SaveAs(path, version, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
