分享：一个Excel操作类

小技巧：Excel十分的强大，有很多的操作没有详细的文档是很难开发应用程序的。这时候，我想到了一个非常简易的办法就是录制宏。将相应的操作录制后保存下来，看看他是怎么操作的。如，单元格合并啊，宽度字体等，八九不离十，你总能知道怎么写。

  1/**//// Excel导出类
  2/// 需要Office2003支持
  3
  4using System;
  5using System.Data;
  6using Microsoft.Office.Interop.Excel;
  7
  8namespace ExcelExport
  9{
 10    public class ExportExcel
 11    {
 12        私有成员变量#region 私有成员变量
 13        private Microsoft.Office.Interop.Excel.Application app;
 14        private Workbook workbook = null;
 15        private Worksheet worksheet = null;
 16        private int rowCount = 0;
 17        private string[] columns = new string[]
 18                        { "", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", 
 19                            "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
 20        #endregion
 21
 22        构造函数#region 构造函数
 23        public ExportExcel()
 24        {
 25            app = new Microsoft.Office.Interop.Excel.Application();
 26            workbook = app.Workbooks.Add(Type.Missing);
 27            worksheet = (Worksheet)workbook.Sheets[1];
 28        }
 29
 30        public ExportExcel(string fileName)
 31        {
 32            app = new Microsoft.Office.Interop.Excel.Application();
 33            try
 34            {
 35                workbook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 36                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 37                                                Type.Missing, Type.Missing, Type.Missing);
 38                worksheet = (Worksheet)workbook.Sheets[1];
 39            }
 40            catch (Exception ex)
 41            {
 42                throw new Exception(ex.Message, ex);
 43            }
 44        }
 45        #endregion
 46
 47        文档操作#region 文档操作
 48        /**//// <summary>
 49        /// 打开Excel文档
 50        /// </summary>
 51        /// <param name="fileName">文件名称</param>
 52        public void Open(string fileName)
 53        {
 54            try
 55            {
 56                workbook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 57                                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 58                                                Type.Missing, Type.Missing, Type.Missing);
 59                worksheet = (Worksheet)workbook.Sheets[1];
 60            }
 61            catch (Exception ex)
 62            {
 63                throw new Exception(ex.Message, ex);
 64            }
 65        }
 66
 67        /**//// <summary>
 68        /// 保存Excel文档
 69        /// </summary>
 70        /// <param name="fileName">文件名称</param>
 71        public void SaveAs(string fileName)
 72        {
 73            try
 74            {
 75                if (System.IO.File.Exists(fileName))
 76                    System.IO.File.Delete(fileName);
 77                worksheet.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 78            }
 79            catch (Exception ex)
 80            {
 81                throw new Exception(ex.Message, ex);
 82            }
 83        }
 84
 85        /**//// <summary>
 86        /// 关闭应用程序
 87        /// </summary>
 88        public void CloseApp()
 89        {
 90            workbook.Close(null, null, null);
 91            app.Workbooks.Close();
 92            app.Application.Quit();
 93            app.Quit();
 94            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
 95            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
 96            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
 97            worksheet = null;
 98            workbook = null;
 99            app = null;
100        }
101        #endregion
102
103        添加数据#region 添加数据
104        /**//// <summary>
105        /// 设置当前列下的所有数据
106        /// </summary>
107        public void AddData(System.Data.DataTable table, int startRow, int startColumn)
108        {
109            int columnCount = table.Columns.Count;
110            rowCount = table.Rows.Count;
111
112            int saveStartColumn = startColumn;
113            for (int i = 0; i < rowCount; i++)
114            {
115                DataRow dr = table.Rows[i];
116                for (int j = 0; j < columnCount; j++)
117                {
118                    Range rg = (Range)worksheet.Cells[startRow, startColumn];
119                    rg.Borders.LineStyle = Constants.xlSolid;
120                    rg.VerticalAlignment = Constants.xlCenter; 
121                    rg.NumberFormatLocal = "0.00_ ";
122                    rg.Value2 = dr[j];
123                    startColumn++;
124                }
125                startRow++;
126                startColumn = saveStartColumn;
127            }
128        }
129
130        /**//// <summary>
131        /// 添加单元格数据
132        /// </summary>
133        /// <param name="row">行编号</param>
134        /// <param name="column">列编号</param>
135        /// <param name="value">添加值</param>
136        public void SetCell(int row, int column, params object[] value)
137        {
138            SetCell(row, column, 1, value);
139        }
140
141        /**//// <summary>
142        /// 添加单元格数据
143        /// </summary>
144        /// <param name="row">行编号</param>
145        /// <param name="column">列编号</param>
146        /// <param name="columnOffset">偏移量</param>
147        /// <param name="value">添加值</param>
148        public void SetCell(int row, int column, int columnOffset, params object[] value)
149        {
150            int offset = 0;
151
152            Range first = worksheet.Cells[row, column] as Range;
153            for (int i = 0; i < value.Length; i++)
154            {
155                Range rg = first.get_Offset(0, offset);
156                rg.Borders.LineStyle = Constants.xlSolid;
157                rg.VerticalAlignment = Constants.xlCenter;
158                rg.NumberFormatLocal = "0.00_ "; 
159                rg.Value2 = value[i];
160
161                offset += columnOffset;
162            }
163        }
164
165        #endregion
166
167        添加公式#region 添加公式
168        /**//// <summary>
169        /// 添加公式
170        /// </summary>
171        /// <param name="rowIndex">行编号</param>
172        /// <param name="columnIndex">列编号</param>
173        /// <param name="formula">公式字符串</param>
174        public void SetFormula(int rowIndex, int columnIndex, string formula)
175        {
176            Range rg = (Range)worksheet.Cells[rowIndex, columnIndex];
177            rg.Formula = formula;
178            rg.NumberFormatLocal = "0.00_ ";
179            rg.Borders.LineStyle = Constants.xlSolid;
180            rg.VerticalAlignment = Constants.xlCenter;
181        }
182        #endregion
183
184        设置格式#region 设置格式
185        /**//// <summary>
186        /// 全文填充颜色
187        /// </summary>
188        /// <param name="colorIndex">颜色编号</param>
189        public void FillSheet(int colorIndex)
190        {
191            worksheet.Cells.Select();
192            worksheet.Cells.Interior.ColorIndex = colorIndex;
193            worksheet.Cells.Interior.Pattern = Constants.xlSolid;
194            worksheet.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
195        }
196
197        /**//// <summary>
198        /// 合并单元格
199        /// </summary>
200        /// <param name="startRow">起始行</param>
201        /// <param name="startColumn">起始列</param>
202        /// <param name="endRow">结束行</param>
203        /// <param name="endColumn">结束列</param>
204        public void UnionCells(int startRow, int startColumn, int endRow, int endColumn)
205        {
206            string startCell = ConvertToColumn(startRow, startColumn);
207            string endCell = ConvertToColumn(endRow, endColumn);
208            Range range = worksheet.get_Range(startCell, endCell);
209            range.MergeCells = true;
210            range.Borders.LineStyle = Constants.xlSolid;
211            range.HorizontalAlignment = Constants.xlCenter;
212            range.VerticalAlignment = Constants.xlCenter;
213        }
214
215        /**//// <summary>
216        /// 合并单元格
217        /// </summary>
218        /// <param name="startCell">起始单元格</param>
219        /// <param name="endCell">结束单元格</param>
220        public void UnionCells(string startCell, string endCell)
221        {
222            Range range = worksheet.get_Range(startCell, endCell);
223            range.MergeCells = true;
224            range.Borders.LineStyle = Constants.xlSolid;
225            range.HorizontalAlignment = Constants.xlCenter;
226            range.VerticalAlignment = Constants.xlCenter;
227        }
228
229
230        /**//// <summary>
231        /// 设置单元格格式
232        /// </summary>
233        /// <param name="row">行编号</param>
234        /// <param name="column">列编号</param>
235        /// <param name="widht">单元格宽度</param>
236        /// <param name="height">单元格高度</param>
237        /// <param name="fontsize">单元格字体大小</param>
238        /// <param name="format">单元格水平对齐方式</param>
239        public void CellFormat(int row, int column, int width, int height, int fontsize, int format)
240        {
241            Range rg = worksheet.Cells[row, column] as Range;
242
243            if (width != -1)
244                rg.ColumnWidth = width;
245            if (height != -1)
246                rg.RowHeight = height;
247            
248            if (fontsize != -1)
249                rg.Font.Size = fontsize;
250            
251            if (format == 0)
252                rg.HorizontalAlignment = Constants.xlCenter;
253            else if (format == 1)
254                rg.HorizontalAlignment = Constants.xlLeft;
255            else if(format == 2)
256                rg.HorizontalAlignment = Constants.xlRight;
257        }
258
259        public string ConvertToColumn(int row, int column)
260        {
261            if (row < 1 || column < 1) throw new Exception("索引超界, 索引编号必须大于0");
262
263            int integer = column / 26;
264            int f = 0;
265            if (integer > 0)
266                f = column % 26;
267
268            if (integer == 0)
269            {
270                integer = column % 26;
271            }
272            else if (f == 0)
273            {
274                integer -= 1;
275                f = 26;
276            }
277            string temp = "";
278            if (f == 0)
279                temp = columns[integer];
280            else
281                temp = columns[integer] + columns[f];
282
283            return temp + row.ToString();
284        }
285        #endregion
286
287    }
288}