c#操作excel

Question

这是我写的一个读写excel文件的类
你可以自己改一下功能都实现了using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Text.RegularExpressions;namespace WindowsFormsApplication3
{
  public class ConvertExecl
  {
    //创建Application对象     
    static Excel.Application _app = null;
    static Workbooks _workbooks = null;
    static _Workbook _workbook = null;
    static Sheets _sheets = null;
    static _Worksheet _workSheet = null;
    object m_objOpt = System.Reflection.Missing.Value;
    string _strResult = null;
    string _strResult2 = null;
    string _file = null;
    public ConvertExecl()
    {    }
    ~ConvertExecl()
    {
      close();
    }
    public void Init()
    {
      close();
      _app = new Excel.ApplicationClass();
      _workbooks = _app.Workbooks;      _app.Visible = false;
      _app.UserControl = true;
      _app.DisplayAlerts = false;
    }
    /// <summary>
    /// 关闭Excle
    /// </summary>
    public void close()
    {
      if (_app == null) return;      _app.Workbooks.Close();
      _app.Quit();
      _app = null;
      _sheets = null;
      _workSheet = null;
      _workbook = null;
      _workbooks = null;      GC.Collect();
    }
    public bool open(string file)
    {
      try
      {
        Init();
        _file = file;
        _workbook = _workbooks.Add(file);
        return true;
      }
      catch (System.Exception e)
      {
        MessageBox.Show(e.Message);
      }      return false;    }
    public void loadSheet()
    {
      int rowCount = 0;
      int i = 0;      ItemManagement.Clear(ItemType.All);      foreach (_Worksheet wsheet in _workbook.Worksheets)
      {
        rowCount = wsheet.UsedRange.Rows.Count;
        if (wsheet.Name.ToLower() == "c")
        {
          ItemC _item = null;
          try
          {
            for (i = 3; i <= rowCount; i++)
            {
              _item = new ItemC();
              _item. = ItemType.ItemC;              _item.id = wsheet.get_Range("A" + i, System.Reflection.Missing.Value).Text.ToString();
              _item.name = wsheet.get_Range("B" + i, System.Reflection.Missing.Value).Text.ToString();
              _item.gender = Convert.ToInt32(wsheet.get_Range("C" + i, System.Reflection.Missing.Value).Text.ToString());
              _item.job = Convert.ToInt32(wsheet.get_Range("D" + i, System.Reflection.Missing.Value).Text.ToString());
              _item.dir = Convert.ToInt32(wsheet.get_Range("E" + i, System.Reflection.Missing.Value).Text.ToString());
              _item.action = Convert.ToInt32(wsheet.get_Range("F" + i, System.Reflection.Missing.Value).Text.ToString());
              _item.actionList = wsheet.get_Range("G" + i, System.Reflection.Missing.Value).Text.ToString();
              _item.delay = Convert.ToInt32(wsheet.get_Range("H" + i, System.Reflection.Missing.Value).Text.ToString());
              _item.playCount = Convert.ToInt32(wsheet.get_Range("I" + i, System.Reflection.Missing.Value).Text.ToString());
              ItemManagement.Add(_item);
            }          }
          catch (Exception)
          {
            _item = null;
          }
        }
      }
    }

    public void writeSheet()
    {
      _workbook = _workbooks.Add(true);
      _sheets = _workbook.Worksheets;      foreach (childClassItem item in Form1.Self.childClass)
      {
        _workSheet = (Worksheet)_sheets.Add(m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        _workSheet.Select(Type.Missing);
        _workSheet.Name = item.flag;        int cols = 1;
        int row = 3;
        _workSheet.Cells[1, cols++] = "编号";
        _workSheet.Cells[1, cols++] = "动画名";        SortedList<string, ItemBase> _list = ItemManagement.GetTable(item.);        switch (item.)
        {
          case ItemType.ItemC:
            _workSheet.Cells[1, cols++] = "职业";
            //_workSheet.Cells[2, cols] = "职业";
            _workSheet.Cells[1, cols++] = "性别";
            //_workSheet.Cells[2, cols] = "性别";
            _workSheet.Cells[1, cols++] = "方向";
            //_workSheet.Cells[2, cols] = "方向";
            _workSheet.Cells[1, cols++] = "动作";
            //_workSheet.Cells[2, cols] = "动作";
            row = 3;            foreach (ItemC _item in _list.Values)
            {
              _workSheet.Cells[row, 1] = _item.id;
              _workSheet.Cells[row, 2] = _item.name;
              _workSheet.Cells[row, 3] = _item.gender;
              _workSheet.Cells[row, 4] = _item.job;
              _workSheet.Cells[row, 5] = _item.dir;
              _workSheet.Cells[row, 6] = _item.action;
              _workSheet.Cells[row, 7] = _item.actionList;
              _workSheet.Cells[row, 8] = _item.delay;
              _workSheet.Cells[row, 9] = _item.playCount;              row++;
            }
            break;
         default:
break;
        }        _workSheet.Cells[1, cols++] = "图片";
        _workSheet.Cells[1, cols++] = "播放间隔";
        _workSheet.Cells[1, cols++] = "播放次数";
      }
      _workSheet = (_Worksheet)_sheets.get_Item(_sheets.Count);
      _workSheet.Delete();
      _workSheet = null;
    }
    public bool saveSheet(string file)
    {
      try
      {
        _app.ActiveWorkbook.SaveAs(file, Excel.XlFileFormat.xlExcel7
            , m_objOpt, m_objOpt, m_objOpt, m_objOpt,
            Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        //System.Diagnostics.Process.Start("explorer.exe", Application.StartupPath.ToString());//打开目录
        return true;
      }
      catch (System.Exception e)
      {
        MessageBox.Show(e.Message);
      }      return false;
    }
  }
}

Answer 1

插入一行或一列： ExcelApp.ActiveSheet.Rows[2].Insert;
参考
http://www.cnblogs.com/litianfei/archive/2008/03/21/1116906.html

Answer 2

    helper.workSheet.Columns[1].Insert
根本就没这个方法嘛

Answer 3

#region 导出到excel
    /// <summary>
    /// 导出到excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            System.Data.DataTable dt = dtSource;
            string TemplatePath = Server.MapPath(@"Template\Third.xls");
            string ExportPath = Server.MapPath("") + @"\ExportReports";            if (!Directory.Exists(ExportPath))
            {
                Directory.CreateDirectory(ExportPath);
            }            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();            if (app == null)
            {
                return;
            }
            app.Visible = false;
            app.UserControl = true;            Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(TemplatePath);
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
            Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);            if (worksheet == null)
            {
                return;
            }            int i = 1;            for (; i <= dt.Rows.Count; i++)
            {
                int j = 0;
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["Month"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["PatientTypeName"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["OutCount"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["BedDayCount"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["AverageDay"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["TotalCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["BedCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["MedicineCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["CureCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["CheckUpCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["VerifyCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["PerAverageCharge"].ToString().Trim();
                worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["DayAverageCharge"].ToString().Trim();
            }            string str = DateTime.Now.ToString("yyyyMMddHHmmss");
            string strExportPath = ExportPath + @"\" + str + ".xls";            workbook.SaveAs(strExportPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);            app.Quit();            app = null;            Response.Redirect("DownLoad.aspx?Path=" + strExportPath, false);
        }
        catch
        {
            Response.Write("<script>alert('导出失败！！')</script>");
            return;
        }
    }
    #endregion
我写的一个写入excel方法，你改下用吧