/// <summary>
/// 导出数据到excel
/// </summary>
/// <param name="Report">报表数据</param>
public void ExportData(DataTable dtReport, DateTime?bdate, DateTime?edate)
{
try
{
int totalColumn = dtReport.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
DataTable Report = dtReport.Clone();
for (int i = 0; i < dtReport.Rows.Count; i++)
{
Report.Rows.Add(dtReport.Rows[i].ItemArray);
}
Report.Columns["prjname"].ColumnName = "名称";
Report.Columns["SPEC"].ColumnName = "药品规格";
Report.Columns["TOTALNUM"].ColumnName = "销售数量";
Report.Columns["PRJFEE"].ColumnName = "销售金额";
Report.Columns["PRODUCTNAME"].ColumnName = "生产厂家";
//DataRow endRow = Report.NewRow();
//endRow["prjname"] = "合计";
//endRow["销售金额"] = 0;
//endRow["销售数量"] = 0;
//for (int index = 0; index < Report.Rows.Count; index++) // 显示销量数量和金额统计，增加报表头 2010.9.9 heyan
//{
// endRow["销售金额"] = Convert.ToDecimal(Report.Rows[index]["销售金额"])
// + Convert.ToDecimal(endRow["销售金额"]);
// endRow["销售数量"] = Convert.ToDecimal(Report.Rows[index]["销售数量"])
// + Convert.ToDecimal(endRow["销售数量"]);
//}
//endRow["销售金额"] = Convert.ToDecimal(endRow["销售金额"]).ToString("0.00").ToString();
//Report.Rows.Add(endRow);
#region 填充数据
//int row = 3;
//Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
//Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
//for (int i = 0; i < Report.Columns.Count; i++)
// excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
//row = row + 1;
//for (int i = 0; i < Report.Rows.Count; i++)
//{
// for (int j = 0; j < Report.Columns.Count; j++)
// {
// object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
// if (Convert.IsDBNull(objValue))
// continue;
// excel.Cells[row + i, j + 1] = objValue.ToString();
// }
//}
#region 填充数据
excel.Cells[1, 1] = HIS.SYSTEM.BussinessLogicLayer.Classes.BaseData.WorkName + "药品销量统计汇总报表";
Microsoft.Office.Interop.Excel.Range titleStartcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1];
Microsoft.Office.Interop.Excel.Range titleEndcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, totalColumn];
excel.get_Range(titleStartcell, titleEndcell).Merge(0);
excel.get_Range(titleStartcell, titleEndcell).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
excel.get_Range(titleStartcell, titleEndcell).Font.Name = "宋体";
excel.get_Range(titleStartcell, titleEndcell).Font.Size = 15;
excel.get_Range(titleStartcell, titleEndcell).Font.Bold = true;
excel.Cells[2, 1] = "统计时间：";
excel.Cells[2, 2] = bdate.Value.ToString("yyyy-MM-dd HH:mm:ss") + " -- " + edate.Value.ToString("yyyy-MM-dd HH:mm:ss");
excel.get_Range((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 2], (Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 6]).Merge(0);
//excel.Cells[2, totalColumn - 2] = "制表人：";
//excel.Cells[2, totalColumn - 1] = _user.Name;
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
}
#endregion
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
#endregion
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch (Exception err)
{
throw err;
}
finally
{
GC.Collect();
}
}
/// <summary>
/// 导出数据到excel
/// </summary>
/// <param name="Report">报表数据</param>
static public void ExportData(DataTable Report)
{
try
{
int totalColumn = Report.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
#region 填充数据
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
}
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
#endregion
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch (Exception err)
{
throw err;
}
finally
{
GC.Collect();
}
}
/// <summary>
/// 输出Excel
/// </summary>
/// <param name="Report"></param>
public void ReportToExcel(DataTable Report)
{
try
{
int totalColumn = Report.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
//e.TotalCount = Report.DataResult.Rows.Count;
#region 填充数据
excel.Cells[1, 1] = HIS.SYSTEM.BussinessLogicLayer.Classes.BaseData.WorkName + "住院病人收入统计";
Microsoft.Office.Interop.Excel.Range titleStartcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1];
Microsoft.Office.Interop.Excel.Range titleEndcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, totalColumn];
excel.get_Range(titleStartcell, titleEndcell).Merge(0);
excel.get_Range(titleStartcell, titleEndcell).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
excel.get_Range(titleStartcell, titleEndcell).Font.Name = "宋体";
excel.get_Range(titleStartcell, titleEndcell).Font.Size = 15;
excel.get_Range(titleStartcell, titleEndcell).Font.Bold = true;
excel.Cells[2, 1] = "统计时间：";
excel.Cells[2, 2] = bdate.ToString("yyyy-MM-dd HH:mm:ss") + " -- " + edate.ToString("yyyy-MM-dd HH:mm:ss");
excel.get_Range((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 2], (Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 6]).Merge(0);
excel.Cells[2, totalColumn - 2] = "制表人：";
excel.Cells[2, totalColumn - 1] = _user.Name;
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
//e.CurrentCount = i;
//if (OnExporting != null)
// OnExporting(e);
}
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
row = row + Report.Rows.Count + 1;
excel.Cells[row, 1] = "审核人";
excel.Cells[row, totalColumn - 2] = "打印日期：";
excel.Cells[row, totalColumn] = HIS.SYSTEM.PubicBaseClasses.XcDate.ServerDateTime.ToString("yyyy-MM-dd");
#endregion
//excel.get_Range(titleStartcell, titleEndcell).Select();
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch (Exception err)
{
MessageBox.Show("输出到Excel发生错误！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
//ErrorWriter.WriteLog(err.Message);
}
finally
{
GC.Collect();
}
}
protected void DataTableToExcel2(System.Data.DataTable qingjiadantbl, string fileName, bool flag)
{
if (qingjiadantbl == null)
{
return;
}
int rowNum = qingjiadantbl.Rows.Count;
int columnNum = qingjiadantbl.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook newBook = excelApp.Workbooks.Add();
//newBook.SaveAs(fileName);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn col in qingjiadantbl.Columns)
{
columnIndex++;
excelApp.Cells[rowIndex, columnIndex] = col.Caption;
}
//将DataTable中的数据导入Excel中
Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[1, 3], excelApp.Cells[rowNum + 1, columnIndex]);
r.NumberFormat = "@";
r.NumberFormatLocal = "@";
for (int i = 0; i < rowNum; i++)
{
rowIndex++;//数据从第二行开始
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//if (columnIndex == 3||columnIndex == 18)
//{
// Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[rowIndex, columnIndex], excelApp.Cells[rowIndex, columnIndex]);
// r.NumberFormat = "@";
// r.NumberFormatLocal = "@";
//}
excelApp.Cells[rowIndex, columnIndex] = qingjiadantbl.Rows[i][j].ToString();
}
}
excelApp.Cells.Columns.AutoFit();
newBook.SaveCopyAs(fileName);
newBook.Close();
excelApp.Workbooks.Close();
excelApp.Quit();
//Kill打开的Excel进程
//Process[] excelApps;
//excelApps = Process.GetProcessesByName("EXCEL");
//foreach (Process p in excelApps)`1q
//{
// p.Kill();
//}//End
if (flag)
{
AddAtt(fileName); //加入附件中
}
}