β

ExcelHelper

布谷中国 275 阅读
C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel= Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;

namespace Com
{

public class ExcelHelper
    {
        private Excel._Application excelApp;
        private string ExcelPath = string.Empty;
        private Excel.WorkbookClass wbclass;
        public ExcelHelper(string _filenPath)
        {
            ExcelPath = _filenPath;
            excelApp = new Excel.Application();
        }
        public DataSet ReadToDataSet()
        {
            object objOpt = System.Reflection.Missing.Value;
            wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(ExcelPath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
            //读取SheetName
            List<string> stTbs = GetSheetNames();
            return LoadDataFromExcel(stTbs);
        }
        /// <summary>
        /// 所有sheet的名称列表
        /// </summary>
        /// <returns></returns>
        private List<string> GetSheetNames()
        {
            List<string> list = new List<string>();
            Excel.Sheets sheets = wbclass.Worksheets;
            string sheetNams = string.Empty;
            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }
        //加载Excel
        private DataSet LoadDataFromExcel(List<string> sheetNames)
        {
            try
            {
                string strConn;
                strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + ExcelPath + “;Extended Properties=’Excel 8.0;HDR=False;IMEX=1′”;
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();

DataSet dsReturn = new DataSet();
                foreach (string tmp in sheetNames)
                {

String sql = “SELECT * FROM  [" + tmp.Trim() + "$]“;//Sheet名称
                    OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                    DataSet OleDsExcle = new DataSet();
                    OleDaExcel.Fill(OleDsExcle, tmp);
                    OleDsExcle.Tables[0].TableName = tmp;
                    dsReturn.Tables.Add(OleDsExcle.Tables[0].Copy());
                }
                OleConn.Close();
                OleConn.Dispose();
                Close();
                return dsReturn;
            }
            catch (Exception err)
            {
                throw new Exception(“读取Excel失败” + err.Message + ExcelPath);
            }
        }
        private void Close()
        {
            excelApp.Quit();
            int generation = System.GC.GetGeneration(excelApp);
            excelApp = null;
            System.GC.Collect(generation);
        }
        /// <summary> 
        /// DataTable 转成 excel表格 
        /// </summary> 
        /// <param name=”dataTable”>数据库数据</param>
        public void DataSetToExcel(DataTable dataTable)
        {

Excel._Workbook workBook;

Excel._Worksheet workSheet;

object misValue = System.Reflection.Missing.Value;

excelApp = new Excel.ApplicationClass();

workBook = excelApp.Workbooks.Add(misValue);

workSheet = (Excel._Worksheet)workBook.ActiveSheet;

int rowIndex = 1;

int colIndex = 0;

//取得标题 
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;

excelApp.Cells[1, colIndex] = col.ColumnName;
            }

//取得表格中的数据 
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;

colIndex = 0;

foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;

excelApp.Cells[rowIndex, colIndex] =

row[col.ColumnName].ToString().Trim();

//设置表格内容居中对齐 
                    workSheet.get_Range(excelApp.Cells[rowIndex, colIndex],

excelApp.Cells[rowIndex, colIndex]).HorizontalAlignment =

Excel.XlVAlign.xlVAlignCenter;
                }
            }

excelApp.Visible = false;

workBook.SaveAs(ExcelPath, Excel.XlFileFormat.xlWorkbookNormal, misValue,

misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,

misValue, misValue, misValue, misValue, misValue);
            dataTable = null;
            workBook.Close(true, misValue, misValue);

}
    }
}

C#
作者:布谷中国
编程资料、编程技巧、源码、素材、蜘蛛软件
原文地址:ExcelHelper, 感谢原作者分享。