???excel.dll 操作excel得通用类

Question

谁有比较好的用excel.dll操作excel, 比如导出，读取excel得 通用类？

Answer 1

.NET不是提供OFFICE一组COM组件,在项目中引入ＯＦＦＩＣＥ组件就可以了
需要添加COM: Microsoft Excel Object Library.一些例子，可以参考一下
      /// <summary>
        /// 将DataSet里所有数据导入Excel.
        /// 需要添加COM: Microsoft Excel Object Library.
        /// using Excel;
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="ds"></param>
        public void ExportToExcel(string savePath,string templatePath, DataSet ds)
        {
            string strResult = LoadExcelModel(templatePath);
            if (strResult == "")
            {
                //excel.Visible = true;                try
                {
                    WriteExcel(ds,savePath);
  
                }
                catch
                {
                    //MessageBox.Show(ex.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                //MessageBox.Show(strResult, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }        #region 载入模板文件
        private string LoadExcelModel(string filePath)
        {
            //
            try
            {
                //for excel2000
                // excel.Workbooks.Open(filePath,oMissing,oMissing,
                // oMissing, oMissing, oMissing, oMissing,
                // oMissing, oMissing, oMissing, oMissing,
                // oMissing, oMissing);                //for excel2003                excel.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }            return "";
        }
        #endregion        #region 生成excel文件内容
        private void WriteExcel(DataSet ds,string savePath)
        {
            Excel.Workbook xlWorkbook = (Excel.Workbook)(excel.ActiveWorkbook);
            Excel.Worksheet xlWorksheet = (Excel.Worksheet)(xlWorkbook.Sheets[sheetName]);
            //xlWorksheet.Activate();
            xlWorksheet.Unprotect(null);
            ((Excel.Worksheet)(xlWorkbook.Sheets[1])).Activate();            xlWorksheet.Name = sheetName;            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
                {
                    int rowIndex = i + 3;
                    int colIndex = k + 1;
                    xlWorksheet.Cells[rowIndex, colIndex] = ds.Tables[0].Rows[i][k].ToString();
                }
            }            excel.Run("Run", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                    
            //锁定基础数据表
            //for excel2000
            // xlWorksheet.Protect(oMissing,true,true,true,oMissing);
            //for excel2003
            xlWorksheet.Protect(oMissing, true, true, true, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            //调用Excel宏
            excel.Workbooks[1].SaveAs(savePath, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing);
            //保存并关闭
            xlWorkbook.Close( Excel.XlSaveAction.xlDoNotSaveChanges, oMissing, oMissing );
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
            xlWorkbook = null;
            
            
        }
        #endregion

Answer 2

C#版又需要的看下：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Office.Interop;
using Microsoft.Office.Core;
namespace MyWeb
{
/// <summary>
/// ExcelEdit 的摘要说明
/// </summary>
    public class ExcelEdit
    {
        public string mFilename;
        public Excel.Application app;
        public Excel.Workbooks wbs;
        public Excel.Workbook wb;
        public Excel.Worksheets wss;
        public Excel.Worksheet ws;
        public ExcelEdit()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        public void Create()//创建一个Excel对象
        {
            app = new Excel.Application();
            wbs = app.Workbooks;
            wb = wbs.Add(true);
        }
        public void Open(string FileName)//打开一个Excel文件
        {
            app = new Excel.Application();
            wbs = app.Workbooks;
            wb = wbs.Add(FileName);
            //wb = wbs.Open(FileName,  0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,Type.Missing,Type.Missing);
            //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
            mFilename = FileName;
        }
        public Excel.Worksheet GetSheet(string SheetName)//获取一个工作表
        {
            Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName];
            return s;
        }
        public Excel.Worksheet AddSheet(string SheetName)//添加一个工作表
        {
            Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing);
            s.Name = SheetName;
            return s;
        }        public void DelSheet(string SheetName)//删除一个工作表
        {
            ((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
        }
        public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
        {
            Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName];
            s.Name = NewSheetName;
            return s;
        }        public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
        {            Sheet.Name = NewSheetName;            return Sheet;
        }        public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)//ws：要设值的工作表     X行Y列     value   值 
        {
            ws.Cells[x, y] = value;
        }
        public void SetCellValue(string ws, int x, int y, object value)//ws：要设值的工作表的名称 X行Y列 value 值
        {            GetSheet(ws).Cells[x, y] = value;
        }

Answer 3

接上：
 public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)//设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
        {
            name = "宋体";
            size = 12;
            color = Excel.Constants.xlAutomatic;
            HorizontalAlignment = Excel.Constants.xlRight;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
        }        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
        {
            //name = "宋体";
            //size = 12;
            //color = Excel.Constants.xlAutomatic;
            //HorizontalAlignment = Excel.Constants.xlRight;            Excel.Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
        }
        public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格
        {
            ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
        }        public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格
        {
            GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);        }
        public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
        {            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(ws).Cells[startX+i, j + startY] = dt.Rows[i][j].ToString();                }            }        }
        public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二
        {            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {                    ws.Cells[startX+i, j + startY] = dt.Rows[i][j];                }            }        }
        public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一
        {            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {                    GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];                }            }        }
        public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {                    ws.Cells[i + startX, j + startY] = dt.Rows[i][j];                }
            }        }        public void InsertPictures(string Filename, string ws)//插入图片操作一
        {
            GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse,MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置
        }        //public void InsertPictures(string Filename, string ws, int Height, int Width)//插入图片操作二
        //{
        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        //}
        //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)//插入图片操作三
        //{        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
        //}        public void InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType)//插入图表操作
        {
            ChartDataType = Excel.XlRowCol.xlColumns;
            wb.Charts.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing);
            {
                wb.ActiveChart.ChartType = ChartType;
                wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
                wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws);
            }
        }
        public bool Save()//保存文档
        {
            if (mFilename == "")
            {
                return false;
            }
            else
            {
                try
                {
                    wb.Save();
                    return true;
                }                catch (Exception ex)
                {
                    return false;
                }
            }
        }
        public bool SaveAs(object FileName)//文档另存为
        {
            try
            {
                wb.SaveAs(FileName,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                return true;
               
            }            catch (Exception ex)
            {
                return false;
                
            }
        }
        public void Close()//关闭一个Excel对象，销毁对象
        {
            //wb.Save();
            wb.Close(Type.Missing,Type.Missing,Type.Missing);
            wbs.Close();
            app.Quit();
            wb = null;
            wbs = null;
            app = null;
            GC.Collect();
        }
    }
}

Answer 4

非常感谢，有读取excel得通用类么？

Answer 5

chenjinaban(cj) ( ) 信誉：83  2007-7-26 14:38:00  得分: 0  
非常感谢，有读取excel得通用类么？-----------
用ODBC也可以读取，需要在Sheet中预先定义好区域