国产香蕉片,国产自拍偷拍片,国产主播网

.Net如何生成Excel文件（VS.net2003 附代碼）

原創(chuàng)|其它|編輯：郝浩|2012-10-11 09:42:39.000|閱讀 420 次

概述：工作中要用，所以研究了一下。簡單的寫一些。代碼是完整的。以下為.NET生成Excel文件的全部代碼，并包含一定操作。其中引用到一個枚舉public enum ExcelAlign {Left,Right,Center};請自行定義在合適處并修改代碼中相應(yīng)部分。

# 界面/圖表報(bào)表/文檔/IDE等千款熱門軟控件火熱銷售中 >>

工作中要用，所以研究了一下。簡單的寫一些。代碼是完整的。

以下為.NET生成Excel文件的全部代碼，并包含一定操作。其中引用到一個枚舉public enum ExcelAlign {Left,Right,Center};請自行定義在合適處并修改代碼中相應(yīng)部分。

注：本代碼在windows2000 server office2003環(huán)境下測試通過，不會遺留Excel進(jìn)程；使用VS.net 2003編寫

------------------------

using System;
using System.Collections;
using Excel;

namespace JointSkyLibrary.JExcel
{
/// <summary>
/// 對Excel進(jìn)行操作的類。
/// </summary>
public class JointExcel
{
  #region 私有成員
  private Excel.ApplicationClass m_objExcel;//Excel應(yīng)用程序?qū)ο?
  private Excel.Workbooks m_objBooks;//Excel的Books對象
  private Excel.Workbook m_objBook;//當(dāng)前Book對象
  private Excel.Worksheet m_objSheet;//當(dāng)前Sheet對象
  private Excel.Range m_Range;//當(dāng)前Range對象
  private System.Reflection.Missing miss = System.Reflection.Missing.Value;//空數(shù)據(jù)變量
  private Excel.Font m_Font;//當(dāng)前單元格的字體屬性對象
  private Excel.Borders m_Borders;//當(dāng)前單元格或者區(qū)域的邊框?qū)傩詫ο?

  //單元格的四條邊框?qū)ο?
  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屬性對象

