/// <summary> /// 将 DataTable 导出到 Excel /// </summary> /// <param name="fApplication"></param> /// <param name="fWorkBook"></param> /// <param name="fOption"></param> private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption) { Excel.Range range; worksheet.Name = fOption.WorkSheetName; if (fOption.DataTable == null) return; int rowCount = fOption.DataTable.Rows.Count; int colCount = fOption.VisibleColumnOptions.Count; int colIndex = 0; int rowIndex = 0; #region " Set Header Values " object[,] colValues = new object[1, colCount]; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { if (!option.Visible) continue; colValues[0, colIndex] = option.Caption; colIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1)); range.Value2 = colValues; #endregion #region " Header Style " range.Font.Bold = true; //range.Font.Name = "Georgia"; range.Font.Name = "宋体"; range.Font.Size = 10; range.RowHeight = 26; range.EntireColumn.AutoFit(); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //range.Locked = true; #endregion #region " Set Row Values " object[,] rowValues = new object[rowCount, colCount]; rowIndex = 0; foreach (DataRow dataRow in fOption.DataTable.Rows) { colIndex = 0; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { //rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; //防止自动将 rowValues[rowIndex, colIndex] ="'"+dataRow[option.ColumnName]; colIndex++; } rowIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)); // worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)).Merge( range.Value2 = rowValues; #region 测试合并自定的单元格 20120612 CS //int tempRowCountStart = 1; //int tempRowCountEnd = 1; ////for (int i = 2; i <=rowCount+1; i++) //Excel.Range tempRange; //// int tempi = 0; //string startValue = ""; ; //fApplication.DisplayAlerts = false; //取消合并CELL的时候弹出提示框 //foreach (DataRow dr in fOption.DataTable.Rows) //{ // //tempi = tempi + 1; // if (dr[0].ToString() == startValue) // { // tempRowCountEnd = tempRowCountEnd + 1; // startValue = dr[0].ToString(); // } // else // { // if (tempRowCountEnd - tempRowCountStart > 0) // { // //合并本次付款金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]); // tempRange.Merge(tempRange.MergeCells); // //订单金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]); // tempRange.Merge(tempRange.MergeCells); // //发票总金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]); // tempRange.Merge(tempRange.MergeCells); // //财务最后付款日期 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]); // tempRange.Merge(tempRange.MergeCells); // //财务付款总额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]); // tempRange.Merge(tempRange.MergeCells); // } // tempRowCountStart = tempRowCountEnd + 1; // tempRowCountEnd = tempRowCountStart; // startValue = dr[0].ToString(); // } //} //#region 解决最后全相同的时候,不合并的情况 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]); //tempRange.Merge(tempRange.MergeCells); ////订单金额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]); //tempRange.Merge(tempRange.MergeCells); ////发票总金额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]); //tempRange.Merge(tempRange.MergeCells); ////财务最后付款日期 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]); //tempRange.Merge(tempRange.MergeCells); ////财务付款总额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]); //tempRange.Merge(tempRange.MergeCells); //fApplication.DisplayAlerts = true; //取消警告弹出提示框 //#endregion #endregion #region " Row Style " range.Font.Name = "宋体"; range.Font.Size = 9; // range.RowHeight = 18; range.EntireColumn.AutoFit(); //range.Borders.ColorIndex = 2; #endregion #endregion #region " Set Borders " range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.ColumnWidth = 12; range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1)); range.Borders.Weight = Excel.XlBorderWeight.xlThin; range.Borders.Color = Color.Black.ToArgb(); #endregion }
public static void ExportToExcel(DataTableExportOptions fOption, String fFileName) { ExportToExcel(new List <DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName); }
public static void ExportToExcel(DataTableExportOptions fOption, String fFileName) { ExportToExcel(new List<DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName); }
/// <summary> /// 将 DataTable 导出到 Excel /// </summary> /// <param name="fApplication"></param> /// <param name="fWorkBook"></param> /// <param name="fOption"></param> private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption) { Excel.Range range; worksheet.Name = fOption.WorkSheetName; if (fOption.DataTable == null) { return; } int rowCount = fOption.DataTable.Rows.Count; int colCount = fOption.VisibleColumnOptions.Count; int colIndex = 0; int rowIndex = 0; #region " Set Header Values " object[,] colValues = new object[1, colCount]; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { if (!option.Visible) { continue; } colValues[0, colIndex] = option.Caption; colIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1)); range.Value2 = colValues; #endregion #region " Header Style " range.Font.Bold = true; //range.Font.Name = "Georgia"; range.Font.Name = "宋体"; range.Font.Size = 10; range.RowHeight = 26; range.EntireColumn.AutoFit(); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //range.Locked = true; #endregion #region " Set Row Values " object[,] rowValues = new object[rowCount, colCount]; rowIndex = 0; foreach (DataRow dataRow in fOption.DataTable.Rows) { colIndex = 0; foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions) { //rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; //防止自动将 rowValues[rowIndex, colIndex] = "'" + dataRow[option.ColumnName]; colIndex++; } rowIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)); // worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)).Merge( range.Value2 = rowValues; #region 测试合并自定的单元格 20120612 CS //int tempRowCountStart = 1; //int tempRowCountEnd = 1; ////for (int i = 2; i <=rowCount+1; i++) //Excel.Range tempRange; //// int tempi = 0; //string startValue = ""; ; //fApplication.DisplayAlerts = false; //取消合并CELL的时候弹出提示框 //foreach (DataRow dr in fOption.DataTable.Rows) //{ // //tempi = tempi + 1; // if (dr[0].ToString() == startValue) // { // tempRowCountEnd = tempRowCountEnd + 1; // startValue = dr[0].ToString(); // } // else // { // if (tempRowCountEnd - tempRowCountStart > 0) // { // //合并本次付款金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]); // tempRange.Merge(tempRange.MergeCells); // //订单金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]); // tempRange.Merge(tempRange.MergeCells); // //发票总金额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]); // tempRange.Merge(tempRange.MergeCells); // //财务最后付款日期 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]); // tempRange.Merge(tempRange.MergeCells); // //财务付款总额 // tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]); // tempRange.Merge(tempRange.MergeCells); // } // tempRowCountStart = tempRowCountEnd + 1; // tempRowCountEnd = tempRowCountStart; // startValue = dr[0].ToString(); // } //} //#region 解决最后全相同的时候,不合并的情况 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]); //tempRange.Merge(tempRange.MergeCells); ////订单金额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]); //tempRange.Merge(tempRange.MergeCells); ////发票总金额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]); //tempRange.Merge(tempRange.MergeCells); ////财务最后付款日期 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]); //tempRange.Merge(tempRange.MergeCells); ////财务付款总额 //tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]); //tempRange.Merge(tempRange.MergeCells); //fApplication.DisplayAlerts = true; //取消警告弹出提示框 //#endregion #endregion #region " Row Style " range.Font.Name = "宋体"; range.Font.Size = 9; // range.RowHeight = 18; range.EntireColumn.AutoFit(); //range.Borders.ColorIndex = 2; #endregion #endregion #region " Set Borders " range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.ColumnWidth = 12; range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1)); range.Borders.Weight = Excel.XlBorderWeight.xlThin; range.Borders.Color = Color.Black.ToArgb(); #endregion }