|
using System; using System.IO; using System.Collections.Generic; using System.Text; using System.Data; using System.Drawing; using System.Windows.Forms; namespace eCityboySoft.Common { /// /// 标题：将 DataGridView 导出到 Excel 或 Xml 通用类 /// 作者：宜城小子（http://blog.） /// 日期：2010-03-21 /// 描述：导出时可以指定需要导出的 DataGridViewColumn 及自定义导出后的列名 /// class ExportHelper { private object missing = Type.Missing; #region " DataGridViewToExcel " /// /// 将 DataGridView 保存为 Excel /// /// DataGridView 对象 /// 保存的文件路径 public void DataGridViewToExcel(DataGridView dataGridView, String filePath) { DataGridViewToExcel(new DataGridViewExportOptions(dataGridView), filePath); } /// /// 将 DataGridView 保存为 Excel /// /// DataGridViewExportOptions 对象 /// 保存的文件路径 public void DataGridViewToExcel(DataGridViewExportOptions dataGridViewExportOption, String filePath) { DataGridViewToExcel(new List(new DataGridViewExportOptions[] { dataGridViewExportOption }), filePath); } /// /// 将 DataGridView 保存为 Excel /// /// DataGridViewExportOptions 对象列表 /// 保存的文件路径 public void DataGridViewToExcel(List dataGridViewExportOptions, String filePath) { if (dataGridViewExportOptions == null || dataGridViewExportOptions.Count == 0) return; #region " 判断文件是否存在，存在则删除原有文件 " try { if (File.Exists(filePath)) File.Delete(filePath); } catch { return; } #endregion Excel.Application application = new Excel.Application(); application.Visible = false; application.UserControl = false; Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing)); #region " 根据需要导出的 DataGridView 数量，预先增加不足的工作表或移除多余的工作表 " // 移除多余的工作表 while (application.ActiveWorkbook.Sheets.Count > dataGridViewExportOptions.Count) ((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete(); // 添加工作表 while (application.ActiveWorkbook.Sheets.Count < dataGridViewExportOptions.Count) application.Worksheets.Add(missing, missing, missing, missing); #endregion int sheetIndex = 1; List sheetNames = new List(); try { foreach (DataGridViewExportOptions option in dataGridViewExportOptions) { #region " 处理在多个 DataGridView 设置为相同的工作表名称的问题 " if (sheetNames.Contains(option.WorkSheetName)) { int i = 1; while (true) { string newSheetName = option.WorkSheetName + i.ToString(); if (!sheetNames.Contains(newSheetName)) { sheetNames.Add(newSheetName); option.WorkSheetName = newSheetName; break; } i++; } } else { sheetNames.Add(option.WorkSheetName); } #endregion ExportToExcel(application, workBook, (Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex], option); sheetIndex++; } workBook.SaveAs(filePath, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); } finally { application.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(application); GC.Collect(); } } #endregion #region " DataGridViewToXml " public void DataGridViewToXml(DataGridView dataGridView, String filePath) { DataGridViewToXml(new DataGridViewExportOptions(dataGridView), filePath); } public void DataGridViewToXml(DataGridViewExportOptions dataGridViewExportOption, String filePath) { DataGridViewToXml(new List(new DataGridViewExportOptions[] { dataGridViewExportOption }), filePath); } public void DataGridViewToXml(List dataGridViewExportOptions, String filePath) { if (dataGridViewExportOptions == null || dataGridViewExportOptions.Count == 0) return; DataSet dataSet = new DataSet(); int sheetIndex = 1; List sheetNames = new List(); try { foreach (DataGridViewExportOptions option in dataGridViewExportOptions) { #region " 处理在多个 DataGridView 设置为相同的表名称的问题 " if (sheetNames.Contains(option.WorkSheetName)) { int i = 1; while (true) { string newSheetName = option.WorkSheetName + i.ToString(); if (!sheetNames.Contains(newSheetName)) { sheetNames.Add(newSheetName); option.WorkSheetName = newSheetName; break; } i++; } } else { sheetNames.Add(option.WorkSheetName); } DataGridViewFillToDataSet(dataSet, option); sheetIndex++; #endregion } ExportToXml(dataSet, filePath); } finally { dataSet.Dispose(); GC.Collect(); } } // 处理 DataGridView 中的数据以填充到指定的 DataSet 中 private void DataGridViewFillToDataSet(DataSet dataSet, DataGridViewExportOptions Option) { DataTable Table = new DataTable(); Table.TableName = Option.WorkSheetName; if (Option.DataGridView == null) return; #region " 填充表头内容 " foreach (DataColumnExportOptions option in Option.VisibleColumnOptions) { if (!option.Visible) continue; Table.Columns.Add(new DataColumn(option.ColumnName)); } #endregion #region " 填充表格正文内容 " foreach (DataGridViewRow dataRow in Option.DataGridView.Rows) { if (dataRow.IsNewRow) continue; DataRow Row = Table.NewRow(); foreach (DataColumnExportOptions option in Option.VisibleColumnOptions) { if (dataRow.Cells[option.ColumnName].Value == null) { Row[option.ColumnName] = ""; } else { Row[option.ColumnName] = dataRow.Cells[option.ColumnName].Value.ToString(); } } Table.Rows.Add(Row); } #endregion dataSet.Tables.Add(Table); } #endregion #region " ExportToXml " /// /// 保存 DataSet 数据到 Xml 文件 /// /// DataSet数据对象 /// Xml 文件地址 private void ExportToXml(DataSet dataSet, String filePath) { #region " 判断文件是否存在，存在则删除原有文件 " try { if (File.Exists(filePath)) File.Delete(filePath); } catch { return; } #endregion dataSet.WriteXml(filePath); } #endregion #region " ExportToExcel " private void ExportToExcel(Excel.Application application, Excel._Workbook workBook, Excel.Worksheet worksheet, DataGridViewExportOptions Option) { Excel.Range range; worksheet.Name = Option.WorkSheetName; if (Option.DataGridView == null) return; int _RowCount = Option.DataGridView.AllowUserToAddRows ? Option.DataGridView.Rows.Count - 1 : Option.DataGridView.Rows.Count; int _CellCount = Option.VisibleColumnOptions.Count; int _CellIndex = 0; int _RowIndex = 0; #region " 填充表头内容 " object[,] _HeadValues = new object[1, _CellCount]; foreach (DataColumnExportOptions option in Option.VisibleColumnOptions) { if (!option.Visible) continue; _HeadValues[0, _CellIndex] = option.Caption; _CellIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(_CellCount, 1)); range.Value2 = _HeadValues; #endregion #region " 设置表头样式 " range.Font.Bold = true; range.Font.Name = "Georgia"; range.Font.Size = 10; range.RowHeight = 26; range.EntireColumn.AutoFit(); #endregion #region " 填充表格正文内容 " object[,] _RowValues = new object[_RowCount, _CellCount]; _RowIndex = 0; foreach (DataGridViewRow dataRow in Option.DataGridView.Rows) { if (dataRow.IsNewRow) continue; _CellIndex = 0; foreach (DataColumnExportOptions option in Option.VisibleColumnOptions) { _RowValues[_RowIndex, _CellIndex] = dataRow.Cells[option.ColumnName].Value; _CellIndex++; } _RowIndex++; } range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(_CellCount, _RowCount + 1)); range.Value2 = _RowValues; #endregion #region " 设置表格正文样式 " range.Font.Name = "Georgia"; range.Font.Size = 9; range.RowHeight = 18; range.EntireColumn.AutoFit(); #endregion #region " 设置单元格边框样式 " range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(_CellCount, _RowCount + 1)); range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; range.Borders.Weight = Excel.XlBorderWeight.xlThin; range.Borders.Color = Color.Black.ToArgb(); #endregion } #endregion #region " GetExcelCellName " /// /// 取得Excel单元格编号 /// /// /// /// A1,A2,B3,...,AB10,... private string GetExcelCellName(int cellIndex, int rowIndex) { if (cellIndex <= 0 || cellIndex > 256) { throw new Exception("Excel 列索引数值超出范围(1-256)!"); } else if (cellIndex <= 26) { return GetExcelCellName(cellIndex) + rowIndex.ToString(); } else { string retLetter = GetExcelCellName(cellIndex / 26); retLetter += GetExcelCellName(cellIndex % 26); retLetter += rowIndex.ToString(); return retLetter; } } /// /// 取得Excel单元格列编号 /// /// /// A,B,...,AC,... private string GetExcelCellName(int cellIndex) { int i = 1; foreach (string letter in Enum.GetNames(typeof(ExcelColumnLetters))) { if (i == cellIndex) return letter; i++; } throw new Exception("Excel 列索引数值超出范围(1-256)!"); } #endregion } #region " ExcelColumnLetters " 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 #region " DataColumnExportOptions " /// /// 导出数据字段属性选项类 /// class DataColumnExportOptions { private String _ColumnName; private String _Caption; private Boolean _Visible; /// /// 字段名称 /// public String ColumnName { get { return _ColumnName; } set { _ColumnName = value; } } /// /// 字段标题 /// public String Caption { get { return _Caption; } set { _Caption = value; } } /// /// 是否显示（导出） /// public Boolean Visible { get { return _Visible; } set { _Visible = value; } } /// /// 构造函数 /// /// 字段名称 public DataColumnExportOptions(String columnName) : this(columnName, columnName) { } /// /// 构造函数 /// /// 字段名称 /// 字段标题 public DataColumnExportOptions(String columnName, String caption) : this(columnName, caption, true) { } /// /// 构造函数 /// /// 字段名称 /// 字段标题 /// 是否显示（导出） public DataColumnExportOptions(String columnName, String caption, Boolean visible) { this._ColumnName = columnName; this._Caption = caption; this._Visible = visible; } } #endregion #region " DataGridViewExportOptions “ class DataGridViewExportOptions { private DataGridView _DataGridView; private List _ColumnOptions; private List _VisibleColumnOptions; private String _WorkSheetName; /// /// 要导出到DataGridView对象 /// public DataGridView DataGridView { get { return _DataGridView; } set { _DataGridView = value; } } /// /// 导出的字段属性列表 /// public List ColumnOptions { get { return _ColumnOptions; } set { _ColumnOptions = value; } } /// /// 要导出的字段列表（只读） /// public List VisibleColumnOptions { get { return _VisibleColumnOptions; } } /// /// 导出的工作表名称 /// public String WorkSheetName { get { return _WorkSheetName; } set { _WorkSheetName = value; } } /// /// 构造函数 /// /// 要导出到DataGridView对象 public DataGridViewExportOptions(DataGridView dataGridView) : this(dataGridView, null) { } /// /// 构造函数 /// /// 要导出到DataGridView对象 /// 导出的字段属性列表 public DataGridViewExportOptions(DataGridView dataGridView, List columnOptions) : this(dataGridView, columnOptions, null) { } /// /// 构造函数 /// /// 要导出到DataGridView对象 /// 导出的字段属性列表 /// 导出生成的工作表名称 public DataGridViewExportOptions(DataGridView dataGridView, List columnOptions, String workSheetName) { if (dataGridView == null) return; this._DataGridView = dataGridView; if (columnOptions == null) { this._ColumnOptions = new List(); foreach (DataGridViewColumn dataColumn in dataGridView.Columns) this._ColumnOptions.Add(new DataColumnExportOptions(dataColumn.Name, dataColumn.HeaderText, dataColumn.Visible)); } else { this._ColumnOptions = columnOptions; } if (String.IsNullOrEmpty(workSheetName)) this._WorkSheetName = dataGridView.Name; else this._WorkSheetName = workSheetName; this._VisibleColumnOptions = new List(); foreach (DataColumnExportOptions option in this._ColumnOptions) { if (option.Visible) this._VisibleColumnOptions.Add(option); } } } #endregion } |
|
|