  //單元格列號數(shù)組
  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中時，請?jiān)赪eb.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>
  /// 選定相應(yīng)單元格
  /// </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>
  /// 選擇相應(yīng)的區(qū)域
  /// </summary>
  /// <param name="startColNum">起始單元格列號</param>
  /// <param name="startRowNum">起始單元格行號</param>
  /// <param name="endColNum">結(jié)束單元格列號</param>
  /// <param name="endRowNum">結(jié)束單元格行號</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("沒有設(shè)定單元格或者區(qū)域");
   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>
  /// 合并選定區(qū)域后給其附值
  /// </summary>
  /// <param name="startRow">起始行號</param>
  /// <param name="startCol">起始列號</param>
  /// <param name="endRow">結(jié)束行號</param>
  /// <param name="endCol">結(jié)束列號</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 設(shè)定單元格對齊方式
  /// <summary>
  /// 設(shè)定單元格中文字的對齊方式
  /// </summary>
  /// <param name="ea">對齊方式</param>
  public void SetHorizontal(JointEmun.ExcelAlign ea)
  {
   if(m_Range == null) throw new System.Exception("沒有設(shè)定單元格或者區(qū)域");
   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>
  /// 設(shè)定單元格中文字的對齊方式
  /// </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>
  /// 設(shè)定選定區(qū)域的對齊方式
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</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 設(shè)置行高和列寬
  /// <summary>
  /// 設(shè)置列寬
  /// </summary>
  /// <param name="columnWidth">列寬度</param>
  public void SetColumnWidth(float columnWidth)
  {
   m_Range.ColumnWidth = columnWidth;
  }
  /// <summary>
  /// 設(shè)置列寬
  /// </summary>
  /// <param name="columnIndex">列號</param>
  /// <param name="columnWidth">列寬度</param>
  public void SetColumnWidth(int columnIndex, float columnWidth)
  {
   SetRange(columnIndex,0);
   m_Range.ColumnWidth = columnWidth;
  }
  /// <summary>
  /// 設(shè)置行高
  /// </summary>
  /// <param name="rowHeigh">行寬度</param>
  public void SetRowHeigh(float rowHeigh)
  {
   m_Range.RowHeight = rowHeigh;
  }
  /// <summary>
  /// 設(shè)置行高
  /// </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>
  /// 將選定區(qū)域中的單元格合并
  /// </summary>
  public void Merge()
  {
   m_Range.Merge(null);
  }
  /// <summary>
  /// 將選定區(qū)域中的單元格合并
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</param>
  public void Merge(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
  {
   SetRange(startColumnIndex ,startRowIndex , endColumnIndex, endRowIndex);
   m_Range.Merge(null);
  }
  #endregion

  #region 設(shè)置字體名稱、大小
  /// <summary>
  /// 設(shè)置區(qū)域內(nèi)的字體
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</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>
  /// 設(shè)置區(qū)域內(nèi)的字號（文字大小）
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</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>
  /// 設(shè)置區(qū)域內(nèi)的字體以及字號
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</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>
  /// 設(shè)置單元格的字體和字號
  /// </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>
  /// 設(shè)置單元格的字體
  /// </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>
  /// 設(shè)置單元格的字號
  /// </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>
  /// 設(shè)定字體
  /// </summary>
  /// <param name="fontName">字體</param>
  public void SetFont(string fontName)
  {
   m_Font.Name = fontName;
  }
  /// <summary>
  /// 設(shè)定字號
  /// </summary>
  /// <param name="fontSize">字號</param>
  public void SetFont(int fontSize)
  {
   m_Font.Size = fontSize;
  }
  /// <summary>
  /// 設(shè)定字體和字號
  /// </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 設(shè)置單元格邊框
  /// <summary>
  /// 設(shè)定單元格邊框
  /// </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>
  /// 設(shè)定單元格邊框
  /// </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>
  /// 設(shè)定選定區(qū)域內(nèi)的單元格邊框
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</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 設(shè)置單元格、行、列自適應(yīng)寬度、高度
  /// <summary>
  /// 選定區(qū)域所有單元格自適應(yīng)列寬行高
  /// </summary>
  /// <param name="startRowIndex">起始行號</param>
  /// <param name="startColumnIndex">起始列號</param>
  /// <param name="endRowIndex">結(jié)束行號</param>
  /// <param name="endColumnIndex">結(jié)束列號</param>
  /// <param name="rowAuto">行是否自適應(yīng)</param>
  /// <param name="ColumnAuto">列是否自適應(yīng)</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>
  /// 選定單元格自適應(yīng)列寬行高
  /// </summary>
  /// <param name="rowIndex">行號</param>
  /// <param name="columnIndex">列號</param>
  /// <param name="rowAuto">行是否自適應(yīng)</param>
  /// <param name="ColumnAuto">列是否自適應(yīng)</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>
  /// 選定單元格自適應(yīng)列寬行高
  /// </summary>
  /// <param name="rowAuto">行是否自適應(yīng)</param>
  /// <param name="ColumnAuto">列是否自適應(yīng)</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操作結(jié)束

  //使用完成后注銷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對象的操作，需要注意對象釋放的過程；需要將所有在操作過程中用到的對象全部釋放，包括隱性使用到的

標(biāo)簽：

本站文章除注明轉(zhuǎn)載外，均為本站原創(chuàng)或翻譯。歡迎任何形式的轉(zhuǎn)載，但請務(wù)必注明出處、不得修改原文相關(guān)鏈接，如果存在內(nèi)容上的異議請郵件反饋至chenjj@fc6vip.cn

文章轉(zhuǎn)載自：網(wǎng)絡(luò)轉(zhuǎn)載

上一篇：如何使用JExcel讀寫Excel .xls文件（附代碼）下一篇：巧用JExcel實(shí)現(xiàn)動態(tài)Excel打印