博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
.NET 通过 NPOI 操作 Excel
阅读量：4978 次
发布时间：2019-06-12

本文共 49088 字，大约阅读时间需要 163 分钟。

目录

.NET 通过 NPOI 操作 Excel

第一步：通过 NuGet 获取 NPOI 包并引入程序集

20170922223938_0819NPOI.png

20170922224620_9876Import.png

第二步：引入 NPOI 帮助类

using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Text;using System.Web;using NPOI;using NPOI.HPSF;using NPOI.HSSF;using NPOI.HSSF.UserModel;using NPOI.HSSF.Util;using NPOI.POIFS;using NPOI.SS.Formula.Eval;using NPOI.SS.UserModel;using NPOI.Util;using NPOI.SS;using NPOI.DDF;using NPOI.SS.Util;using System.Collections;using System.Text.RegularExpressions;using NPOI.XSSF;using NPOI.XSSF.UserModel;using DreamClub.Web.Core;public class NPOIHelper{    private static WriteLog wl = new WriteLog();    #region 从datatable中将数据导出到excel    ///     /// DataTable导出到Excel的MemoryStream    ///     /// 源DataTable    /// 表头文本    static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)    {        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;        #region 右击文件 属性信息        //{        //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();        //    dsi.Company = "http://www.yongfa365.com/";        //    workbook.DocumentSummaryInformation = dsi;        //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();        //    si.Author = "柳永法"; //填加xls文件作者信息        //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息        //    si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息        //    si.Comments = "说明信息"; //填加xls文件作者信息        //    si.Title = "NPOI测试"; //填加xls文件标题信息        //    si.Subject = "NPOI测试Demo"; //填加文件主题信息        //    si.CreateDateTime = DateTime.Now;        //    workbook.SummaryInformation = si;        //}        #endregion        HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;        HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");        //取得列宽        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;        foreach (DataRow row in dtSource.Rows)        {            #region 新建表，填充表头，填充列头，样式            if (rowIndex == 65535 || rowIndex == 0)            {                if (rowIndex != 0)                {                    sheet = workbook.CreateSheet() as HSSFSheet;                }                #region 表头及样式                {                    HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;                    headerRow.HeightInPoints = 25;                    headerRow.CreateCell(0).SetCellValue(strHeaderText);                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                    HSSFFont font = workbook.CreateFont() as HSSFFont;                    font.FontHeightInPoints = 20;                    font.Boldweight = 700;                    headStyle.SetFont(font);                    headerRow.GetCell(0).CellStyle = headStyle;                    sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));                    //headerRow.Dispose();                }                #endregion                #region 列头及样式                {                    HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                    HSSFFont font = workbook.CreateFont() as HSSFFont;                    font.FontHeightInPoints = 10;                    font.Boldweight = 700;                    headStyle.SetFont(font);                    foreach (DataColumn column in dtSource.Columns)                    {                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                        //设置列宽                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                    }                    //headerRow.Dispose();                }                #endregion                rowIndex = 2;            }            #endregion            #region 填充内容            HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;            foreach (DataColumn column in dtSource.Columns)            {                HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;                string drValue = row[column].ToString();                switch (column.DataType.ToString())                {                    case "System.String": //字符串类型                        double result;                        if (isNumeric(drValue, out result))                        {                            double.TryParse(drValue, out result);                            newCell.SetCellValue(result);                            break;                        }                        else                        {                            newCell.SetCellValue(drValue);                            break;                        }                    case "System.DateTime": //日期类型                        DateTime dateV;                        DateTime.TryParse(drValue, out dateV);                        newCell.SetCellValue(dateV);                        newCell.CellStyle = dateStyle; //格式化显示                        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;            //sheet.Dispose();            //workbook.Dispose();            return ms;        }    }    ///     /// DataTable导出到Excel的MemoryStream    ///     /// 源DataTable    /// 表头文本    static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)    {        XSSFWorkbook workbook = new XSSFWorkbook();        XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;        #region 右击文件 属性信息        //{        //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();        //    dsi.Company = "http://www.yongfa365.com/";        //    workbook.DocumentSummaryInformation = dsi;        //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();        //    si.Author = "柳永法"; //填加xls文件作者信息        //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息        //    si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息        //    si.Comments = "说明信息"; //填加xls文件作者信息        //    si.Title = "NPOI测试"; //填加xls文件标题信息        //    si.Subject = "NPOI测试Demo"; //填加文件主题信息        //    si.CreateDateTime = DateTime.Now;        //    workbook.SummaryInformation = si;        //}        #endregion        XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;        XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");        //取得列宽        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;        foreach (DataRow row in dtSource.Rows)        {            #region 新建表，填充表头，填充列头，样式            if (rowIndex == 0)            {                #region 表头及样式                //{                //    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;                //    headerRow.HeightInPoints = 25;                //    headerRow.CreateCell(0).SetCellValue(strHeaderText);                //    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;                //    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;                //    XSSFFont font = workbook.CreateFont() as XSSFFont;                //    font.FontHeightInPoints = 20;                //    font.Boldweight = 700;                //    headStyle.SetFont(font);                //    headerRow.GetCell(0).CellStyle = headStyle;                //    //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));                //    //headerRow.Dispose();                //}                #endregion                #region 列头及样式                {                    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;                    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                    XSSFFont font = workbook.CreateFont() as XSSFFont;                    font.FontHeightInPoints = 10;                    font.Boldweight = 700;                    headStyle.SetFont(font);                    foreach (DataColumn column in dtSource.Columns)                    {                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                        //设置列宽                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                    }                    //headerRow.Dispose();                }                #endregion                rowIndex = 1;            }            #endregion            #region 填充内容            XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;            foreach (DataColumn column in dtSource.Columns)            {                XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;                string drValue = row[column].ToString();                switch (column.DataType.ToString())                {                    case "System.String": //字符串类型                        double result;                        if (isNumeric(drValue, out result))                        {                            double.TryParse(drValue, out result);                            newCell.SetCellValue(result);                            break;                        }                        else                        {                            newCell.SetCellValue(drValue);                            break;                        }                    case "System.DateTime": //日期类型                        DateTime dateV;                        DateTime.TryParse(drValue, out dateV);                        newCell.SetCellValue(dateV);                        newCell.CellStyle = dateStyle; //格式化显示                        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++;        }        workbook.Write(fs);        fs.Close();    }    ///     /// DataTable导出到Excel文件    ///     /// 源DataTable    /// 表头文本    /// 保存位置    public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName)    {        string[] temp = strFileName.Split('.');        if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && dtSource.Rows.Count < 65536)        {            using (MemoryStream ms = ExportDT(dtSource, strHeaderText))            {                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                {                    byte[] data = ms.ToArray();                    fs.Write(data, 0, data.Length);                    fs.Flush();                }            }        }        else        {            if (temp[temp.Length - 1] == "xls")                strFileName = strFileName + "x";            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))            {                ExportDTI(dtSource, strHeaderText, fs);            }        }    }    #endregion    #region 从excel中将数据导出到datatable    ///     /// 读取excel 默认第一行为标头    ///     /// excel文档路径    /// 
        public static DataTable ImportExceltoDt(string strFileName)    {        DataTable dt = new DataTable();        IWorkbook wb;        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))        {            wb = WorkbookFactory.Create(file);        }        ISheet sheet = wb.GetSheetAt(0);        dt = ImportDt(sheet, 0, true);        return dt;    }    ///     /// 读取Excel流到DataTable    ///     /// Excel流    /// 
    
     第一个sheet中的数据
        public static DataTable ImportExceltoDt(Stream stream)    {        try        {            DataTable dt = new DataTable();            IWorkbook wb;            using (stream)            {                wb = WorkbookFactory.Create(stream);            }            ISheet sheet = wb.GetSheetAt(0);            dt = ImportDt(sheet, 0, true);            return dt;        }        catch (Exception)        {            throw;        }    }    ///     /// 读取Excel流到DataTable    ///     /// Excel流    /// 表单名    /// 列头所在行号，-1表示没有列头    /// 
    
     指定sheet中的数据
        public static DataTable ImportExceltoDt(Stream stream, string sheetName, int HeaderRowIndex)    {        try        {            DataTable dt = new DataTable();            IWorkbook wb;            using (stream)            {                wb = WorkbookFactory.Create(stream);            }            ISheet sheet = wb.GetSheet(sheetName);            dt = ImportDt(sheet, HeaderRowIndex, true);            return dt;        }        catch (Exception)        {            throw;        }    }    ///     /// 读取Excel流到DataSet    ///     /// Excel流    /// 
    
     Excel中的数据
        public static DataSet ImportExceltoDs(Stream stream)    {        try        {            DataSet ds = new DataSet();            IWorkbook wb;            using (stream)            {                wb = WorkbookFactory.Create(stream);            }            for (int i = 0; i < wb.NumberOfSheets; i++)            {                DataTable dt = new DataTable();                ISheet sheet = wb.GetSheetAt(i);                dt = ImportDt(sheet, 0, true);                ds.Tables.Add(dt);            }            return ds;        }        catch (Exception)        {            throw;        }    }    ///     /// 读取Excel流到DataSet    ///     /// Excel流    /// 字典参数，key：sheet名，value：列头所在行号，-1表示没有列头    /// 
    
     Excel中的数据
        public static DataSet ImportExceltoDs(Stream stream, Dictionary
    
      dict)    {        try        {            DataSet ds = new DataSet();            IWorkbook wb;            using (stream)            {                wb = WorkbookFactory.Create(stream);            }            foreach (string key in dict.Keys)            {                DataTable dt = new DataTable();                ISheet sheet = wb.GetSheet(key);                dt = ImportDt(sheet, dict[key], true);                ds.Tables.Add(dt);            }            return ds;        }        catch (Exception)        {            throw;        }    }    /// 
         /// 读取excel    ///     /// 
     excel文件路径    /// 
     需要导出的sheet    /// 
     列头所在行号，-1表示没有列头    /// 
         public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)    {        HSSFWorkbook workbook;        IWorkbook wb;        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))        {            wb = new HSSFWorkbook(file);        }        ISheet sheet = wb.GetSheet(SheetName);        DataTable table = new DataTable();        table = ImportDt(sheet, HeaderRowIndex, true);        //ExcelFileStream.Close();        workbook = null;        sheet = null;        return table;    }    /// 
         /// 读取excel    ///     /// 
     excel文件路径    /// 
     需要导出的sheet序号    /// 
     列头所在行号，-1表示没有列头    /// 
         public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)    {        HSSFWorkbook workbook;        IWorkbook wb;        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))        {            wb = WorkbookFactory.Create(file);        }        ISheet isheet = wb.GetSheetAt(SheetIndex);        DataTable table = new DataTable();        table = ImportDt(isheet, HeaderRowIndex, true);        //ExcelFileStream.Close();        workbook = null;        isheet = null;        return table;    }    /// 
         /// 读取excel    ///     /// 
     excel文件路径    /// 
     需要导出的sheet    /// 
     列头所在行号，-1表示没有列头    /// 
         public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)    {        HSSFWorkbook workbook;        IWorkbook wb;        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))        {            wb = WorkbookFactory.Create(file);        }        ISheet sheet = wb.GetSheet(SheetName);        DataTable table = new DataTable();        table = ImportDt(sheet, HeaderRowIndex, needHeader);        //ExcelFileStream.Close();        workbook = null;        sheet = null;        return table;    }    /// 
         /// 读取excel    ///     /// 
     excel文件路径    /// 
     需要导出的sheet序号    /// 
     列头所在行号，-1表示没有列头    /// 
         public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)    {        HSSFWorkbook workbook;        IWorkbook wb;        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))        {            wb = WorkbookFactory.Create(file);        }        ISheet sheet = wb.GetSheetAt(SheetIndex);        DataTable table = new DataTable();        table = ImportDt(sheet, HeaderRowIndex, needHeader);        //ExcelFileStream.Close();        workbook = null;        sheet = null;        return table;    }    /// 
         /// 将制定sheet中的数据导出到datatable中    ///     /// 
     需要导出的sheet    /// 
     列头所在行号，-1表示没有列头    /// 
         static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)    {        DataTable table = new DataTable();        IRow headerRow;        int cellCount;        try        {            if (HeaderRowIndex < 0 || !needHeader)            {                headerRow = sheet.GetRow(0);                cellCount = headerRow.LastCellNum;                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)                {                    DataColumn column = new DataColumn(Convert.ToString(i));                    table.Columns.Add(column);                }            }            else            {                headerRow = sheet.GetRow(HeaderRowIndex);                cellCount = headerRow.LastCellNum;                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)                {                    if (headerRow.GetCell(i) == null)                    {                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)                        {                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));                            table.Columns.Add(column);                        }                        else                        {                            DataColumn column = new DataColumn(Convert.ToString(i));                            table.Columns.Add(column);                        }                    }                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)                    {                        DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));                        table.Columns.Add(column);                    }                    else                    {                        DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());                        table.Columns.Add(column);                    }                }            }            int rowCount = sheet.LastRowNum;            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)            {                try                {                    IRow row;                    if (sheet.GetRow(i) == null)                    {                        row = sheet.CreateRow(i);                    }                    else                    {                        row = sheet.GetRow(i);                    }                    DataRow dataRow = table.NewRow();                    for (int j = row.FirstCellNum; j <= cellCount; j++)                    {                        try                        {                            if (row.GetCell(j) != null)                            {                                switch (row.GetCell(j).CellType)                                {                                    case CellType.String:                                        string str = row.GetCell(j).StringCellValue;                                        if (str != null && str.Length > 0)                                        {                                            dataRow[j] = str.ToString();                                        }                                        else                                        {                                            dataRow[j] = null;                                        }                                        break;                                    case CellType.Numeric:                                        if (DateUtil.IsCellDateFormatted(row.GetCell(j)))                                        {                                            dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);                                        }                                        else                                        {                                            dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);                                        }                                        break;                                    case CellType.Boolean:                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                        break;                                    case CellType.Error:                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                        break;                                    case CellType.Formula:                                        switch (row.GetCell(j).CachedFormulaResultType)                                        {                                            case CellType.String:                                                string strFORMULA = row.GetCell(j).StringCellValue;                                                if (strFORMULA != null && strFORMULA.Length > 0)                                                {                                                    dataRow[j] = strFORMULA.ToString();                                                }                                                else                                                {                                                    dataRow[j] = null;                                                }                                                break;                                            case CellType.Numeric:                                                dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);                                                break;                                            case CellType.Boolean:                                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                                break;                                            case CellType.Error:                                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                                break;                                            default:                                                dataRow[j] = "";                                                break;                                        }                                        break;                                    default:                                        dataRow[j] = "";                                        break;                                }                            }                        }                        catch (Exception exception)                        {                            wl.WriteLogs(exception.ToString());                        }                    }                    table.Rows.Add(dataRow);                }                catch (Exception exception)                {                    wl.WriteLogs(exception.ToString());                }            }        }        catch (Exception exception)        {            wl.WriteLogs(exception.ToString());        }        return table;    }    #endregion    public static void InsertSheet(string outputFile, string sheetname, DataTable dt)    {        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);        IWorkbook hssfworkbook = WorkbookFactory.Create(readfile);        //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);        int num = hssfworkbook.GetSheetIndex(sheetname);        ISheet sheet1;        if (num >= 0)            sheet1 = hssfworkbook.GetSheet(sheetname);        else        {            sheet1 = hssfworkbook.CreateSheet(sheetname);        }        try        {            if (sheet1.GetRow(0) == null)            {                sheet1.CreateRow(0);            }            for (int coluid = 0; coluid < dt.Columns.Count; coluid++)            {                if (sheet1.GetRow(0).GetCell(coluid) == null)                {                    sheet1.GetRow(0).CreateCell(coluid);                }                sheet1.GetRow(0).GetCell(coluid).SetCellValue(dt.Columns[coluid].ColumnName);            }        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());            throw;        }        for (int i = 1; i <= dt.Rows.Count; i++)        {            try            {                if (sheet1.GetRow(i) == null)                {                    sheet1.CreateRow(i);                }                for (int coluid = 0; coluid < dt.Columns.Count; coluid++)                {                    if (sheet1.GetRow(i).GetCell(coluid) == null)                    {                        sheet1.GetRow(i).CreateCell(coluid);                    }                    sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString());                }            }            catch (Exception ex)            {                wl.WriteLogs(ex.ToString());                //throw;            }        }        try        {            readfile.Close();            FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);            hssfworkbook.Write(writefile);            writefile.Close();        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());        }    }    #region 更新excel中的数据    /// 
         /// 更新Excel表格    ///     /// 
     需更新的excel表格路径    /// 
     sheet名    /// 
     需更新的数据    /// 
     需更新的列号    /// 
     需更新的开始行号    public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)    {        //FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);        IWorkbook hssfworkbook = null;// WorkbookFactory.Create(outputFile);        //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);        for (int i = 0; i < updateData.Length; i++)        {            try            {                if (sheet1.GetRow(i + rowid) == null)                {                    sheet1.CreateRow(i + rowid);                }                if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)                {                    sheet1.GetRow(i + rowid).CreateCell(coluid);                }                sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);            }            catch (Exception ex)            {                wl.WriteLogs(ex.ToString());                throw;            }        }        try        {            //readfile.Close();            FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);            hssfworkbook.Write(writefile);            writefile.Close();        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());        }    }    /// 
         /// 更新Excel表格    ///     /// 
     需更新的excel表格路径    /// 
     sheet名    /// 
     需更新的数据    /// 
     需更新的列号    /// 
     需更新的开始行号    public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)    {        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);        readfile.Close();        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);        for (int j = 0; j < coluids.Length; j++)        {            for (int i = 0; i < updateData[j].Length; i++)            {                try                {                    if (sheet1.GetRow(i + rowid) == null)                    {                        sheet1.CreateRow(i + rowid);                    }                    if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)                    {                        sheet1.GetRow(i + rowid).CreateCell(coluids[j]);                    }                    sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);                }                catch (Exception ex)                {                    wl.WriteLogs(ex.ToString());                }            }        }        try        {            FileStream writefile = new FileStream(outputFile, FileMode.Create);            hssfworkbook.Write(writefile);            writefile.Close();        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());        }    }    /// 
         /// 更新Excel表格    ///     /// 
     需更新的excel表格路径    /// 
     sheet名    /// 
     需更新的数据    /// 
     需更新的列号    /// 
     需更新的开始行号    public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)    {        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);        for (int i = 0; i < updateData.Length; i++)        {            try            {                if (sheet1.GetRow(i + rowid) == null)                {                    sheet1.CreateRow(i + rowid);                }                if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)                {                    sheet1.GetRow(i + rowid).CreateCell(coluid);                }                sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);            }            catch (Exception ex)            {                wl.WriteLogs(ex.ToString());                throw;            }        }        try        {            readfile.Close();            FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);            hssfworkbook.Write(writefile);            writefile.Close();        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());        }    }    /// 
         /// 更新Excel表格    ///     /// 
     需更新的excel表格路径    /// 
     sheet名    /// 
     需更新的数据    /// 
     需更新的列号    /// 
     需更新的开始行号    public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)    {        FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);        HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);        readfile.Close();        ISheet sheet1 = hssfworkbook.GetSheet(sheetname);        for (int j = 0; j < coluids.Length; j++)        {            for (int i = 0; i < updateData[j].Length; i++)            {                try                {                    if (sheet1.GetRow(i + rowid) == null)                    {                        sheet1.CreateRow(i + rowid);                    }                    if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)                    {                        sheet1.GetRow(i + rowid).CreateCell(coluids[j]);                    }                    sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);                }                catch (Exception ex)                {                    wl.WriteLogs(ex.ToString());                }            }        }        try        {            FileStream writefile = new FileStream(outputFile, FileMode.Create);            hssfworkbook.Write(writefile);            writefile.Close();        }        catch (Exception ex)        {            wl.WriteLogs(ex.ToString());        }    }    #endregion    public static int GetSheetNumber(string outputFile)    {        int number = 0;        try        {            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);            number = hssfworkbook.NumberOfSheets;        }        catch (Exception exception)        {            wl.WriteLogs(exception.ToString());        }        return number;    }    public static ArrayList GetSheetName(string outputFile)    {        ArrayList arrayList = new ArrayList();        try        {            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);            for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)            {                arrayList.Add(hssfworkbook.GetSheetName(i));            }        }        catch (Exception exception)        {            wl.WriteLogs(exception.ToString());        }        return arrayList;    }    public static bool isNumeric(String message, out double result)    {        Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");        result = -1;        if (rex.IsMatch(message))        {            result = double.Parse(message);            return true;        }        else            return false;    }    //  现用导出  \\\\\\\\\\      /// 
         /// 用于Web导出                                                                                             第一步    ///     /// 
     源DataTable    /// 
     表头文本    /// 
     文件名    public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)    {        HttpContext curContext = HttpContext.Current;        // 设置编码和附件格式        curContext.Response.ContentType = "application/vnd.ms-excel";        curContext.Response.ContentEncoding = Encoding.UTF8;        curContext.Response.Charset = "";        curContext.Response.AppendHeader("Content-Disposition",        "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));        curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());        curContext.Response.End();    }    /// 
         /// DataTable导出到Excel的MemoryStream                                                                      第二步    ///     /// 
     源DataTable    /// 
     表头文本    public static MemoryStream Export(DataTable dtSource, string strHeaderText)    {        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;        #region 右击文件 属性信息        {            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();            dsi.Company = "NPOI";            workbook.DocumentSummaryInformation = dsi;            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();            si.Author = "文件作者信息"; //填加xls文件作者信息            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息            si.Comments = "作者信息"; //填加xls文件作者信息            si.Title = "标题信息"; //填加xls文件标题信息            si.Subject = "主题信息";//填加文件主题信息            si.CreateDateTime = DateTime.Now;            workbook.SummaryInformation = si;        }        #endregion        HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;        HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");        //取得列宽        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;        foreach (DataRow row in dtSource.Rows)        {            #region 新建表，填充表头，填充列头，样式            if (rowIndex == 65535 || rowIndex == 0)            {                if (rowIndex != 0)                {                    sheet = workbook.CreateSheet() as HSSFSheet;                }                #region 表头及样式                {                    if (string.IsNullOrEmpty(strHeaderText))                    {                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                        //headStyle.Alignment = CellHorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont() as HSSFFont;                        font.FontHeightInPoints = 20;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        headerRow.GetCell(0).CellStyle = headStyle;                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));                        //headerRow.Dispose();                    }                }                #endregion                #region 列头及样式                {                    HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;                    //headStyle.Alignment = CellHorizontalAlignment.CENTER;                    HSSFFont font = workbook.CreateFont() as HSSFFont;                    font.FontHeightInPoints = 10;                    font.Boldweight = 700;                    headStyle.SetFont(font);                    foreach (DataColumn column in dtSource.Columns)                    {                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                        //设置列宽                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                    }                    //headerRow.Dispose();                }                #endregion                rowIndex = 1;            }            #endregion            #region 填充内容            HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;            foreach (DataColumn column in dtSource.Columns)            {                HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;                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);                        newCell.CellStyle = dateStyle;//格式化显示                        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;            //sheet.Dispose();            //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet            return ms;        }    }    /// 
         /// /注：分浏览器进行编码（IE必须编码，FireFox不能编码，Chrome可编码也可不编码）    ///     /// 
         /// 
         /// 
         public static void ExportByWeb(DataSet ds, string strHeaderText, string strFileName)    {        HttpContext curContext = HttpContext.Current;        curContext.Response.ContentType = "application/vnd.ms-excel";        curContext.Response.Charset = "";        if (curContext.Request.UserAgent.ToLower().IndexOf("firefox", System.StringComparison.Ordinal) > 0)        {            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName);        }        else        {            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));        }        //  curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" +strFileName);        curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");        curContext.Response.BinaryWrite(ExportDataSetToExcel(ds, strHeaderText).GetBuffer());        curContext.Response.End();    }    /// 
         /// 由DataSet导出Excel    ///     /// 
     要导出数据的DataTable    /// 
     工作表名称    /// 
     
      Excel工作表
         private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName)    {        HSSFWorkbook workbook = new HSSFWorkbook();        MemoryStream ms = new MemoryStream();        string[] sheetNames = sheetName.Split(',');        for (int i = 0; i < sheetNames.Length; i++)        {            ISheet sheet = workbook.CreateSheet(sheetNames[i]);            #region 列头            IRow headerRow = sheet.CreateRow(0);            HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;            HSSFFont font = workbook.CreateFont() as HSSFFont;            font.FontHeightInPoints = 10;            font.Boldweight = 700;            headStyle.SetFont(font);            //取得列宽            int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];            foreach (DataColumn item in sourceDs.Tables[i].Columns)            {                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;            }            // 处理列头            foreach (DataColumn column in sourceDs.Tables[i].Columns)            {                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                //设置列宽                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);            }            #endregion            #region 填充值            int rowIndex = 1;            foreach (DataRow row in sourceDs.Tables[i].Rows)            {                IRow dataRow = sheet.CreateRow(rowIndex);                foreach (DataColumn column in sourceDs.Tables[i].Columns)                {                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());                }                rowIndex++;            }            #endregion        }        workbook.Write(ms);        ms.Flush();        ms.Position = 0;        workbook = null;        return ms;    }    /// 
         /// 验证导入的Excel是否有数据    ///     /// 
         /// 
         public static bool HasData(Stream excelFileStream)    {        using (excelFileStream)        {            IWorkbook workBook = new HSSFWorkbook(excelFileStream);            if (workBook.NumberOfSheets > 0)            {                ISheet sheet = workBook.GetSheetAt(0);                return sheet.PhysicalNumberOfRows > 0;            }        }        return false;    }}

第三步：在程序中调用相应的方法对数据进行导出导入操作

将 DataTable 导出为 Excel

string fileName = Server.MapPath("~/Content/temp/" + "课程数据.xls");var dt = bll.GetDataTable();NPOIHelper.ExportDTtoExcel(dt, "课程数据", fileName);//参数说明 dt：数据源，"课程数据"：导出的Excel表头，fileName：导出的文件名（全路径）

将 Excel 导入为 DataTable

string fileName = Server.MapPath("~/Content/temp/" + "课程数据.xls");DataTable dt = NPOIHelper.ImportExceltoDt(fileName);//参数说明 fileName：导入的文件名（全路径）

在 Web 端将 DataTable 导出为 Excel 并输出到浏览器下载

public FileResult ExportSelClassDataExcel(string id){    //获取要导出的数据    var dt = bll.GetDataTable();    string filename = DateTime.Now.ToString("yyyy-MM-dd")+".xls";    NPOIHelper.ExportByWeb(dt , "这是表头文本啊", filename);    return null;}

其它方法

帮助类中提供的方法还很多，不在一一赘述

转载于:https://www.cnblogs.com/wangzhezhifenfeng/p/7614574.html

你可能感兴趣的文章
软件绿色版和安装版的区别
查看>>
HeapSpray初窥(2014.12)
查看>>
hdu5437 优先队列 长春网赛
查看>>
每日模板一练——树链剖分求LCA（好吧是真的写错了）
查看>>
常用图像数据集：标注、检索
查看>>
python基础补漏-02-collection
查看>>
场景设计（二）-----组合场景设计
查看>>
找到的一些SQLite3的int和二进制数据的插入、读取操作。
查看>>
PHP 程序员的技术成长规划(转载)
查看>>
springboot使用jdbcTemplate连接数据库
查看>>
iOS中XMPP简单聊天实现 好友和聊天
查看>>
面试时如何优雅的谈论OC
查看>>
sublime安装插件
查看>>
C++ 函数的扩展①
查看>>
Linux查看实时带宽流量情况
查看>>
2018-2019-2 网络对抗技术 20165231 Exp 8 Web基础
查看>>
Docker基础教程
查看>>
c 函数调用产生的汇编指令和数据在内存情况(1)
查看>>
第九章：聚类分析的典型应用和技术小窍门
查看>>
CSS页面遮罩
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-11-08 15:29:37   当前IP: 18.191.165.187   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我