导入导出excel

调用Office组件

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using ZG.ERP.App.AppPublic;
using ZG.ERP.Common.SysParameter;
using ZG.ERP.Common.Data;
using ZG.ERP.Common.Utility;
using System.IO;
using System.Diagnostics;
using System.Data.OleDb;
using System.Runtime.InteropServices;



 //打开并读取Excel模板
            Microsoft.Office.Interop.Excel.Application application; //这是一个客户端
            Microsoft.Office.Interop.Excel.Workbooks workbooks; //所有工作薄
            Microsoft.Office.Interop.Excel.Worksheet worksheet;//工作表
            Microsoft.Office.Interop.Excel.Workbook workbook; //所用到的工作表

            application = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                workbooks = application.Workbooks;
                workbook = workbooks.Open(
                        filePath, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];

                //dtExcelData.Columns.Add("SKU编号");
                dtExcelData.Columns.Add("品名");
                dtExcelData.Columns.Add("材质");
                dtExcelData.Columns.Add("钢厂");
                dtExcelData.Columns.Add("规格");
                dtExcelData.Columns.Add("预定数量(件)");
                dtExcelData.Columns.Add("预定重量(吨)");
                dtExcelData.Columns.Add("预付单价(元)");
                dtExcelData.Columns.Add("预付款小计(元)");              
                dtExcelData.Columns.Add("备注");

              
                string productName;
                string material;
                string factory;
                string specification;
                string FLAN_num;
                string Quantity;
                string FLAN_price;
                string totalPiece;                
                string remark;

                //循环读取Excel内容放入DataTable
                for (int i = 2; i <= worksheet.UsedRange.Rows.Count; i++)
                {
                    if (((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2 == null)
                    {
                        continue;
                    }                 
                    productName = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 1]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 1]).Value2.ToString() : string.Empty;
                    material = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2.ToString() : string.Empty;
                    factory = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 3]).Value2 != null ?
                      ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 3]).Value2.ToString() : string.Empty;
                    specification = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 4]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 4]).Value2.ToString() : string.Empty;
                    Quantity = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 5]).Value2 != null ?
                       ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 5]).Value2.ToString() : string.Empty;
                    FLAN_num = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 6]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 6]).Value2.ToString() : string.Empty;
                    FLAN_price = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 7]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 7]).Value2.ToString() : string.Empty;
                    totalPiece = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 8]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 8]).Value2.ToString() : string.Empty;
                    remark = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 9]).Value2 != null ?
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 9]).Value2.ToString() : string.Empty;

                    dtExcelData.Rows.Add(new object[] { productName, material,factory, specification,Quantity, FLAN_num,
                                                    FLAN_price, totalPiece, remark});
                }

                workbook.Close(Type.Missing, filePath, Type.Missing);
                workbooks.Close();
            }
            catch { }
            finally
            {
                application.Quit();

                //杀Excel进程
                IntPtr t = new IntPtr(application.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 
                int k = 0;
                GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k 
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 
                p.Kill(); //关闭进程k 
            }

第二种，利用NPOI开源组件导入导出

//===============================================================================
//功    能：NPOI开源组件导出导入EXCEL
//作    者：段晓锋
//创建日期：2011年11月11日
//修改历史
//修 改 人：
//修改日期：
//修改描述：
//===============================================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;

using ZG.ERP.Common.SysParameter;
using ZG.ERP.Common.Data;
using ZG.ERP.Common.Utility;

using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.POIFS;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.Util;
namespace ZG.ERP.Common.Utility
{
    /// <summary>
    /// NPOI导出导入帮助类
    /// </summary>
    public  class NPOIHelper
    {
        #region NPOI开源组件导出EXCEL方法
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, out string errorstr)
        {
            errorstr = "";
            try
            {               
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();

                #region 右击文件 属性信息
                {
                    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                    dsi.Company = "NPOI";
                    workbook.DocumentSummaryInformation = dsi;

                    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                    si.Author = "找钢网"; //填加xls文件作者信息
                    si.ApplicationName = "找钢网ERP"; //填加xls文件创建程序信息
                    si.LastAuthor = SysConfig.LoginUserInfo.LoginName; //填加xls文件最后保存者信息
                    si.Comments = SysConfig.LoginUserInfo.LoginName;  //填加xls文件作者信息
                    si.Title = "找钢网"; //填加xls文件标题信息
                    si.Subject = "找钢网的码单表格";//填加文件主题信息
                    si.CreateDateTime = DateTime.Now;
                    workbook.SummaryInformation = si;
                }
                #endregion


                //取得列宽
                int[] arrColWidth = new int[dtSource.Columns.Count];
                foreach (DataColumn item in dtSource.Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                int rowIndex = 0;

                IRow row0 = sheet.CreateRow(0);//在工作表中添加一行 
                ICellStyle headStyle = workbook.CreateCellStyle();
                //headStyle.Alignment =CellHorizontalAlignment.CENTER;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                foreach (DataColumn column in dtSource.Columns)
                {
                    row0.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    row0.GetCell(column.Ordinal).CellStyle = headStyle;

                    //设置列宽
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                }
                rowIndex = 1;
                foreach (DataRow row in dtSource.Rows)
                {
                    //#region 新建表，填充表头，填充列头，样式
                    //if (rowIndex == 65535 || rowIndex == 0)
                    //{
                    //    if (rowIndex != 0)
                    //    {
                    //        sheet = workbook.CreateSheet();
                    //    }
                    //    rowIndex = 2;
                    //}
                    //#endregion 

                    #region 填充内容
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        ICell newCell = dataRow.CreateCell(column.Ordinal);

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }

                    }
                    #endregion

                    rowIndex++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                    return ms;
                }
            }
            catch(Exception ex)
            {
                errorstr = ex.Message.ToString();
                return null;
            }
        }

        #endregion

        #region NPOI开源组件导入EXCEL方法
        /// <summary>
        /// xls导入到datatable
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static DataTable Import(string filePath, out string errorStr)
        {
            try
            {
                errorStr = "";
                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                DataTable dt = new DataTable();
                for (int j = 0; j < 12; j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                while (rows.MoveNext())
                {
                    IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt;
            }
            catch(Exception ex)
            {
                errorStr = ex.Message.ToString();
                return null;
            }
        }
        #endregion
    }
}

 　NPOI开源地址：http://npoi.codeplex.com/

　　NPOI中文文档：http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

 

　　MyXls开源地址：http://sourceforge.net/projects/myxls/