• Npoi读取Excel操作类


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using org.in2bits.MyXls;
    using System.Data;
    using System.Text.RegularExpressions;
    using System.IO;
    using NPOI;
    using NPOI.HPSF;
    using NPOI.HSSF;
    using NPOI.HSSF.UserModel;
    using NPOI.HSSF.Util;
    using NPOI.POIFS;
    using NPOI.Util;
    using System.Web;
    
    
    namespace Util
    {
        public class MyExcelUtil
        {
            /// <summary>
            /// DateTimeRowIndexes Starts From 1
            /// </summary>
            /// <param name="FileName"></param>
            /// <param name="DateTimeRowIndexes">Starts From 1</param>
            /// <returns>DataTable</returns>
            public static DataTable ReadExcelXSL(string FileName, int[] DateRowIndex)
            {
                XlsDocument doc = new XlsDocument(FileName);
                Worksheet ws = doc.Workbook.Worksheets[0];
                DataTable dt = new DataTable();
    
                if (ws.Rows.Count > 1)
                {
                    Row HeadRow = ws.Rows[1];
                    for (ushort i = 1; i <= HeadRow.CellCount; i++)
                        dt.Columns.Add("C" + i);
    
                    for (ushort i = 2; i < ws.Rows.Count; i++)
                    {
                        DataRow row = dt.NewRow();
                        Row dataRow = ws.Rows[i];
    
                        for (ushort j = 1; j <= dataRow.CellCount; j++)
                        {
                            object CellValue = dataRow.GetCell(j).Value;
                            if (DateRowIndex != null && DateRowIndex.Length > 0 && DateRowIndex.Contains<int>(j))
                            {
                                if (Regex.IsMatch(CellValue.ToString(), @"^\d{4}-\d{1,2}-\d{1,2}$"))
                                {
                                    CellValue = CellValue.ToString();
                                }
                                else
                                {
                                    CellValue = Convert.ToDateTime("1900-1-1").AddDays(Convert.ToInt32(CellValue)).ToString("yyyy-MM-dd");
                                }
                            }
    
                            row["C" + j] = CellValue;
                        }
    
                        dt.Rows.Add(row);
                    }
                }
    
                return dt;
            }
    
            /// <summary> 
            /// 读取excel , 默认第一行为标头  
            /// </summary>  
            /// <param name="strFileName"s>excel文档路径</param>  
            /// <param name="DateTimeRowIndexes">Starts From 1</param>
            /// <returns>DataTable</returns>  
            public static DataTable ReadExcel(string strFileName, int[] DateRowIndex)
            {
                try
                {
                    DataTable dt = new DataTable();
    
                    HSSFWorkbook hssfworkbook;
                    using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
    
                    HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
    
                    for (int j = 1; j <= cellCount; j++)
                    {
                        HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add("C" + j);
                    }
    
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = (HSSFRow)sheet.GetRow(i);
                        if (row == null || string.IsNullOrEmpty(Convert.ToString(row.GetCell(0)))) break;
                        DataRow dataRow = dt.NewRow();
    
                        for (int j = 0; j < cellCount; j++)
                        {
                            object CellValue = row.GetCell(j);
                            if (DateRowIndex != null && DateRowIndex.Length > 0 && DateRowIndex.Contains<int>(j + 1))
                            {
                                if (CellValue == null || CellValue.ToString() == "")
                                {
                                    CellValue = "1900-01-01";
                                }
                                else
                                {
                                    if (CellValue.ToString().Contains("/"))
                                    {
                                        CellValue = CellValue.ToString().Replace("/", "-");
                                    }
                                    if (Regex.IsMatch(CellValue.ToString(), @"^\d{4}-\d{1,2}-\d{1,2}$"))
                                    {
                                        CellValue = CellValue.ToString();
                                    }
                                    else
                                    {
                                        CellValue = Convert.ToDateTime("1900-1-1").AddDays(Convert.ToInt32(CellValue)).ToString("yyyy-MM-dd");
                                    }
                                }
                            }
    
                            dataRow[j] = CellValue;
                        }
    
                        dt.Rows.Add(dataRow);
                    }
                    return dt;
                }
                catch (Exception e)
                {
                    throw e;
                }
    
            }
    
            public static void ToExcel(DataTable dt, string title)
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
    
                //填充表头    
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }
    
    
                //填充内容    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
    
    
                MemoryStream ms = new MemoryStream(); //传回客户端
    
                workbook.Write(ms);
                workbook = null;
                ms.Flush();
                ms.Position = 0;
    
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(title) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");//导出到客户端
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
                HttpContext.Current.Response.End();
                ms.Close();//释放
                ms.Dispose();
    
    
            }
    
    
        }
    }
  • 相关阅读:
    jQuery火箭图标返回顶部代码
    网站开发之免费的图标库——iconfont
    网站开发之免费的图片库——undraw
    在webpack中使用echarts
    WeUI+的使用
    微信小程序引用自定义组件
    显示字符串中间加星号
    解决history的方法执行后不刷新页面的问题
    阻止input输入框弹出输入法
    使用taro框架开发小程序
  • 原文地址:https://www.cnblogs.com/iwenwen/p/3129075.html
Copyright © 2020-2023  润新知