/// <summary> /// 付款情况汇总表导出EXCEL /// </summary> /// <param name="name">表头</param> /// <param name="dw">分公司名</param> /// <param name="date">年月</param> private void EXCEL_BB_SK_HZ(string name, string dw, string date) { //MessageBox.Show("Test"); Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A6").Merge(false); excel.get_Range("B4", "B6").Merge(false); excel.get_Range("S4", "S6").Merge(false); excel.get_Range("C4", "F4").Merge(false); excel.get_Range("G4", "J4").Merge(false); excel.get_Range("K4", "L4").Merge(false); excel.get_Range("M4", "P4").Merge(false); excel.get_Range("Q4", "R4").Merge(false); excel.get_Range("D5", "E5").Merge(false); excel.get_Range("H5", "I5").Merge(false); excel.get_Range("N5", "O5").Merge(false); //CFGJKLMPR excel.get_Range("C5", "C6").Merge(false); excel.get_Range("F5", "F6").Merge(false); excel.get_Range("G5", "G6").Merge(false); excel.get_Range("J5", "J6").Merge(false); excel.get_Range("K5", "K6").Merge(false); excel.get_Range("L5", "L6").Merge(false); excel.get_Range("M5", "M6").Merge(false); excel.get_Range("P5", "P6").Merge(false); excel.get_Range("Q5", "Q6").Merge(false); excel.get_Range("R5", "R6").Merge(false); excel.Cells[1, 1] = name; excel.Cells[3, 1] = dw + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "S4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "S6").Font.Bold = true; excel.Cells[4, "A"] = "部门"; excel.Cells[4, "B"] = "客户类型"; excel.Cells[4, "C"] = "签定合同总额"; excel.Cells[4, "G"] = "回款金额"; excel.Cells[4, "K"] = "尚欠金额"; excel.Cells[4, "M"] = "已开发票金额"; excel.Cells[4, "Q"] = "未开发票金额"; excel.Cells[4, "S"] = "备注"; excel.Cells[5, "D"] = "本年"; excel.Cells[5, "H"] = "本年"; excel.Cells[5, "N"] = "本年"; excel.Cells[5, "C"] = "上年"; excel.Cells[5, "G"] = "上年"; excel.Cells[5, "M"] = "上年"; excel.Cells[5, "F"] = "总累计"; excel.Cells[5, "J"] = "总累计"; excel.Cells[5, "P"] = "总累计"; excel.Cells[5, "K"] = "金额"; excel.Cells[5, "Q"] = "金额"; excel.Cells[5, "L"] = "比例"; excel.Cells[5, "R"] = "比例"; excel.Cells[6, "D"] = "本月"; excel.Cells[6, "H"] = "本月"; excel.Cells[6, "N"] = "本月"; excel.Cells[6, "E"] = "本年"; excel.Cells[6, "I"] = "本年"; excel.Cells[6, "O"] = "本年"; excel.get_Range("C7", excel.Cells[dataGridView1.Rows.Count + 6, "R"]).NumberFormat = "#,##0.00"; excel.get_Range("R7", excel.Cells[dataGridView1.Rows.Count + 6, "R"]).NumberFormat = "0%"; excel.get_Range("L7", excel.Cells[dataGridView1.Rows.Count + 6, "L"]).NumberFormat = "0%"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString(); } } } for (int i = 0; i < this.dataGridView1.Rows.Count; i++) { //MessageBox.Show(excel.Cells[i + 7, "A"].ToString()); if (excel.get_Range("A" + (i + 7), "A" + (i + 7)).Value.ToString() == "") { excel.Cells[i + 7, "A"] = null; excel.get_Range(excel.Cells[i + 7, "A"], excel.Cells[i + 6, "A"]).Merge(false); } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$6"; }
/// <summary> /// 付款情况明细表导出EXCEL /// </summary> /// <param name="name">表头</param> /// <param name="dw">分公司名</param> /// <param name="date">年月</param> private void EXCEL_FKMX(string name, string dw, string date) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A6").Merge(false); excel.get_Range("B4", "B6").Merge(false); excel.get_Range("C4", "C6").Merge(false); excel.get_Range("D4", "D6").Merge(false); excel.get_Range("E4", "E6").Merge(false); excel.get_Range("F4", "I4").Merge(false); excel.get_Range("J4", "K4").Merge(false); excel.get_Range("L4", "O4").Merge(false); excel.get_Range("P4", "Q4").Merge(false); excel.get_Range("R4", "R6").Merge(false); excel.get_Range("S4", "S6").Merge(false); excel.get_Range("G5", "H5").Merge(false); excel.get_Range("M5", "N5").Merge(false); excel.get_Range("F5", "F6").Merge(false); excel.get_Range("I5", "I6").Merge(false); excel.get_Range("J5", "J6").Merge(false); excel.get_Range("K5", "K6").Merge(false); excel.get_Range("P5", "P6").Merge(false); excel.get_Range("L5", "L6").Merge(false); excel.get_Range("O5", "O6").Merge(false); excel.get_Range("P5", "P6").Merge(false); excel.get_Range("Q5", "Q6").Merge(false); excel.Cells[1, 1] = name; excel.Cells[3, 1] = dw + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "S4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "S6").Font.Bold = true; excel.Cells[4, "A"] = "序号"; excel.Cells[4, "B"] = "内外"; excel.Cells[4, "C"] = "合同号"; excel.Cells[4, "D"] = "客户"; excel.Cells[4, "E"] = "结算金额"; excel.Cells[4, "F"] = "已付货款"; excel.Cells[4, "J"] = "未付货款"; excel.Cells[4, "L"] = "已收发票金额"; excel.Cells[4, "P"] = "未收发票金额"; excel.Cells[4, "R"] = "财务余额"; excel.Cells[4, "S"] = "备注"; excel.Cells[5, "G"] = "本年"; excel.Cells[5, "M"] = "本年"; excel.Cells[6, "M"] = "本月"; excel.Cells[6, "G"] = "本月"; excel.Cells[5, "F"] = "上年"; excel.Cells[5, "L"] = "上年"; excel.Cells[5, "I"] = "总累计"; excel.Cells[5, "O"] = "总累计"; excel.Cells[5, "J"] = "金额"; excel.Cells[5, "P"] = "金额"; excel.Cells[5, "K"] = "比例"; excel.Cells[5, "Q"] = "比例"; excel.Cells[6, "G"] = "本月"; excel.Cells[6, "M"] = "本月"; excel.Cells[6, "H"] = "本年"; excel.Cells[6, "N"] = "本年"; excel.get_Range("E7", excel.Cells[dataGridView1.Rows.Count + 6, "R"]).NumberFormat = "#,##0.00"; excel.get_Range("k7", excel.Cells[dataGridView1.Rows.Count + 6, "k"]).NumberFormat = "0%"; excel.get_Range("q7", excel.Cells[dataGridView1.Rows.Count + 6, "q"]).NumberFormat = "0%"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$6"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
private void butexcel_Click(object sender, EventArgs e) { try { #region 简单打印 this.butexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string title = ":"; if (rdo1.Checked == true) { title = title + "日期:" + dtp1.Value.ToShortDateString() + " 到:" + dtp2.Value.ToShortDateString(); } else { title = title + "年份:" + cmbyear.Text.Trim() + " 月份:" + cmbmonth.Text.Trim(); } string where1 = ""; where1 = "药剂科室:" + cmbyjks.Text.Trim(); where1 = where1 + title; //写入行头 DataTable tb = (DataTable)this.myDataGrid1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = tb.Columns.Count; for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[5, 1 + j] = tb.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[6 + i, 1 + j] = "" + tb.Rows[i][j].ToString(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 myExcel.Cells[1, 1] = TrasenFrame.Classes.Constant.HospitalName + this.Text; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = where1.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butexcel.Enabled = true; #endregion } catch (System.Exception err) { this.butprint.Enabled = true; MessageBox.Show(err.Message); } }
private void EXCEL_MX(string name, string dw, string date) { if (this.dataGridView1.Rows.Count == 0) return; try { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A5").Merge(false); excel.get_Range("B4", "B5").Merge(false); excel.get_Range("C4", "C5").Merge(false); excel.get_Range("D4", "D5").Merge(false); excel.get_Range("E4", "E5").Merge(false); excel.get_Range("I4", "I5").Merge(false); excel.get_Range("H4", "H5").Merge(false); excel.get_Range("F4", "G4").Merge(false); excel.Cells[1, 1] = name; excel.Cells[3, 1] = dw + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "m5").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "S5").Font.Bold = true; excel.Cells[4, "A"] = "序号"; excel.Cells[4, "B"] = "合同号"; excel.Cells[4, "C"] = "客户"; excel.Cells[4, "D"] = "产品名称"; excel.Cells[4, "E"] = "上月余额"; excel.Cells[4, "F"] = "本月"; excel.Cells[4, "H"] = "结转下月余额"; excel.Cells[5, "f"] = "估验金额"; excel.Cells[5, "g"] = "冲估验金额"; excel.Cells[4, "I"] = "备注"; excel.get_Range("D6", excel.Cells[dataGridView1.Rows.Count + 5, "H"]).NumberFormat = "#,##0.00"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { //if (decimal.Parse(dataGridView1[5, i].Value.ToString() == "" ? "0" : dataGridView1[5, i].Value.ToString()) == 0 && decimal.Parse(dataGridView1[6, i].Value.ToString() == "" ? "0" : dataGridView1[6, i].Value.ToString()) == 0) //{ // continue; //} for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 6, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 6, j + 1] = dataGridView1[j, i].Value.ToString(); } } } //for (int i = 7; i < this.dataGridView1.Rows.Count; i++) //{ // if (excel.Cells[i, 2].ToString() == "System.__ComObject") // { // excel.get_Range("A" + i, "J" + i).Select(); // excel.get_Range("A" + i, "J" + i).Delete(Excel.XlDeleteShiftDirection.xlShiftUp); // } //} excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$5"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; } catch (Exception ex) { MessageBox.Show(ex.ToString()); MessageView.MessageErrorShow(ex); return; } }
private void button2_Click(object sender, EventArgs e) { if (dataGridView1.DataSource == null || ((DataTable)this.dataGridView1.DataSource).Rows.Count == 0) { MessageBox.Show("没有数据!"); return; } try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 this.butprint.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); Excel._Worksheet ws = (Excel._Worksheet)myExcel.ActiveSheet; //查询条件 string swhere = ""; if (_menuTag.Function_Name == "Fun_ts_mz_tjbb_jk_htdw") { swhere = "统计部门:" + cmbjgbm.Text + " 缴款日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + " 缴款员:" + cmbuser.Text; } else { swhere = "统计部门:" + cmbjgbm.Text + " 收费日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + " 收费员:" + cmbuser.Text; } //写入行头 DataTable tb = (DataTable)this.dataGridView1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < tb.Columns.Count; j++) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName; if (tb.Columns[j].ColumnName == "单位名称") { ws.get_Range(myExcel.Cells[5, SumColCount], myExcel.Cells[5, SumColCount]).ColumnWidth = 24; } else { ws.get_Range(myExcel.Cells[5, SumColCount], myExcel.Cells[5, SumColCount]).ColumnWidth = 9; } } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { ncol = ncol + 1; if (tb.Columns[j].ColumnName == "日期") { myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim(); } else { myExcel.Cells[6 + i, ncol] = "" + tb.Rows[i][j].ToString().Trim(); } } } //设置报表表格为最适应宽度 //myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); //myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = Constant.HospitalName + label1.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butprint.Enabled = true; #endregion } catch (System.Exception err) { this.butprint.Enabled = true; MessageBox.Show(err.Message); } finally { this.Cursor = Cursors.Arrow; } }
private void btExcel_Click(object sender, EventArgs e) { try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 DataTable tb = (DataTable)this.dataGridView1.DataSource; if (tb == null || tb.Rows.Count == 0) { return; } this.btExcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string type = "项目名称:" + txtXm.Text; string dept_type = ""; if (rbKdks.Checked) { dept_type = " 统计科室类型:" + rbKdks.Text; } else if (rbBrks.Checked) { dept_type = " 统计科室类型:" + rbBrks.Text; } else { dept_type = " 统计科室类型:" + rbZxks.Text; } string dept = " 科室:" + cmbDept.Text; string doc = " 医生:" + cmbDoctor.Text; string tj_type = ""; if (rbFsrq.Checked) { tj_type = " 统计日期方式:" + rbFsrq.Text; } else if (rbJsrq.Checked) { tj_type = " 统计日期方式:" + rbJsrq.Text; } else { tj_type = " 统计日期方式:" + rbDqzy.Text; } string rq = " 日期:" + this.dtp1.Value.ToString() + " 到 " + this.dtp2.Value.ToString(); string swhere = type + dept_type + dept + doc + tj_type + rq; //写入行头 int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < this.dataGridView1.Columns.Count; j++) { if (this.dataGridView1.Columns[j].Visible) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = "" + this.dataGridView1.Columns[j].HeaderText; } } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = tb.Rows[i][j].ToString().Trim();//"'" + } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = TrasenFrame.Classes.Constant.HospitalName + this.label1.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; #endregion } catch (System.Exception err) { MessageBox.Show(err.Message); } finally { this.btExcel.Enabled = true; this.Cursor = Cursors.Arrow; } }
/// <summary> /// DATAGIRDVIEW数据导出EXCEL /// 例:this.ExportDataGridview1(dataGridView1, true); /// </summary> /// <param name="dataGridView1">要导出的DATAGIRDVIEW</param> /// <param name="isShowExcle">是否显示导出的EXCEL</param> /// <returns>导出成功返回TRUE失败返回FALSE</returns> public static Excel.Application ExportDataGridview1(DataGridView dataGridView1, string name) { try { if (dataGridView1.Rows.Count == 0) return null; //建立Excel对象 Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.Cells[1, 1] = name; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //生成字段名称 for (int i = 0; i < dataGridView1.ColumnCount; i++) { excel.Cells[3, i + 1] = dataGridView1.Columns[i].HeaderText; } //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 4, j + 1] = "'" + (dataGridView1[j, i].Value == null ? "" : dataGridView1[j, i].Value.ToString()); } else { excel.Cells[i + 4, j + 1] = (dataGridView1[j, i].Value == null ? "" : dataGridView1[j, i].Value.ToString()); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 3, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); DrawExcelBorders(excel, "A3", excel.Cells[dataGridView1.Rows.Count + 3, dataGridView1.Columns.Count]); return excel; } catch (Exception ex) { //MessageBox.Show(ex.ToString()); throw ex; //return false; } }
private void EXCEL_HZ(string name, string date) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", "j2").Merge(false); excel.get_Range("A3", "j3").Merge(false); excel.get_Range("A4", "A5").Merge(false); excel.get_Range("B4", "B5").Merge(false); excel.get_Range("C4", "C5").Merge(false); excel.get_Range("J4", "J5").Merge(false); excel.get_Range("D4", "E4").Merge(false); excel.get_Range("F4", "G4").Merge(false); excel.get_Range("H4", "I4").Merge(false); excel.get_Range("A6", "A8").Merge(false); excel.get_Range("B6", "B8").Merge(false); //excel.get_Range("C6", "C8").Merge(false); excel.get_Range("J6", "J8").Merge(false); excel.get_Range("A9", "A11").Merge(false); excel.get_Range("B9", "B11").Merge(false); excel.get_Range("J9", "J11").Merge(false); //excel.get_Range("C9", "C11").Merge(false); excel.get_Range("A12", "A14").Merge(false); excel.get_Range("B12", "B14").Merge(false); excel.get_Range("J12", "J14").Merge(false); //excel.get_Range("C12", "C14").Merge(false); excel.get_Range("A15", "A17").Merge(false); excel.get_Range("B15", "B17").Merge(false); excel.get_Range("J15", "J17").Merge(false); //excel.get_Range("C15", "C17").Merge(false); excel.get_Range("A18", "A20").Merge(false); excel.get_Range("B18", "B20").Merge(false); excel.get_Range("J18", "J20").Merge(false); //excel.get_Range("C18", "C20").Merge(false); excel.get_Range("A21", "A23").Merge(false); excel.get_Range("B21", "B23").Merge(false); excel.get_Range("J21", "J23").Merge(false); //excel.get_Range("C21", "C23").Merge(false); excel.get_Range("A24", "A26").Merge(false); excel.get_Range("B24", "B26").Merge(false); excel.get_Range("J24", "J26").Merge(false); //excel.get_Range("C24", "C26").Merge(false); excel.get_Range("A27", "A29").Merge(false); excel.get_Range("B27", "B29").Merge(false); excel.get_Range("J27", "J29").Merge(false); //excel.get_Range("C27", "C29").Merge(false); excel.get_Range("A30", "A32").Merge(false); excel.get_Range("B30", "B32").Merge(false); excel.get_Range("J30", "J32").Merge(false); //excel.get_Range("C30", "C32").Merge(false); excel.get_Range("A33", "A35").Merge(false); excel.get_Range("B33", "B35").Merge(false); excel.get_Range("J33", "J35").Merge(false); //excel.get_Range("C33", "C35").Merge(false); excel.get_Range("A36", "A38").Merge(false); excel.get_Range("B36", "B38").Merge(false); excel.get_Range("J36", "J38").Merge(false); //excel.get_Range("C36", "C38").Merge(false); excel.get_Range("A1", "A1").Value = name; excel.get_Range("A3", "A3").Value = date; excel.get_Range("A4", "A4").Value = "序号"; excel.get_Range("B4", "B4").Value = "公司"; excel.get_Range("J4", "J4").Value = "备注"; excel.get_Range("C4", "C4").Value = "来源"; excel.get_Range("D4", "D4").Value = "本年"; excel.get_Range("F4", "F4").Value = "同期"; excel.get_Range("H4", "H4").Value = "增减额"; excel.get_Range("D5", "D5").Value = "本月"; excel.get_Range("E5", "E5").Value = "累计"; excel.get_Range("F5", "F5").Value = "本月"; excel.get_Range("G5", "G5").Value = "累计"; excel.get_Range("H5", "H5").Value = "本月"; excel.get_Range("I5", "I5").Value = "累计"; //excel.get_Range("A6", "A6").Value = ""; //excel.get_Range("B6", "B6").Value = "沈阳铸锻工业有限公司"; //excel.get_Range("C6", "C6").Value = "内部"; //excel.get_Range("C7", "C7").Value = "外部"; //excel.get_Range("C8", "C8").Value = "小计"; //excel.get_Range("A9", "A9").Value = "1"; //excel.get_Range("B9", "B9").Value = "沈阳铸锻工业有限公司本部公司"; //excel.get_Range("C9", "C9").Value = "内部"; //excel.get_Range("C10", "C10").Value = "外部"; //excel.get_Range("C11", "C11").Value = "小计"; //excel.get_Range("A12", "A12").Value = "2"; //excel.get_Range("B12", "B12").Value = "沈阳铸锻工业有限公司铸钢公司"; //excel.get_Range("C12", "C12").Value = "内部"; //excel.get_Range("C13", "C13").Value = "外部"; //excel.get_Range("C14", "C14").Value = "小计"; //excel.get_Range("A15", "A15").Value = "3"; //excel.get_Range("B15", "B15").Value = "沈阳铸锻工业有限公司锻造公司"; //excel.get_Range("C15", "C15").Value = "内部"; //excel.get_Range("C16", "C16").Value = "外部"; //excel.get_Range("C17", "C17").Value = "小计"; //excel.get_Range("A18", "A18").Value = "4"; //excel.get_Range("B18", "B18").Value = "沈阳铸锻工业有限公司热处理公司"; //excel.get_Range("C18", "C18").Value = "内部"; //excel.get_Range("C19", "C19").Value = "外部"; //excel.get_Range("C20", "C20").Value = "小计"; //excel.get_Range("A21", "A21").Value = "5"; //excel.get_Range("B21", "B21").Value = "沈阳铸锻工业有限公司特钢公司"; //excel.get_Range("C21", "C21").Value = "内部"; //excel.get_Range("C22", "C22").Value = "外部"; //excel.get_Range("C23", "C23").Value = "小计"; //excel.get_Range("A24", "A24").Value = "6"; //excel.get_Range("B24", "B24").Value = "沈阳铸锻工业有限公司铸铁公司"; //excel.get_Range("C24", "C24").Value = "内部"; //excel.get_Range("C25", "C25").Value = "外部"; //excel.get_Range("C26", "C26").Value = "小计"; //excel.get_Range("A27", "A27").Value = "7"; //excel.get_Range("B27", "B27").Value = "沈阳铸锻工业有限公司机加公司"; //excel.get_Range("C27", "C27").Value = "内部"; //excel.get_Range("C28", "C28").Value = "外部"; //excel.get_Range("C29", "C29").Value = "小计"; //excel.get_Range("A30", "A30").Value = "8"; //excel.get_Range("B30", "B30").Value = "沈阳铸锻工业有限公司动能公司"; //excel.get_Range("C30", "C30").Value = "内部"; //excel.get_Range("C31", "C31").Value = "外部"; //excel.get_Range("C32", "C32").Value = "小计"; //excel.get_Range("A33", "A33").Value = "9"; //excel.get_Range("B33", "B33").Value = "沈阳铸锻工业有限公司模型公司"; //excel.get_Range("C33", "C33").Value = "内部"; //excel.get_Range("C34", "C34").Value = "外部"; //excel.get_Range("C35", "C35").Value = "小计"; //excel.get_Range("A36", "A36").Value = "10"; //excel.get_Range("B36", "B36").Value = "沈阳铸锻工业有限公司销售模型公司"; //excel.get_Range("C36", "C36").Value = "内部"; //excel.get_Range("C37", "C37").Value = "外部"; //excel.get_Range("C38", "C38").Value = "小计"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 6, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 6, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", "J5").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "J5").Font.Bold = true; excel.get_Range("A6", "C38").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A6", "I8").Font.Bold = true; excel.get_Range("A1", "J38").EntireColumn.AutoFit(); //excel.get_Range("C6", excel.Cells[dataGridView1.Rows.Count + 5, "J"]).NumberFormat = "#,##0.00"; excel.get_Range("C6", excel.Cells[dataGridView1.Rows.Count + 5, "J"]).NumberFormat = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * -??_ ;_ @_ "; excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count + 1]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count + 1]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$5"; return; //excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 6).ToString()).Merge(false); ////(excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; ////(excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //excel.get_Range("A1", "T6").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //excel.get_Range("A3", "A3").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ////(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; ////(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //excel.get_Range("A1", "t6").Font.Bold = true; //excel.Cells[4, "A"] = "序号"; //excel.Cells[4, "B"] = "合同种类"; //excel.get_Range("A4", "A6").Value = "序号"; //excel.get_Range("B4", "c6").Value = "公司名称"; //excel.get_Range("D4", "G4").Value = "合同总额"; //excel.get_Range("H4", "K4").Value = (this.toolStripComboBox4.Text == "产品销售总览表" ? "已收货款" : "已付货款"); //excel.get_Range("L4", "M4").Value = (this.toolStripComboBox4.Text == "产品销售总览表" ? "未收货款" : "未付货款"); //excel.get_Range("N4", "Q4").Value = (this.toolStripComboBox4.Text == "产品销售总览表" ? "已开发票金额" : "已收发票金额"); //excel.get_Range("R4", "S4").Value = (this.toolStripComboBox4.Text == "产品销售总览表" ? "未开票金额" : "未收票金额"); //excel.get_Range("E5", "F5").Value = "本年"; //excel.get_Range("I5", "J5").Value = "本年"; //excel.get_Range("O5", "P5").Value = "本年"; //excel.get_Range("D5", "D6").Value = "以前年度"; //excel.get_Range("G5", "G6").Value = "总累计"; //excel.get_Range("H5", "H6").Value = "以前年度"; //excel.get_Range("K5", "K6").Value = "总累计"; //excel.get_Range("L5", "L6").Value = "金额"; //excel.get_Range("M5", "M6").Value = "比例"; //excel.get_Range("N5", "N6").Value = "以前年度"; //excel.get_Range("Q5", "Q6").Value = "总累计"; //excel.get_Range("R5", "R6").Value = "金额"; //excel.get_Range("S5", "S6").Value = "比例"; //excel.get_Range("T4", "T6").Value = "备注"; //excel.get_Range("E6", "E6").Value = "本月"; //excel.get_Range("F6", "F6").Value = "本年"; //excel.get_Range("I6", "I6").Value = "本月"; //excel.get_Range("J6", "J6").Value = "本年"; //excel.get_Range("O6", "O6").Value = "本月"; //excel.get_Range("P6", "P6").Value = "本年"; //excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "总计"; //excel.get_Range("c" + (this.dataGridView1.Rows.Count + 4).ToString(), "c" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "外部"; //excel.get_Range("c" + (this.dataGridView1.Rows.Count + 5).ToString(), "c" + (this.dataGridView1.Rows.Count + 5).ToString()).Value = "内部"; //excel.get_Range("c" + (this.dataGridView1.Rows.Count + 6).ToString(), "c" + (this.dataGridView1.Rows.Count + 6).ToString()).Value = "合计"; //excel.get_Range("a1", "t2").Value = name; //excel.get_Range("a3", "a3").Value = this.toolStripComboBox4.Text + " " + this.toolStripComboBox1.Text + "年" + this.toolStripComboBox2.Text + "月"; ////DataTable dt = DBAdo.DtFillSql("SELECT LID,LNAME FROM ALX WHERE LID LIKE '" + ClassCustom.codeSub(this.toolStripComboBox4.Text) + "__'"); //string str = ClassCustom.codeSub(this.toolStripComboBox4.Text); //DataTable dt = DBAdo.DtFillSql("SELECT CCODE,CNAME FROM ACLIENTS WHERE CCODE LIKE '01__'"); //int index = 0; //for (int i = 0; i < dt.Rows.Count * 2; i++) //{ // excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Merge(false); // excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Value = dt.Rows[index][1].ToString(); // excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Merge(false); // excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Value = (index + 1).ToString(); // excel.get_Range("C" + (i + 7).ToString(), "C" + (i + 7).ToString()).Value = "外部"; // excel.get_Range("C" + (i + 8).ToString(), "C" + (i + 8).ToString()).Value = "内部"; // index++; // i++; //} //excel.get_Range("d7", excel.Cells[dataGridView1.Rows.Count + 6, "t"]).NumberFormat = "#,##0.00"; //excel.get_Range("m7", excel.Cells[dataGridView1.Rows.Count + 6, "m"]).NumberFormat = "0%"; //excel.get_Range("s7", excel.Cells[dataGridView1.Rows.Count + 6, "s"]).NumberFormat = "0%"; ////填充数据 //for (int i = 0; i < dataGridView1.RowCount; i++) //{ // for (int j = 3; j < dataGridView1.ColumnCount; j++) // { // if (dataGridView1[j, i].ValueType == typeof(string)) // { // excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); // } // else // { // excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString(); // } // } //} //excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); //ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]); //Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; //sheet1.PageSetup.PrintTitleRows = "$1:$6"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
private void btnexcel_Click(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; try { #region 简单打印 this.Cursor = PubStaticFun.WaitCursor(); this.btnexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string title = "排名方式:"; if (rdoje.Checked == true) { title = title + "按金额排名"; } else { title = title + "按数量排名"; } string where1 = ""; where1 = "药剂科室:" + cmbyjks.Text.Trim(); where1 = where1 + " 日期:" + dtp1.Value.ToShortDateString() + " 到:" + dtp2.Value.ToShortDateString() + ""; where1 = where1 + " 药品类型:" + cmbyplx.Text + " 药品属性:" + cmbypsx.Text + " "; if (strYLFLtxt != "") { where1 = where1 + " 药理分类:" + txtYLFL.Text + " "; } where1 = where1 + title; //写入行头 DataTable tb = (DataTable)this.myDataGrid1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = tb.Columns.Count; for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[5, 1 + j] = tb.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[6 + i, 1 + j] = "" + tb.Rows[i][j].ToString(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 if (ifZFPH == "0") { myExcel.Cells[1, 1] = TrasenFrame.Classes.Constant.HospitalName + "销售排名统计"; } else { myExcel.Cells[1, 1] = TrasenFrame.Classes.Constant.HospitalName + "与上月对比增幅排名统计"; } myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = where1.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 //myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.btnexcel.Enabled = true; #endregion } catch (System.Exception err) { MessageBox.Show(err.Message); } finally { this.btnexcel.Enabled = true; this.Cursor = Cursors.Arrow; } }
private void cmiExcel_Click(object sender, EventArgs e) { try { if (this.dataGridView1.Rows.Count < 1) { return; } this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string swhere = ""; swhere = "办卡日期从:" + ksrq.ToString() + " 到:" + jsrq.ToString(); //写入行头 DataTable tb = (DataTable)this.dataGridView1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < tb.Columns.Count; j++) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "" + tb.Rows[i][j].ToString().Trim(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = Constant.HospitalName + label1.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; #endregion } catch (System.Exception err) { MessageBox.Show(err.Message); } finally { this.Cursor = Cursors.Arrow; } }
private void EXCEL_MX(string name, string dw, string date) { if (this.dataGridView1.Rows.Count == 0) return; Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A6").Merge(false); excel.get_Range("B4", "B6").Merge(false); excel.get_Range("C4", "C6").Merge(false); excel.get_Range("D4", "D6").Merge(false); excel.get_Range("i4", "i6").Merge(false); excel.get_Range("e4", "h4").Merge(false); excel.get_Range("f5", "g5").Merge(false); excel.get_Range("e5", "e6").Merge(false); excel.get_Range("h5", "h6").Merge(false); excel.Cells[1, 1] = name; excel.Cells[3, 1] = dw + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "S4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "S6").Font.Bold = true; excel.Cells[4, "A"] = "序号"; excel.Cells[4, "B"] = "内外"; excel.Cells[4, "C"] = "合同号"; excel.Cells[4, "D"] = "客户"; excel.Cells[4, "e"] = "结算金额"; excel.Cells[4, "i"] = "备注"; excel.Cells[5, "e"] = "上年"; excel.Cells[5, "h"] = "总累计"; excel.Cells[5, "f"] = "本年"; excel.Cells[6, "f"] = "本月"; excel.Cells[6, "g"] = "本年"; excel.get_Range("E7", excel.Cells[dataGridView1.Rows.Count + 6, "h"]).NumberFormat = "#,##0.00"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$6"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
private void butexcel_Click(object sender, EventArgs e) { //ts_jc_log.ExcelOper.ExportData_ForDataTable(myDataGrid1, "门诊处方用药统计表"); try { #region 导出 this.butexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string title = ""; string where1 = ""; title = "药剂科室:" + Convert.ToString(cmbyjks.Text).Trim() + " 领药方式:" + Convert.ToString(cmbtlfs.Text); if (txtks.Text != "") { title += " 科室编码包含:" + txtks.Text; } where1 = title + where1; //写入行头 DataTable tb = (DataTable)this.myDataGrid1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = tb.Columns.Count; for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[5, 1 + j] = tb.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { for (int j = 0; j < tb.Columns.Count; j++) { myExcel.Cells[6 + i, 1 + j] = "" + tb.Rows[i][j].ToString(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 myExcel.Cells[1, 1] = TrasenFrame.Classes.Constant.HospitalName + "科室领药情况"; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = where1.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; ////最后一行为黄色 //myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; #endregion } catch (System.Exception err) { MessageBox.Show(err.Message); } finally { this.butexcel.Enabled = true; } }
private void button1_Click(object sender, EventArgs e) { excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing); }
/// <summary> /// 将DataTable数据导出到Excel表 /// </summary> /// <param name="tmpDataTable">要导出的DataTable</param> /// <param name="strFileName">Excel的保存路径及名称</param> public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName) { if (tmpDataTable == null) { return; } long rowNum = tmpDataTable.Rows.Count; //行数 int columnNum = tmpDataTable.Columns.Count; //列数 Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = true; //不显示更改提示 m_xlApp.Visible = true; //false;// Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //worksheet.SetBackgroundPicture("d:\\26.jpg"); try { //查询条件 string rq = "日期:" + this.dateTimePicker1.Value.ToShortDateString() + " 到 " + this.dateTimePicker2.Value.ToShortDateString(); string ks = ""; string swhere = rq + ks; int SumColCount = tmpDataTable.Columns.Count; //for (int j = 0; j < tmpDataTable.Columns.Count; j++) //{ // if (this.dataGridView1.Columns[j].Visible) // { // SumColCount = SumColCount + 1; // m_xlApp.Cells[5, SumColCount] = "" + tmpDataTable.Columns[j].Caption; // } //} //报表名称 string ss = TrasenFrame.Classes.Constant.HospitalName + "已打印检验单表"; m_xlApp.Cells[1, 1] = ss; m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Bold = true; m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Select(); m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 m_xlApp.Cells[3, 1] = swhere.Trim(); m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Font.Size = 10; m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Select(); m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; if (rowNum > 65536) //单张Excel表格最大行数 { long pageRows = 65535; //定义每页显示的行数,行数必须小于65536 int scount = (int)(rowNum / pageRows); //导出数据生成的表单数 if (scount * pageRows < rowNum) //当总行数不被pageRows整除时,经过四舍五入可能页数不准 { scount = scount + 1; } for (int sc = 1; sc <= scount; sc++) { if (sc > 1) { object missing = System.Reflection.Missing.Value; worksheet = (Excel.Worksheet)workbook.Worksheets.Add( missing, missing, missing, missing); //添加一个sheet } else { worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1 } string[,] datas = new string[pageRows + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tmpDataTable.Columns[i].Caption; //表头信息 } Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int init = int.Parse(((sc - 1) * pageRows).ToString()); int r = 0; int index = 0; int result; if (pageRows * sc >= rowNum) { result = (int)rowNum; } else { result = int.Parse((pageRows * sc).ToString()); } for (r = init; r < result; r++) { index = index + 1; for (int i = 0; i < columnNum; i++) { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化 range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } } else { string[,] datas = new string[rowNum + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tmpDataTable.Columns[i].Caption; } Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int r = 0; for (r = 0; r < rowNum; r++) { for (int i = 0; i < columnNum; i++) { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } //workbook.Saved = true; // workbook.SaveCopyAs(strFileName); } catch (Exception ex) { MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (m_xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp); m_xlApp = null; //xlApp.Quit(); } GC.Collect(); //EndReport(); } }
/// <summary> /// 导出到EXCEL /// </summary> private void ExportToExcel() { DataTable dtData = (DataTable)this.dgvList.DataSource; int SumColCount = dtData.Columns.Count; int rowCount = dtData.Rows.Count; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); string xm = ""; xm = " 收费员:" + cmbuser.Text; string swhere = "日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + xm; string title = Constant.HospitalName + label1.Text; //写标题行 int excelRowIndex = 1; myExcel.Cells[excelRowIndex, 1] = title; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Merge(Type.Missing); myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Font.Size = 16; //写收费员及日期 excelRowIndex++; myExcel.Cells[excelRowIndex, 1] = swhere; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Merge(Type.Missing); //写表格列头 excelRowIndex++; int excelColIndex = 0; List <string> lstCatalog = new List <string>(); for (int i = 0; i < dgvList.Columns.Count; i++) { excelColIndex = i + 1; if (i < 2) { myExcel.Cells[excelRowIndex, excelColIndex] = dgvList.Columns[i].DataPropertyName; myExcel.get_Range(myExcel.Cells[excelRowIndex, excelColIndex], myExcel.Cells[excelRowIndex + 1, excelColIndex]).Merge(Type.Missing); } else { string temp = dgvList.Columns[i].DataPropertyName; string[] temps = temp.Split("_".ToCharArray()); if (!lstCatalog.Contains(temps[1])) { lstCatalog.Add(temps[1]); myExcel.Cells[excelRowIndex, excelColIndex] = temps[1]; } myExcel.Cells[excelRowIndex + 1, excelColIndex] = temps[0]; } } for (int i = 0; i < lstCatalog.Count; i++) { excelColIndex = 3 * (i + 1); myExcel.get_Range(myExcel.Cells[excelRowIndex, excelColIndex], myExcel.Cells[excelRowIndex, excelColIndex + 2]).Select(); myExcel.get_Range(myExcel.Cells[excelRowIndex, excelColIndex], myExcel.Cells[excelRowIndex, excelColIndex + 2]).Merge(Type.Missing); } //写数据 excelRowIndex += 2; //for ( int i = 0 ; i < dgvList.Rows.Count ; i++ ) //{ // excelColIndex = 1; // for ( int j = 0 ; j < dgvList.Columns.Count ; j++ ) // { // myExcel.Cells[excelRowIndex , excelColIndex] = dgvList[j , i].Value; // excelColIndex++; // } // excelRowIndex++; //} ExcelDataExport export = new ExcelDataExport(myExcel, dgvList, excelRowIndex); export.Exporting += new ExportingHandle(export_Exporting); int r, c; export.Export(out r, out c); excelRowIndex = r; excelColIndex = c; #region 网格样式调整 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Select(); //居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).HorizontalAlignment = Excel.Constants.xlCenter; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).VerticalAlignment = Excel.Constants.xlCenter; //网格线 myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.Constants.xlNone; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.Constants.xlNone; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; myExcel.ActiveWindow.DisplayGridlines = false; #endregion myExcel.Visible = true; }
private void EXCEL_ML(string date, string dw) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A5").Merge(false); excel.get_Range("B4", "B5").Merge(false); excel.get_Range("C4", "C5").Merge(false); excel.get_Range("D4", "D5").Merge(false); excel.get_Range("E4", "E5").Merge(false); //excel.get_Range("F4", "I4").Merge(false); excel.get_Range("l4", "l5").Merge(false); excel.get_Range("m4", "m5").Merge(false); excel.get_Range("n4", "n5").Merge(false); excel.get_Range("f4", "h4").Merge(false); excel.get_Range("i4", "k4").Merge(false); excel.Cells[1, 1] = "销售合同毛利明细表"; excel.Cells[3, 1] = dw + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "m5").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "S5").Font.Bold = true; excel.Cells[4, "A"] = "部门"; excel.Cells[4, "B"] = "签订日期"; excel.Cells[4, "C"] = "客户类型"; excel.Cells[4, "D"] = "合同号"; excel.Cells[4, "E"] = "客户"; excel.Cells[4, "F"] = "销售合同总额"; excel.Cells[4, "i"] = "外协合同总额"; excel.Cells[5, "f"] = "收入"; excel.Cells[5, "g"] = "税额"; excel.Cells[5, "h"] = "小计"; excel.Cells[5, "i"] = "成本"; excel.Cells[5, "j"] = "税额"; excel.Cells[5, "k"] = "小计"; excel.Cells[4, "l"] = "产品毛利"; excel.Cells[4, "m"] = "比率"; excel.Cells[4, "n"] = "备注"; excel.get_Range("F6", excel.Cells[dataGridView1.Rows.Count + 5, "M"]).NumberFormat = "#,##0.00"; excel.get_Range("M6", excel.Cells[dataGridView1.Rows.Count + 5, "M"]).NumberFormat = "0%"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 6, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 6, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$5"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
/// <summary> /// 导出数据表到Excel /// </summary> /// <param name="table">数据表</param> /// <param name="title">标题</param> /// <param name="info">相关信息</param> private void ExportToExcel(DataTable table, string title, string info) { if (table == null) { return; } Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //写入行头 int sumRowCount = table.Rows.Count; int sumColCount = 0; for (int j = 0; j < table.Columns.Count; j++) { sumColCount += 1; myExcel.Cells[5, sumColCount] = table.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, sumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, sumColCount]).Font.Size = 10; //逐行写入数据 for (int i = 0; i < table.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < table.Columns.Count; j++) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "'" + table.Rows[i][j].ToString().Trim(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Borders.LineStyle = 1; //报表名称 myExcel.Cells[1, 1] = title; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = info.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, sumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, sumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, sumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + sumRowCount, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; }
/// <summary> /// 导出到EXCEL /// </summary> private void ExportToExcel() { DataTable dtData = (DataTable)this.dgvList.DataSource; int SumColCount = dtData.Columns.Count; int rowCount = dtData.Rows.Count; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); string xm = ""; xm = " 院区:" + cmbjgbm.Text; string swhere = "日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + xm; string title = Constant.HospitalName + label1.Text; //写标题行 int excelRowIndex = 1; myExcel.Cells[excelRowIndex, 1] = title; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Merge(Type.Missing); myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Font.Size = 16; //写收费员及日期 excelRowIndex++; myExcel.Cells[excelRowIndex, 1] = swhere; myExcel.get_Range(myExcel.Cells[excelRowIndex, 1], myExcel.Cells[excelRowIndex, SumColCount]).Merge(Type.Missing); //写表格列头 excelRowIndex++; int grdStartRow = excelRowIndex; int excelColIndex = 0; for (int i = 0; i < dgvList.Columns.Count; i++) { excelColIndex = i + 1; myExcel.Cells[excelRowIndex, excelColIndex] = dgvList.Columns[i].HeaderText; } //写数据 excelRowIndex++; //excelColIndex = 1; //for ( int i = 0 ; i < dgvList.Rows.Count ; i++ ) //{ // excelColIndex = 1; // for ( int j = 0 ; j < dgvList.Columns.Count ; j++ ) // { // myExcel.Cells[excelRowIndex , excelColIndex] = dgvList[j , i].Value; // excelColIndex++; // } // excelRowIndex++; //} ExcelDataExport export = new ExcelDataExport(myExcel, dgvList, excelRowIndex); export.Exporting += new ExportingHandle(export_Exporting); int r, c; export.Export(out r, out c); excelRowIndex = r; excelColIndex = c; //选中明细网格 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Columns.AutoFit(); //居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).HorizontalAlignment = Excel.Constants.xlCenter; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).VerticalAlignment = Excel.Constants.xlCenter; //网格线 myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Select(); myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.Constants.xlNone; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.Constants.xlNone; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; myExcel.get_Range(myExcel.Cells[grdStartRow, 1], myExcel.Cells[excelRowIndex - 1, excelColIndex - 1]).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; myExcel.ActiveWindow.DisplayGridlines = false; myExcel.Visible = true; }
private void butexcel_Click(object sender, EventArgs e) { try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 this.butexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string swhere = ""; swhere = "医技科室:" + cmbzxks.Text + " 申请科室:" + cmbks.Text; string swhere1 = "确认日期从:" + dtpqrrq1.Value.ToShortDateString() + " 到 " + dtpqrrq2.Value.ToShortDateString() + " "; if (txtjcxm.Text.Trim() != "") { swhere1 = swhere1 + " 项目名称:" + txtjcxm.Text.Trim(); } //写入行头 DataTable tb = (DataTable)this.dgvyjsq.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < tb.Columns.Count; j++) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName; } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "" + tb.Rows[i][j].ToString().Trim(); } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = this.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; myExcel.Cells[4, 1] = swhere1.Trim(); myExcel.get_Range(myExcel.Cells[4, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[4, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[4, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butexcel.Enabled = true; #endregion } catch (System.Exception err) { this.butexcel.Enabled = true; MessageBox.Show(err.Message); } finally { this.Cursor = Cursors.Arrow; } }
private void EXCEL_FKMX() { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A6").Merge(false); excel.get_Range("B4", "c6").Merge(false); excel.get_Range("D4", "G4").Merge(false); excel.get_Range("H4", "K4").Merge(false); excel.get_Range("L4", "M4").Merge(false); excel.get_Range("N4", "Q4").Merge(false); excel.get_Range("R4", "S4").Merge(false); excel.get_Range("E5", "F5").Merge(false); excel.get_Range("I5", "J5").Merge(false); excel.get_Range("O5", "P5").Merge(false); excel.get_Range("D5", "D6").Merge(false); excel.get_Range("G5", "G6").Merge(false); excel.get_Range("H5", "H6").Merge(false); excel.get_Range("K5", "K6").Merge(false); excel.get_Range("L5", "L6").Merge(false); excel.get_Range("M5", "M6").Merge(false); excel.get_Range("N5", "N6").Merge(false); excel.get_Range("Q5", "Q6").Merge(false); excel.get_Range("R5", "R6").Merge(false); excel.get_Range("S5", "S6").Merge(false); excel.get_Range("T4", "T6").Merge(false); excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 6).ToString()).Merge(false); //(excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A1", "T6").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A3", "A3").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "t6").Font.Bold = true; excel.Cells[4, "A"] = "序号"; excel.Cells[4, "B"] = "合同种类"; excel.get_Range("A4", "A6").Value = "序号"; excel.get_Range("B4", "c6").Value = "公司名称"; excel.get_Range("D4", "G4").Value = "合同总额"; excel.get_Range("H4", "K4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "已收货款" : "已付货款"); excel.get_Range("L4", "M4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "未收货款" : "未付货款"); excel.get_Range("N4", "Q4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "已开发票金额" : "已收发票金额"); excel.get_Range("R4", "S4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "未开票金额" : "未收票金额"); excel.get_Range("E5", "F5").Value = "本年"; excel.get_Range("I5", "J5").Value = "本年"; excel.get_Range("O5", "P5").Value = "本年"; excel.get_Range("D5", "D6").Value = "以前年度"; excel.get_Range("G5", "G6").Value = "总累计"; excel.get_Range("H5", "H6").Value = "以前年度"; excel.get_Range("K5", "K6").Value = "总累计"; excel.get_Range("L5", "L6").Value = "金额"; excel.get_Range("M5", "M6").Value = "比例"; excel.get_Range("N5", "N6").Value = "以前年度"; excel.get_Range("Q5", "Q6").Value = "总累计"; excel.get_Range("R5", "R6").Value = "金额"; excel.get_Range("S5", "S6").Value = "比例"; excel.get_Range("T4", "T6").Value = "备注"; excel.get_Range("E6", "E6").Value = "本月"; excel.get_Range("F6", "F6").Value = "本年"; excel.get_Range("I6", "I6").Value = "本月"; excel.get_Range("J6", "J6").Value = "本年"; excel.get_Range("O6", "O6").Value = "本月"; excel.get_Range("P6", "P6").Value = "本年"; excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "总计"; excel.get_Range("c" + (this.dataGridView1.Rows.Count + 4).ToString(), "c" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "外部"; excel.get_Range("c" + (this.dataGridView1.Rows.Count + 5).ToString(), "c" + (this.dataGridView1.Rows.Count + 5).ToString()).Value = "内部"; excel.get_Range("c" + (this.dataGridView1.Rows.Count + 6).ToString(), "c" + (this.dataGridView1.Rows.Count + 6).ToString()).Value = "合计"; excel.get_Range("a1", "t2").Value = ClassCustom.codeSub1(this.toolStripComboBox4.Text) + "合同总览表"; excel.get_Range("a3", "a3").Value = this.toolStripComboBox4.Text + " " + this.toolStripComboBox1.Text + "年" + this.toolStripComboBox2.Text + "月"; //DataTable dt = DBAdo.DtFillSql("SELECT LID,LNAME FROM ALX WHERE LID LIKE '" + ClassCustom.codeSub(this.toolStripComboBox4.Text) + "__'"); string str = ClassCustom.codeSub(this.toolStripComboBox4.Text); DataTable dt = DBAdo.DtFillSql("SELECT CCODE,CNAME FROM ACLIENTS WHERE CCODE LIKE '01__'"); int index = 0; for (int i = 0; i < dt.Rows.Count * 2; i++) { excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Merge(false); excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Value = dt.Rows[index][1].ToString(); excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Merge(false); excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Value = (index + 1).ToString(); excel.get_Range("C" + (i + 7).ToString(), "C" + (i + 7).ToString()).Value = "外部"; excel.get_Range("C" + (i + 8).ToString(), "C" + (i + 8).ToString()).Value = "内部"; index++; i++; } excel.get_Range("d7", excel.Cells[dataGridView1.Rows.Count + 6, "t"]).NumberFormat = "#,##0.00"; excel.get_Range("m7", excel.Cells[dataGridView1.Rows.Count + 6, "m"]).NumberFormat = "0%"; excel.get_Range("s7", excel.Cells[dataGridView1.Rows.Count + 6, "s"]).NumberFormat = "0%"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 3; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$6"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
private void EXCEL_EXPORT_RMB(string tname, string cname, string date) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = true; //********************************************************************************************** excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false); excel.get_Range("A4", "A5").Merge(false); excel.get_Range("B4", "E4").Merge(false); excel.get_Range("F4", "J4").Merge(false); excel.get_Range("K4", "K5").Merge(false); excel.Cells[4, "A"] = "日期"; excel.Cells[4, "B"] = "金额"; excel.Cells[4, "F"] = "凭证信息"; excel.Cells[4, "K"] = "备注"; excel.Cells[5, "B"] = "现汇"; excel.Cells[5, "C"] = "票据"; excel.Cells[5, "D"] = "抹帐"; excel.Cells[5, "E"] = "小计"; excel.Cells[5, "F"] = "连接凭证"; excel.Cells[5, "G"] = "年"; excel.Cells[5, "H"] = "月"; excel.Cells[5, "I"] = "凭证类型"; excel.Cells[5, "J"] = "凭证号"; //********************************************************************************************** excel.Cells[1, 1] = tname; excel.Cells[3, 1] = cname + " " + date; (excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; (excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.get_Range("A4", "K4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.get_Range("A1", "K5").Font.Bold = true; //excel.get_Range("E7", excel.Cells[dataGridView1.Rows.Count + 6, "R"]).NumberFormat = "#,##0.00"; //excel.get_Range("k7", excel.Cells[dataGridView1.Rows.Count + 6, "k"]).NumberFormat = "0%"; excel.get_Range("A6", excel.Cells[dataGridView1.Rows.Count + 6, "A"]).NumberFormat = "yyyy-MM-dd"; //填充数据 for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 6, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 6, j + 1] = dataGridView1[j, i].Value.ToString(); } } } excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count]).EntireColumn.AutoFit(); ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 5, dataGridView1.Columns.Count + 1]); Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet; sheet1.PageSetup.PrintTitleRows = "$1:$6"; //sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS; }
private void butExcel_Click(object sender, EventArgs e) { try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 this.butexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //查询条件 string xm = " 缴款员:" + cmbuser.Text; string swhere = "缴款日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + xm; //写入行头 DataTable tb = (DataTable)this.dataGridView1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < tb.Columns.Count; j++) { //if (checkBox1.Checked == true) //{ // if (tb.Rows[tb.Rows.Count - 1][j].ToString().Trim() != "" && tb.Columns[j].ColumnName != "科室" ) // { // SumColCount = SumColCount + 1; // myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName; // } //} //else //{ if (dataGridView1.Columns[j].Visible == true) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName; } //} } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { //if (checkBox1.Checked == true) //{ // if (tb.Rows[tb.Rows.Count - 1][j].ToString().Trim() != "" && tb.Columns[j].ColumnName != "科室" && tb.Columns[j].ColumnName != "医生") // { // ncol = ncol + 1; // myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim(); // } //} //else //{ if (dataGridView1.Columns[j].Visible == true) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim(); } //} } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = Constant.HospitalName + label1.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butexcel.Enabled = true; #endregion } catch (System.Exception err) { this.butexcel.Enabled = true; MessageBox.Show(err.Message); } finally { this.Cursor = Cursors.Arrow; } }
/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { //DateTime start = DateTime.Now; try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 DataTable tb = (DataTable)this.myDataGrid1.DataSource; if (tb == null || tb.Rows.Count == 0) { return; } this.btnExport.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //写入行头 int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < this.myDataGrid1.Columns.Count; j++) { if (this.myDataGrid1.Columns[j].Visible) { SumColCount = SumColCount + 1; myExcel.Cells[2, SumColCount] = "" + this.myDataGrid1.Columns[j].HeaderText; } } myExcel.get_Range(myExcel.Cells[2, 1], myExcel.Cells[2, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[2, 1], myExcel.Cells[2, SumColCount]).Font.Size = 10; ////逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { ncol = ncol + 1; myExcel.Cells[3 + i, ncol] = tb.Rows[i][j].ToString().Trim();//"'" + } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[2, 1], myExcel.Cells[2 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = "分类记帐统计(" + this.BCurrentDept.DeptName + ")"; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[2 + SumRowCount, 1], myExcel.Cells[2 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; #endregion } catch (System.Exception err) { MessageBox.Show(err.Message); } finally { // MessageBox.Show(DateTime.Now.Subtract(start) + "毫秒"); this.btnExport.Enabled = true; this.Cursor = Cursors.Arrow; } }
private void butprint_Click(object sender, System.EventArgs e) { try { #region 简单打印 this.butprint.Enabled = false; Excel.Application myExcel = new Excel.Application( ); myExcel.Application.Workbooks.Add(true); //查询条件 string swhere = ""; //写入行头 DataTable tb = (DataTable)this.myDataGrid1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < this.myDataGrid1.TableStyles[0].GridColumnStyles.Count; j++) { if (this.myDataGrid1.TableStyles[0].GridColumnStyles[j].Width > 0) { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = this.myDataGrid1.TableStyles[0].GridColumnStyles[j].HeaderText.Trim(); } } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { if (this.myDataGrid1.TableStyles[0].GridColumnStyles[j].Width > 0) { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim(); } } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 myExcel.Cells[1, 1] = "药库药品供货商表"; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 //myExcel.get_Range(myExcel.Cells[5+SumRowCount,1],myExcel.Cells[5+SumRowCount,SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butprint.Enabled = true; #endregion } catch (System.Exception err) { this.butprint.Enabled = true; MessageBox.Show(err.Message); } }
private void butexcel_Click(object sender, EventArgs e) { if (dataGridView1.DataSource == null) { return; } try { this.Cursor = PubStaticFun.WaitCursor(); #region 简单打印 this.butexcel.Enabled = false; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); Excel._Worksheet ws = (Excel._Worksheet)myExcel.ActiveSheet; //查询条件 string xm = ""; if (rdJg.Checked == true) { xm = " 统计分类:经管项目"; } else { xm = " 统计分类:会计项目"; } //Modify By Zj 2012-10-15 string swhere = ""; if (chksfrq.Checked) { swhere = "收费日期从:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + xm + " 部门名称:" + cmbjgbm.Text; } else { swhere = "确费日期从:" + dtpqrrq1.Value.ToString() + " 到:" + dtpqrrq2.Value.ToString() + xm + " 部门名称:" + cmbjgbm.Text; } //写入行头 DataTable tb = (DataTable)this.dataGridView1.DataSource; int SumRowCount = tb.Rows.Count; int SumColCount = 0; for (int j = 0; j < tb.Columns.Count; j++) { if (checkBox1.Checked == true) { if (tb.Rows[tb.Rows.Count - 1][j].ToString().Trim() != "") { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName.Trim(); } } else { SumColCount = SumColCount + 1; myExcel.Cells[5, SumColCount] = tb.Columns[j].ColumnName.Trim(); } } myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 9; //逐行写入数据, for (int i = 0; i < tb.Rows.Count; i++) { int ncol = 0; for (int j = 0; j < tb.Columns.Count; j++) { if (checkBox1.Checked == true) { if (tb.Rows[tb.Rows.Count - 1][j].ToString().Trim() != "") { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "" + tb.Rows[i][j].ToString().Trim(); } } else { ncol = ncol + 1; myExcel.Cells[6 + i, ncol] = "" + tb.Rows[i][j].ToString().Trim(); } } } //设置报表表格为最适应宽度 myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Font.Size = 9; //加边框 myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1; //报表名称 string ss = Constant.HospitalName + label1.Text; myExcel.Cells[1, 1] = ss; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true; myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16; //报表名称跨行居中 myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //报表条件 myExcel.Cells[3, 1] = swhere.Trim(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10; myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select(); myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //最后一行为黄色 myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19; //让Excel文件可见 myExcel.Visible = true; this.butexcel.Enabled = true; #endregion } catch (System.Exception err) { this.butexcel.Enabled = true; MessageBox.Show(err.Message); } finally { this.Cursor = Cursors.Arrow; } }