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