今天上班不是很忙,考虑到经常有人要对 Excel 数据的读写操作,就花了一点功夫,把这部分写成一个模板,以便以后使用。虽然不难,但整理出来感觉还不错,就共享出来供大家参考和学习。
- /// Function: Read and write excel file
- /// Author: Andy Niu
- /// Date: 2011-12-08
- using System;
- using System.Data;
- using System.Linq;
- using System.Text;
- using Microsoft.Office.Interop.Excel;
- namespace MyClass
- {
- public class MyExcel : IDisposable
- {
- private static readonly object missing = System.Reflection.Missing.Value;
- private ApplicationClass myExcel = new ApplicationClass();
- #region Members
- private object updateLinks = missing;
- private object readOnly = missing;
- private object format = missing;
- private object password = missing;
- private object writeResPassword = missing;
- private object ignoreReadOnlyRecommended = missing;
- private object origin = missing;
- private object delimiter = missing;
- private object editable = missing;
- private object notify = missing;
- private object converter = missing;
- private object addToMru = missing;
- private object local = missing;
- private object corruptLoad = missing;
- private object conflictResolution = missing;
- private object textCodepage = missing;
- private object textVisualLayout = missing;
- private object readOnlyRecommended = missing;
- private object createBackup = missing;
- private XlSaveAsAccessMode accessMode = XlSaveAsAccessMode.xlNoChange;
- private Style titleStyle = null;
- #endregion
- #region Property
- public object UpdateLinks
- {
- get { return updateLinks; }
- set { updateLinks = value; }
- }
- public object ReadOnly
- {
- get { return readOnly; }
- set { readOnly = value; }
- }
- public object Format
- {
- get { return format; }
- set { format = value; }
- }
- public object Password
- {
- get { return password; }
- set { password = value; }
- }
- public object WriteResPassword
- {
- get { return writeResPassword; }
- set { writeResPassword = value; }
- }
- public object IgnoreReadOnlyRecommended
- {
- get { return ignoreReadOnlyRecommended; }
- set { ignoreReadOnlyRecommended = value; }
- }
- public object Origin
- {
- get { return origin; }
- set { origin = value; }
- }
- public object Delimiter
- {
- get { return delimiter; }
- set { delimiter = value; }
- }
- public object Editable
- {
- get { return editable; }
- set { editable = value; }
- }
- public object Notify
- {
- get { return notify; }
- set { notify = value; }
- }
- public object Converter
- {
- get { return converter; }
- set { converter = value; }
- }
- public object AddToMru
- {
- get { return addToMru; }
- set { addToMru = value; }
- }
- public object Local
- {
- get { return local; }
- set { local = value; }
- }
- public object CorruptLoad
- {
- get { return corruptLoad; }
- set { corruptLoad = value; }
- }
- public object ConflictResolution
- {
- get { return conflictResolution; }
- set { conflictResolution = value; }
- }
- public object TextCodepage
- {
- get { return textCodepage; }
- set { textCodepage = value; }
- }
- public object TextVisualLayout
- {
- get { return textVisualLayout; }
- set { textVisualLayout = value; }
- }
- public object ReadOnlyRecommended
- {
- get { return readOnlyRecommended; }
- set { readOnlyRecommended = value; }
- }
- public object CreateBackup
- {
- get { return createBackup; }
- set { createBackup = value; }
- }
- public XlSaveAsAccessMode AccessMode
- {
- get { return accessMode; }
- set { accessMode = value; }
- }
- public Style TitleStyle
- {
- get { return titleStyle; }
- set { titleStyle = value; }
- }
- #endregion
- #region Column Name
- private static readonly string[] titles = new string[] { "Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y" };
- private string columnName(int colIndex)
- {
- if (colIndex < 0)
- {
- throw new Exception("Column Index Error");
- }
- string colName = string.Empty;
- // 十進制轉化為26進制
- int reminder = colIndex % 26;
- while (colIndex != 0)
- {
- colName = string.Format("{0}{1}", titles[reminder], colName);
- colIndex = colIndex / 26;
- reminder = colIndex % 26;
- }
- return colName;
- }
- #endregion
- #region Public Method
- /// <summary>
- /// Oepn Excel File
- /// </summary>
- /// <param name="fileName">the fullpath of excel file </param>
- /// <returns>return a instance of workbook</returns>
- public Workbook Open(string fileName)
- {
- if (myExcel == null)
- {
- throw new Exception("Application Error : myExcel is null");
- }
- if (string.IsNullOrEmpty(fileName))
- {
- throw new Exception("Parameter is empty or null");
- }
- try
- {
- return myExcel.Application.Workbooks.Open(fileName, updateLinks, readOnly, format, password, writeResPassword, ignoreReadOnlyRecommended, origin, delimiter, editable, notify, converter, addToMru, local, corruptLoad);
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- /// <summary>
- /// Get data from sheet of wookbook.
- /// </summary>
- /// <param name="sheet">a instance of wooksheet .</param>
- /// <param name="isContainsTitle">a mark means that sheet whether contains title or not .</param>
- /// <returns>ratrun a dataset</returns>
- public DataSet GetDataSet(Worksheet sheet, bool isContainsTitle)
- {
- if (sheet == null)
- {
- throw new Exception("Parameter sheet is null ");
- }
- DataSet ds = new DataSet(sheet.Name);
- try
- {
- // 起始行数
- int startRow = 1;
- if (!isContainsTitle)
- {
- startRow = 0;
- }
- // 起始列数
- int startCol = 1;
- // 总行數(若有標題,則包括标题)
- int rowCount = sheet.UsedRange.Cells.Rows.Count;
- // 总列数
- int columnCount = sheet.UsedRange.Cells.Columns.Count;
- // 获取记录值
- object[,] value = (object[,])sheet.UsedRange.Value2;
- // 新添 Table
- System.Data.DataTable table = new System.Data.DataTable(sheet.Name); ;
- ds.Tables.Add(table);
- // 添加标题
- if (isContainsTitle)
- {
- for (int i = startCol; i <= columnCount; i++)
- {
- if (value[startRow, i] == null)
- {
- throw new Exception("Excel read error : Excel contains empty header columns ");
- }
- table.Columns.Add(value[startRow, i].ToString());
- }
- }
- else
- {
- for (int i = startCol; i <= columnCount; i++)
- {
- table.Columns.Add(columnName(i)); ;
- }
- }
- // 添加数据
- for (int i = startRow + 1; i < rowCount; i++)
- {
- DataRow row = table.NewRow();
- for (int j = startCol; j < columnCount; j++)
- {
- row[j - startCol] = value[i, j];
- }
- table.Rows.Add(row);
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- return ds;
- }
- /// <summary>
- /// Write the first table data of dataset to excel file
- /// </summary>
- /// <param name="data">a instance of dataset</param>
- /// <param name="fileName">special the file path with saving excel file</param>
- public void WriteToExcel(DataSet data, string fileName)
- {
- if (data == null)
- {
- throw new Exception("Parameter data can not be empty");
- }
- // 数据表
- System.Data.DataTable table = data.Tables[0];
- // 添加工作簿
- myExcel.Application.Workbooks.Add(true);
- // 总列数
- int columnCount = table.Columns.Count;
- // 总行数 不包括标题
- int rowCount = table.Rows.Count;
- // 起始行号
- const int startRow = 2;
- // 起始列号
- const int startCol = 1;
- // 标题行号
- const int titleRowIndex = 1;
- // excel 数据域
- Range excelData = myExcel.Cells;
- // 写标题
- for (int colIndex = startCol; colIndex < columnCount; colIndex++)
- {
- excelData[titleRowIndex, colIndex] = table.Columns[colIndex - 1].ColumnName;
- }
- // 写数据
- for (int rowIndex = 0; rowIndex < rowCount - 1; rowIndex++)
- {
- DataRow row = table.Rows[rowIndex];
- for (int colIndex = startCol; colIndex < columnCount; colIndex++)
- {
- if (row[colIndex - 1] == null)
- {
- continue;
- }
- excelData[rowIndex + startRow, colIndex] = row[colIndex - 1].ToString();
- }
- }
- // 设置Title样式
- if (SetTitleStyle != null)
- {
- SetTitleStyle(this, new EventArgs());
- Range title = excelData.get_Range(string.Format("{0}{1}", columnName(startCol), titleRowIndex), string.Format("{0}{1}", columnName(columnCount), titleRowIndex));
- if (titleStyle != null)
- {
- title.Style = titleStyle;
- }
- }
- // 自动调整列宽
- excelData.EntireColumn.AutoFit();
- // 自动调整行高
- excelData.EntireRow.AutoFit();
- // 设置文件格式
- Format = XlFileFormat.xlWorkbookNormal;
- ReadOnlyRecommended = false;
- CreateBackup = false;
- // 保存时,不弹出是否保存的窗口直接保存
- myExcel.DisplayAlerts = false;
- myExcel.ActiveWorkbook.SaveAs(fileName, format, password, writeResPassword, readOnlyRecommended, createBackup, accessMode, conflictResolution, addToMru, textCodepage, textVisualLayout, local);
- myExcel.ActiveWorkbook.Close(false, fileName, false);
- }
- public Microsoft.Office.Interop.Excel.Style AddStyle(string styleName)
- {
- if (string.IsNullOrEmpty(styleName))
- {
- throw new Exception("The style name is empty ");
- }
- return myExcel.Application.ActiveWorkbook.Styles.Add(styleName, missing);
- }
- #endregion
- #region Event
- public delegate void SetTitleStyleHandler(object sender, EventArgs e);
- public event SetTitleStyleHandler SetTitleStyle;
- #endregion
- #region Implemented IDisposable Member
- public void Dispose()
- {
- if (myExcel != null)
- {
- myExcel.DisplayAlerts = false;
- if (myExcel.Application.ActiveWorkbook != null)
- {
- myExcel.Application.ActiveWorkbook.Close(false, null, false);
- }
- myExcel.Quit();
- System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
- myExcel = null;
- GC.Collect();
- }
- }
- #endregion
- }
- }
转载于:https://blog.51cto.com/peaklife/738309