原创|其它|编辑:郝浩|2012-10-11 09:42:39.000|阅读 396 次
概述:工作中要用,所以研究了一下。简单的写一些。代码是完整的。以下为.NET生成Excel文件的全部代码,并包含一定操作。其中引用到一个枚举public enum ExcelAlign {Left,Right,Center};请自行定义在合适处并修改代码中相应部分。
# 慧都年终大促·界面/图表报表/文档/IDE等千款热门软控件火热促销中 >>
工作中要用,所以研究了一下。简单的写一些。代码是完整的。
以下为.NET生成Excel文件的全部代码,并包含一定操作。其中引用到一个枚举public enum ExcelAlign {Left,Right,Center};请自行定义在合适处并修改代码中相应部分。
注:本代码在windows2000 server office2003环境下测试通过,不会遗留Excel进程;使用VS.net 2003编写
------------------------
using System; using System.Collections; using Excel; namespace JointSkyLibrary.JExcel { /// <summary> /// 对Excel进行操作的类。 /// </summary> public class JointExcel { #region 私有成员 private Excel.ApplicationClass m_objExcel;//Excel应用程序对象 private Excel.Workbooks m_objBooks;//Excel的Books对象 private Excel.Workbook m_objBook;//当前Book对象 private Excel.Worksheet m_objSheet;//当前Sheet对象 private Excel.Range m_Range;//当前Range对象 private System.Reflection.Missing miss = System.Reflection.Missing.Value;//空数据变量 private Excel.Font m_Font;//当前单元格的字体属性对象 private Excel.Borders m_Borders;//当前单元格或者区域的边框属性对象 //单元格的四条边框对象 private Excel.Border m_BorderTop; private Excel.Border m_BorderBottom; private Excel.Border m_BorderLeft; private Excel.Border m_BorderRight; private Excel.Range m_cellRange;//单元格Range对象,用来取得对象的Rows和Columns属性对象 //单元格列号数组 private string[] m_colString = new string[26] {"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","Z"}; #endregion /// <summary> /// 本类使用在web application中时,请在Web.Config中添加 /// <identity impersonate="true"/> /// </summary> public JointExcel() { m_objExcel = new Excel.ApplicationClass(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet; } ~JointExcel() { //释放所有Com对象 if(m_cellRange != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_cellRange); if(m_BorderTop != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderTop); if(m_BorderBottom != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderBottom); if(m_BorderLeft != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderLeft); if(m_BorderRight != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderRight); if(m_Borders != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Borders); if(m_Font != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Font); if(m_Range != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Range); if(m_objSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); if(m_objBook != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); if(m_objBooks != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); if(m_objExcel != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); GC.Collect(); } #region 选定单元格 private string GetCell(int ColNum,int RowNum) { int row = RowNum +1; if(ColNum<0 || ColNum >255) { throw new Exception("行号错误"); } int i0,i1 = 0; i0 = Math.DivRem(ColNum,25,out i1); if(i0==0 && i1==0) { return "A"+row.ToString(); } else if(i0==0 && i1>0) { return m_colString[i1]+row.ToString(); } else { return m_colString[i0]+m_colString[i1]+row.ToString(); } } /// <summary> /// 选定相应单元格 /// </summary> /// <param name="ColNum">int 列号</param> /// <param name="RowNum">int 行号</param> public void SetRange(int ColNum,int RowNum) { m_Range = m_objSheet.get_Range((object)GetCell(ColNum,RowNum),miss); m_Font = m_Range.Font; m_Borders = m_Range.Borders; m_BorderTop = m_Borders[Excel.XlBordersIndex.xlEdgeTop]; m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom]; m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft]; m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight]; m_cellRange = m_Range; } /// <summary> /// 选择相应的区域 /// </summary> /// <param name="startColNum">起始单元格列号</param> /// <param name="startRowNum">起始单元格行号</param> /// <param name="endColNum">结束单元格列号</param> /// <param name="endRowNum">结束单元格行号</param> public void SetRange(int startColNum,int startRowNum,int endColNum,int endRowNum) { m_Range = m_objSheet.get_Range((object)GetCell(startColNum,startRowNum),(object)GetCell(endColNum,endRowNum)); m_Font = m_Range.Font; m_Borders = m_Range.Borders; m_BorderTop = m_Borders[Excel.XlBordersIndex.xlEdgeTop]; m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom]; m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft]; m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight]; m_cellRange = m_Range; } #endregion //开始具体的Excel操作 #region 给单元格附值 /// <summary> /// 给选定单元格附值 /// </summary> /// <param name="value">值</param> public void SetCellValue(string value) { if(m_Range == null) throw new System.Exception("没有设定单元格或者区域"); m_Range.Value2 = value; } /// <summary> /// 给选定单元格附值 /// </summary> /// <param name="col">列号</param> /// <param name="row">行号</param> /// <param name="value">值</param> public void SetCellValue(int row,int col,string value) { SetRange(col,row); m_Range.Value2 = value; } /// <summary> /// 合并选定区域后给其附值 /// </summary> /// <param name="startRow">起始行号</param> /// <param name="startCol">起始列号</param> /// <param name="endRow">结束行号</param> /// <param name="endCol">结束列号</param> /// <param name="value">值</param> public void SetCellValue(int startRow,int startCol,int endRow,int endCol,string value) { Merge(startRow,startCol,endRow,endCol); m_Range.Value2 = value; } #endregion #region 设定单元格对齐方式 /// <summary> /// 设定单元格中文字的对齐方式 /// </summary> /// <param name="ea">对齐方式</param> public void SetHorizontal(JointEmun.ExcelAlign ea) { if(m_Range == null) throw new System.Exception("没有设定单元格或者区域"); switch(ea.ToString()) { case "Left" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; case "Right" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; break; case "center" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; default: m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; } } /// <summary> /// 设定单元格中文字的对齐方式 /// </summary> /// <param name="rowIndex">单元格行号</param> /// <param name="columnIndex">单元格列号</param> /// <param name="ea">对齐方式</param> public void SetHorizontal(int rowIndex, int columnIndex,JointEmun.ExcelAlign ea) { SetRange(columnIndex,rowIndex); switch(ea.ToString()) { case "Left" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; case "Right" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; break; case "center" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; default: m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; } } /// <summary> /// 设定选定区域的对齐方式 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> /// <param name="ea">对齐方式</param> public void SetHorizontal(int startRowIndex, int startColumnIndex,int endRowIndex, int endColumnIndex,JointEmun.ExcelAlign ea) { SetRange(startColumnIndex,startRowIndex,endColumnIndex,endRowIndex); switch(ea.ToString()) { case "Left" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; case "Right" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; break; case "center" : m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; default: m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; break; } } #endregion #region 设置行高和列宽 /// <summary> /// 设置列宽 /// </summary> /// <param name="columnWidth">列宽度</param> public void SetColumnWidth(float columnWidth) { m_Range.ColumnWidth = columnWidth; } /// <summary> /// 设置列宽 /// </summary> /// <param name="columnIndex">列号</param> /// <param name="columnWidth">列宽度</param> public void SetColumnWidth(int columnIndex, float columnWidth) { SetRange(columnIndex,0); m_Range.ColumnWidth = columnWidth; } /// <summary> /// 设置行高 /// </summary> /// <param name="rowHeigh">行宽度</param> public void SetRowHeigh(float rowHeigh) { m_Range.RowHeight = rowHeigh; } /// <summary> /// 设置行高 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="rowHeigh">行宽度</param> public void SetRowHeigh(int rowIndex, float rowHeigh) { SetRange(0,rowIndex); m_Range.RowHeight = rowHeigh; } #endregion #region 合并单元格 /// <summary> /// 将选定区域中的单元格合并 /// </summary> public void Merge() { m_Range.Merge(null); } /// <summary> /// 将选定区域中的单元格合并 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> public void Merge(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex) { SetRange(startColumnIndex ,startRowIndex , endColumnIndex, endRowIndex); m_Range.Merge(null); } #endregion #region 设置字体名称、大小 /// <summary> /// 设置区域内的字体 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> /// <param name="fontName">字体名称</param> public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName) { SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex); m_Font.Name = fontName; } /// <summary> /// 设置区域内的字号(文字大小) /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> /// <param name="fontSize">字号</param> public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, int fontSize) { SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex); m_Font.Size = fontSize; } /// <summary> /// 设置区域内的字体以及字号 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> /// <param name="fontName">字体名称</param> /// <param name="fontSize">字号</param> public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, string fontName, int fontSize) { SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex); m_Font.Name = fontName; m_Font.Size = fontSize; } /// <summary> /// 设置单元格的字体和字号 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="columnIndex">列号</param> /// <param name="fontName">字体</param> /// <param name="fontSize">字号</param> public void SetFont(int rowIndex, int columnIndex, string fontName, int fontSize) { SetRange(columnIndex, rowIndex); m_Font.Name = fontName; m_Font.Size = fontSize; } /// <summary> /// 设置单元格的字体 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="columnIndex">列号</param> /// <param name="fontName">字体</param> public void SetFont(int rowIndex, int columnIndex, string fontName ) { SetRange(columnIndex, rowIndex); m_Font.Name = fontName; } /// <summary> /// 设置单元格的字号 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="columnIndex">列号</param> /// <param name="fontSize">字号</param> public void SetFont(int rowIndex, int columnIndex, int fontSize) { SetRange(columnIndex, rowIndex); m_Font.Size = fontSize; } /// <summary> /// 设定字体 /// </summary> /// <param name="fontName">字体</param> public void SetFont(string fontName) { m_Font.Name = fontName; } /// <summary> /// 设定字号 /// </summary> /// <param name="fontSize">字号</param> public void SetFont(int fontSize) { m_Font.Size = fontSize; } /// <summary> /// 设定字体和字号 /// </summary> /// <param name="fontName">字体</param> /// <param name="fontSize">字号</param> public void SetFont(string fontName,int fontSize) { m_Font.Name = fontName; m_Font.Size = fontSize; } #endregion #region 设置单元格边框 /// <summary> /// 设定单元格边框 /// </summary> public void SetBorder() { m_Borders.LineStyle = 1; m_BorderTop.Weight = Excel.XlBorderWeight.xlMedium; m_BorderBottom.Weight = Excel.XlBorderWeight.xlMedium; m_BorderLeft.Weight = Excel.XlBorderWeight.xlMedium; m_BorderRight.Weight = Excel.XlBorderWeight.xlMedium; } /// <summary> /// 设定单元格边框 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="columnIndex">列号</param> public void SetBorder(int rowIndex, int columnIndex) { SetRange(columnIndex, rowIndex); m_Borders.LineStyle = 1; m_BorderTop.Weight = Excel.XlBorderWeight.xlMedium; m_BorderBottom.Weight = Excel.XlBorderWeight.xlMedium; m_BorderLeft.Weight = Excel.XlBorderWeight.xlMedium; m_BorderRight.Weight = Excel.XlBorderWeight.xlMedium; } /// <summary> /// 设定选定区域内的单元格边框 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> public void SetBorder(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex) { SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex); m_Borders.LineStyle = 1; m_BorderTop.Weight = Excel.XlBorderWeight.xlMedium; m_BorderBottom.Weight = Excel.XlBorderWeight.xlMedium; m_BorderLeft.Weight = Excel.XlBorderWeight.xlMedium; m_BorderRight.Weight = Excel.XlBorderWeight.xlMedium; } #endregion #region 设置单元格、行、列自适应宽度、高度 /// <summary> /// 选定区域所有单元格自适应列宽行高 /// </summary> /// <param name="startRowIndex">起始行号</param> /// <param name="startColumnIndex">起始列号</param> /// <param name="endRowIndex">结束行号</param> /// <param name="endColumnIndex">结束列号</param> /// <param name="rowAuto">行是否自适应</param> /// <param name="ColumnAuto">列是否自适应</param> public void SetCellAutoFit(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, bool rowAuto, bool ColumnAuto) { SetRange(startColumnIndex,startRowIndex ,endColumnIndex , endRowIndex); if (rowAuto) { m_cellRange = m_Range.Rows; m_cellRange.AutoFit(); } if (ColumnAuto) { m_cellRange = m_Range.Columns; m_cellRange.AutoFit(); } } /// <summary> /// 选定单元格自适应列宽行高 /// </summary> /// <param name="rowIndex">行号</param> /// <param name="columnIndex">列号</param> /// <param name="rowAuto">行是否自适应</param> /// <param name="ColumnAuto">列是否自适应</param> public void SetCellAutoFit(int rowIndex, int columnIndex, bool rowAuto, bool ColumnAuto) { SetRange(columnIndex, rowIndex); if (rowAuto) { m_cellRange = m_Range.Rows; m_cellRange.AutoFit(); } if (ColumnAuto) { m_cellRange = m_Range.Columns; m_cellRange.AutoFit(); } } /// <summary> /// 选定单元格自适应列宽行高 /// </summary> /// <param name="rowAuto">行是否自适应</param> /// <param name="ColumnAuto">列是否自适应</param> public void SetCellAutoFit(bool rowAuto, bool ColumnAuto) { if (rowAuto) { m_cellRange = m_Range.Rows; m_cellRange.AutoFit(); } if (ColumnAuto) { m_cellRange = m_Range.Columns; m_cellRange.AutoFit(); } } #endregion #region 保存文件 public void SaveAs(string fileName) { m_objBook.SaveAs(fileName, miss, miss, miss, miss,miss, Excel.XlSaveAsAccessMode.xlNoChange, miss,miss,miss, miss, miss); } #endregion //Excel操作结束 //使用完成后注销Excel Com对象 /// <summary> /// 释放Excel Com对象 /// </summary> public void Dispose() { //释放所有对象 m_objBook.Close(false, miss, miss); m_objBooks.Close(); m_objExcel.Quit(); //释放所有Com对象 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_cellRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderTop); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderBottom); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderLeft); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderRight); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Borders); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Font); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Range); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); GC.Collect(); } } }
对Excel对象的操作,需要注意对象释放的过程;需要将所有在操作过程中用到的对象全部释放,包括隐性使用到的
本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn
文章转载自:网络转载