/// <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;
}
}
