public class ExcelHelper { private static object missing = Type.Missing; #region ================导出================= /// <summary> /// 导出到Excel /// </summary> /// <param name="dgv"></param> public static void ExportToExcel(System.Windows.Forms.DataGridView dgv) { ExportToExcel(dgv, 0, dgv.Columns.Count); } /// <summary> /// 导出到Excel /// </summary> /// <param name="dgv"></param> /// <param name="startColumnIndex">从第几列开始</param> public static void ExportToExcel(System.Windows.Forms.DataGridView dgv, int startColumnIndex) { ExportToExcel(dgv, startColumnIndex, dgv.Columns.Count); } /// <summary> /// 导出到Excel /// </summary> /// <param name="dgv"></param> /// <param name="startColumnIndex">从第几列开始</param> /// <param name="endColumnIndex">到第几列结束</param> public static void ExportToExcel(System.Windows.Forms.DataGridView dgv, int startColumnIndex, int endColumnIndex) { if (dgv.Rows.Count == 0) return; try { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; app.UserControl = false; Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(missing); Microsoft.Office.Interop.Excel.Worksheet ws = app.ActiveWorkbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet; try { ExportToExcel(app, wb, ws, dgv, startColumnIndex, endColumnIndex); } finally { app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); GC.Collect(); } } catch { System.Windows.Forms.MessageBox.Show("无法正常启动计算机上的EXCEL软件！", "系统提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning); } } /// <summary> /// 导出到excel /// </summary> /// <param name="app"></param> /// <param name="wb"></param> /// <param name="ws"></param> /// <param name="dgv"></param> /// <param name="startColumnIndex">从第几列开始</param> /// <param name="startColumnIndex">到第几列结束</param> private static void ExportToExcel(Microsoft.Office.Interop.Excel.Application app, Microsoft.Office.Interop.Excel._Workbook wb, Microsoft.Office.Interop.Excel.Worksheet ws, System.Windows.Forms.DataGridView dgv, int startColumnIndex, int endColumnIndex) { Microsoft.Office.Interop.Excel.Range range; int rowCount = dgv.Rows.Count; int colCount = 0; int colIndex = 0; int rowIndex = 0; #region 开始到结束 int startIndex = 0;//开始的列索引 int endIndex = dgv.Columns.Count;//结束的列索引 if (endColumnIndex < endIndex) endIndex = endColumnIndex; if (startIndex < endIndex && startColumnIndex < endIndex) { startIndex = startColumnIndex; } #endregion for (int i = startIndex; i < endIndex; i++) { if (!dgv.Columns[i].Visible) continue; colCount++; } //foreach (DataGridViewColumn col in dgv.Columns) //{ // if (!col.Visible) // continue; // colCount++; //} #region "Set title" String endCellAddress = ""; if (colCount > 0 && colCount <= 26) { endCellAddress = ((char)('A' + colCount)).ToString() + "1"; } else if (colCount > 26 && colCount < 26 * 26) { int iTemp = (int)Math.Ceiling((double)colCount / (double)26); endCellAddress = ((char)('A' + iTemp)).ToString() + ((char)('A' + (colCount - 26 * 26))).ToString() + "1"; } else { throw new Exception("列数太多，操作出错！"); } ws.Cells[1, 1] = "导出数据"; range = ws.get_Range("A1", endCellAddress); range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.Font.Size = 20; range.Font.Name = "黑体"; range.Borders.LineStyle = 1; range.Select(); range.Merge(false); //range.Merge(ws.get_Range("A1", endCellAddress)); #endregion "Set title" #region " Set Header Values " object[,] colValues = new object[1, colCount + 1]; colIndex = 0; colValues[0, colIndex] = "序号"; for (int i = startIndex; i < endIndex; i++) { if (!dgv.Columns[i].Visible) continue; colValues[0, colIndex + 1] = dgv.Columns[i].HeaderText; colIndex++; } //foreach (DataGridViewColumn col in dgv.Columns) //{ // if (!col.Visible) // continue; // colValues[0, colIndex + 1] = col.HeaderText; // colIndex++; //} range = ws.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount + 1, 2)); range.Value2 = colValues; #endregion #region " Header Style " range.Font.Bold = true; range.Font.Name = "Georgia"; range.Font.Size = 10; range.RowHeight = 26; range.EntireColumn.AutoFit(); #endregion #region " Set Row Values " object[,] rowValues = new object[rowCount, colCount + 1]; rowIndex = 0; foreach (System.Windows.Forms.DataGridViewRow row in dgv.Rows) { colIndex = 0; rowValues[rowIndex, colIndex] = rowIndex + 1; foreach (System.Windows.Forms.DataGridViewCell cell in row.Cells) { if (cell.ColumnIndex == 0) { colIndex++; continue; } if (row.Cells[colIndex].Value.ToString().IsInt()) rowValues[row.Index, colIndex] = string.Format("'{0}", row.Cells[colIndex].Value.ToString()); else rowValues[row.Index, colIndex] = row.Cells[colIndex].Value.ToString(); colIndex++; } rowIndex++; } range = ws.get_Range(GetExcelCellName(1, 3), GetExcelCellName(colCount + 1, rowCount + 2)); range.Value2 = rowValues; #endregion #region " Row Style " range.Font.Name = "Georgia"; range.Font.Size = 9; range.RowHeight = 18; range.EntireColumn.AutoFit(); range.Borders.ColorIndex = 2; #endregion #region " Set Borders " range = ws.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount + 1, rowCount + 2)); range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; range.Borders.Color = System.Drawing.Color.Black.ToArgb(); #endregion } #region " GetCellName " private static string GetExcelCellName(int fColIndex, int fRowIndex) { if (fColIndex <= 0 || fColIndex > 256) { throw new Exception("Excel 列索引数值超出范围(1-256)!"); } else if (fColIndex <= 26) { return GetExcelCellName(fColIndex) + fRowIndex.ToString(); } else { string retLetter = GetExcelCellName(fColIndex / 26); retLetter += GetExcelCellName(fColIndex % 26); retLetter += fRowIndex.ToString(); return retLetter; } } private static string GetExcelCellName(int fColIndex) { int i = 1; foreach (string letter in Enum.GetNames(typeof(ExcelColumnLetters))) { if (i == fColIndex) return letter; i++; } throw new Exception("Excel 列索引数值超出范围(1-256)!"); } #endregion public enum ExcelColumnLetters { A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7, H = 8, I = 9, J = 10, K = 11, L = 12, M = 13, N = 14, O = 15, P = 16, Q = 17, R = 18, S = 19, T = 20, U = 21, V = 22, W = 23, X = 24, Y = 25, Z = 26 } #endregion }