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