Example #1
0
 //ExcelHelper excle = new ExcelHelper();
 //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
 //Response.BinaryWrite(excle.ExportXLS());
 public static DataTable Excel2DataTable(string filePath)
 {
     NPOI.SS.UserModel.ISheet sheet = null;
     #region//初始化信息
     try
     {
         string fileExt = Path.GetExtension(filePath).ToLower();
         using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
         {
             if (fileExt == ".xls")
             {
                 sheet = new NPOI.HSSF.UserModel.HSSFWorkbook(file).GetSheetAt(0);
             }
             else if (fileExt == ".xlsx")
             {
                 sheet = new NPOI.XSSF.UserModel.XSSFWorkbook(file).GetSheetAt(0);
             }
             else
             {
                 sheet = null;
             }
         }
     }
     catch (Exception e)
     {
         throw e;
     }
     #endregion
     if (sheet == null)
     {
         return(null);
     }
     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
     DataTable dt = new DataTable();
     for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
     {
         dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
     }
     while (rows.MoveNext())
     {
         NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
         DataRow dr = dt.NewRow();
         for (int i = 0; i < row.LastCellNum; i++)
         {
             NPOI.SS.UserModel.ICell cell = row.GetCell(i);
             if (cell == null)
             {
                 dr[i] = null;
             }
             else
             {
                 dr[i] = cell.ToString();
             }
         }
         dt.Rows.Add(dr);
     }
     return(dt);
 }
Example #2
0
        public static DataTable ImportExcelXlsFile(string filePath, string extension, int sheetnum = 0, int columnnum = 0)
        {
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            NPOI.SS.UserModel.ISheet sheet;
            sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();

            //给DdataTable添加表头
            if (sheet.GetRow(sheetnum) != null)
            {
                for (int j = 0; j < (sheet.GetRow(columnnum).LastCellNum); j++)
                {
                    dt.Columns.Add(sheet.GetRow(columnnum).Cells[j].ToString());
                }
                //读取数据
                while (rows.MoveNext())
                {
                    HSSFRow row1 = null;

                    row1 = (HSSFRow)rows.Current;
                    int j = row1.LastCellNum;

                    DataRow dr = dt.NewRow();
                    if (j > dt.Columns.Count)
                    {
                        j = dt.Columns.Count;
                    }
                    for (int i = 0; i < j; i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row1.GetCell(i);

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return(dt);
        }
Example #3
0
        public static DataTable ImportExcelFile(string filePath)

        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息

            try

            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))

                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }

            catch (Exception e)

            {
                throw e;
            }

            #endregion
            NPOI.SS.UserModel.ISheet       sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows  = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                dt.Columns.Add(sheet.GetRow(0).GetCell(j).ToString());
            }
            //sheet.cu
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                if (row.RowNum != 0)
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return(dt);
        }
Example #4
0
 /// <summary>
 /// 获取单元格的值。
 /// </summary>
 /// <param name="iCell">NPOI.SS.UserModel.ICell对象。</param>
 /// <returns>单元格的值。</returns>
 public static object GetCellValue(NPOI.SS.UserModel.ICell iCell)
 {
     if (iCell == null)
     {
         return(null);
     }
     else
     {
         if (iCell.CellType == NPOI.SS.UserModel.CellType.Blank)
         {
             return("");
         }
         else if (iCell.CellType == NPOI.SS.UserModel.CellType.Boolean)
         {
             return(iCell.BooleanCellValue);
         }
         else if (iCell.CellType == NPOI.SS.UserModel.CellType.Error)
         {
             return(iCell.ErrorCellValue.ToString(System.Globalization.CultureInfo.InvariantCulture));
         }
         else if (iCell.CellType == NPOI.SS.UserModel.CellType.Formula)
         {
             if (iCell.CachedFormulaResultType == NPOI.SS.UserModel.CellType.Error)
             {
                 return("");
             }
             else
             {
                 return(iCell.NumericCellValue);
             }
         }
         else if (iCell.CellType == NPOI.SS.UserModel.CellType.Numeric)
         {
             if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(iCell))
             {
                 return(iCell.DateCellValue);
             }
             else
             {
                 return(iCell.NumericCellValue);
             }
         }
         else if (iCell.CellType == NPOI.SS.UserModel.CellType.String)
         {
             return(iCell.StringCellValue);
         }
         else
         {
             return(iCell.ToString());
         }
     }
 }
Example #5
0
        public static dynamic GetCellValue(NPOI.SS.UserModel.ICell cell)
        {
            if (cell == null)
            {
                return(null);
            }
            else
            {
                switch (cell.CellType)
                {
                case NPOI.SS.UserModel.CellType.Blank:
                    return("");

                case NPOI.SS.UserModel.CellType.Boolean:
                    return(cell.BooleanCellValue);

                case NPOI.SS.UserModel.CellType.Error:
                    return(cell.ErrorCellValue.ToString(System.Globalization.CultureInfo.InvariantCulture));

                case NPOI.SS.UserModel.CellType.Formula:
                    if (cell.CachedFormulaResultType == NPOI.SS.UserModel.CellType.Error)
                    {
                        return("#NUM!");
                    }
                    else
                    {
                        return(cell.NumericCellValue);
                    }

                case NPOI.SS.UserModel.CellType.Numeric:
                    if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
                    {
                        return(cell.DateCellValue);
                    }
                    else
                    {
                        return(cell.NumericCellValue);
                    }

                case NPOI.SS.UserModel.CellType.String:
                    return(cell.StringCellValue);

                default:
                    return(cell.ToString());
                }
            }
        }
Example #6
0
        public object Cell2Object(NPOI.SS.UserModel.ICell cell, System.Type type, out bool doSet)
        {
            if (cell.Hyperlink != null)
            {
                var segs = cell.Hyperlink.Address.Split('!');
                if (segs.Length != 2)
                {
                    doSet = false;
                    EngineNS.Profiler.Log.WriteLine(EngineNS.Profiler.ELogTag.Warning, "Excel", $"HyperLink({cell.Hyperlink}) is invalid");
                    return(null);
                }
                var tab      = segs[0];
                var linkCell = segs[1];
                var sheet    = Workbook.GetSheet(tab);
                if (sheet == null)
                {
                    doSet = false;
                    return(null);
                }

                int col, row;
                GetCellPosition(linkCell, out col, out row);
                var rowObj = sheet.GetRow(row);
                if (rowObj == null)
                {
                    doSet = false;
                    return(null);
                }

                var linker = rowObj.GetCell(col);
                if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
                {//是一个数组
                    var obj = System.Activator.CreateInstance(type) as System.Collections.IList;
                    if (linker != null)
                    {
                        FillObjectList(obj, type.GenericTypeArguments[0], linker.Row);
                    }
                    doSet = true;
                    return(obj);
                }
                else
                {//是一个对象
                    if (linker == null)
                    {
                        doSet = false;
                        return(null);
                    }

                    var atts = type.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
                    if (atts == null || atts.Length == 0)
                    {
                        doSet = false;
                        return(null);
                    }

                    var obj = System.Activator.CreateInstance(type);
                    if (obj != null)
                    {
                        FillObject(obj, linker.Row);
                    }
                    doSet = true;
                    return(obj);
                }
            }
            else
            {
                doSet = true;
                if (type == typeof(sbyte))
                {
                    return(System.Convert.ToSByte(cell.ToString()));
                }
                else if (type == typeof(Int16))
                {
                    return(System.Convert.ToInt16(cell.ToString()));
                }
                else if (type == typeof(Int32))
                {
                    return(System.Convert.ToInt32(cell.ToString()));
                }
                else if (type == typeof(Int64))
                {
                    return(System.Convert.ToInt64(cell.ToString()));
                }
                if (type == typeof(byte))
                {
                    return(System.Convert.ToByte(cell.ToString()));
                }
                else if (type == typeof(UInt16))
                {
                    return(System.Convert.ToUInt16(cell.ToString()));
                }
                else if (type == typeof(UInt32))
                {
                    return(System.Convert.ToUInt32(cell.ToString()));
                }
                else if (type == typeof(UInt64))
                {
                    return(System.Convert.ToUInt64(cell.ToString()));
                }
                else if (type == typeof(double))
                {
                    return(System.Convert.ToDouble(cell.ToString()));
                }
                else if (type == typeof(float))
                {
                    return(System.Convert.ToSingle(cell.ToString()));
                }
                else if (type == typeof(string))
                {
                    return(cell.StringCellValue);
                }
                else if (type == typeof(bool))
                {
                    return(cell.BooleanCellValue);
                }
                else if (type == typeof(DateTime))
                {
                    return(System.Convert.ToDateTime(cell.ToString()));
                }
                doSet = false;
            }
            return(null);
        }
Example #7
0
        public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, bool firstLineIsColumnHead)
        {
            System.Data.DataTable       dataSource = new System.Data.DataTable();
            NPOI.SS.UserModel.IWorkbook workbook   = GetWorkbookFromExcelFile(fileName);
            if (workbook == null)
            {
                return(dataSource);
            }
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
            if (sheet == null)
            {
                return(dataSource);
            }
            NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
            if (firstRow != null)
            {
                for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++)
                {
                    if (firstLineIsColumnHead)
                    {
                        NPOI.SS.UserModel.ICell cell = firstRow.GetCell(columnIndex);
                        if (cell == null)
                        {
                            dataSource.Columns.Add();
                        }
                        else
                        {
                            dataSource.Columns.Add(cell.ToString());
                        }
                    }
                    else
                    {
                        dataSource.Columns.Add();
                    }
                }
            }
            int startRowIndex = firstLineIsColumnHead ? 1 : 0;

            for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                System.Data.DataRow dataSourceRow = dataSource.NewRow();
                dataSource.Rows.Add(dataSourceRow);
                NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
                if (row != null)
                {
                    for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                            case NPOI.SS.UserModel.CellType.Blank:
                                dataSourceRow[columnIndex] = "";
                                break;

                            case NPOI.SS.UserModel.CellType.Boolean:
                                dataSourceRow[columnIndex] = cell.BooleanCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Error:
                                dataSourceRow[columnIndex] = cell.ErrorCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Formula:
                                dataSourceRow[columnIndex] = cell.NumericCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Numeric:
                                if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
                                {
                                    dataSourceRow[columnIndex] = cell.DateCellValue;
                                }
                                else
                                {
                                    dataSourceRow[columnIndex] = cell.NumericCellValue;
                                }
                                break;

                            case NPOI.SS.UserModel.CellType.String:
                                dataSourceRow[columnIndex] = cell.StringCellValue;
                                break;

                            default:
                                dataSourceRow[columnIndex] = cell.ToString();
                                break;
                            }
                        }
                    }
                }
            }
            return(dataSource);
        }
Example #8
0
        public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int sheetNumber, int startColumnNumber, int startRowNumber, params string[] columnNames)
        {
            System.Data.DataTable dataSource = new System.Data.DataTable();
            if (columnNames.Length < 1)
            {
                return(dataSource);
            }
            foreach (string columnName in columnNames)
            {
                dataSource.Columns.Add(columnName);
            }
            NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
            if (workbook == null)
            {
                return(dataSource);
            }
            if (sheetNumber > workbook.NumberOfSheets)
            {
                return(dataSource);
            }
            int sheetIndex = sheetNumber > 0 ? sheetNumber - 1 : workbook.ActiveSheetIndex;

            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(sheetIndex);
            int startRowIndex = startRowNumber > 0 ? startRowNumber - 1 : 0;

            for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
            {
                System.Data.DataRow dataSourceRow = dataSource.NewRow();
                dataSource.Rows.Add(dataSourceRow);
                NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    int startColumnIndex = startColumnNumber > 0 ? startColumnNumber - 1 : 0;
                    int columnIndex      = 0;
                    Enumerable.Range(startColumnIndex, dataSource.Columns.Count).ToList().ForEach(cellIndex =>
                    {
                        NPOI.SS.UserModel.ICell cell = row.GetCell(cellIndex);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                            case NPOI.SS.UserModel.CellType.Blank:
                                dataSourceRow[columnIndex] = "";
                                break;

                            case NPOI.SS.UserModel.CellType.Boolean:
                                dataSourceRow[columnIndex] = cell.BooleanCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Error:
                                dataSourceRow[columnIndex] = cell.ErrorCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Formula:
                                dataSourceRow[columnIndex] = cell.NumericCellValue;
                                break;

                            case NPOI.SS.UserModel.CellType.Numeric:
                                if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
                                {
                                    dataSourceRow[columnIndex] = cell.DateCellValue;
                                }
                                else
                                {
                                    dataSourceRow[columnIndex] = cell.NumericCellValue;
                                }
                                break;

                            case NPOI.SS.UserModel.CellType.String:
                                dataSourceRow[columnIndex] = cell.StringCellValue;
                                break;

                            default:
                                dataSourceRow[columnIndex] = cell.ToString();
                                break;
                            }
                        }
                        columnIndex++;
                    });
                }
            }
            return(dataSource);
        }
Example #9
0
        /// <summary>
        /// 从NPOI.SS.UserModel.ISheet中获取数据表。
        /// </summary>
        /// <param name="iSheet">NPOI.SS.UserModel.ISheet对象。</param>
        /// <param name="firstRowIsColumnHead">是否将首行作为标题行。</param>
        /// <param name="maxColumnCount">最大列数,如果为0则不限制;如果大于0,则限制为指定的列数。</param>
        /// <param name="startColumnIndex">起始列的索引,从0开始。</param>
        /// <param name="startRowIndex">起始行的索引,从0开始。</param>
        /// <returns>System.Data.DataTable对象。</returns>
        public static System.Data.DataTable GetDataTable(NPOI.SS.UserModel.ISheet iSheet, bool firstRowIsColumnHead, int maxColumnCount, int startColumnIndex, int startRowIndex)
        {
            // 如果为空直接返回空。
            if (iSheet == null)
            {
                return(null);
            }
            // 获取最大列数和最大行数。
            int usedColumnCount = Extension.NPOIMethod.GetUsedColumnCount(iSheet);
            int usedRowCount    = iSheet.LastRowNum + 1;

            // 初始化DataTable。
            System.Data.DataTable dataTable = new System.Data.DataTable(iSheet.SheetName);

            #region 设置列标题
            // 声明标题行。
            NPOI.SS.UserModel.IRow headRow = null;
            // 如果首行为标题行,则获取首行为标题行。
            if (firstRowIsColumnHead)
            {
                headRow = iSheet.GetRow(0);
            }
            // 初始化DataTable最大列数为设定的最大列数。
            int maxDataTableColumnCount = maxColumnCount;
            // 如果未设定最大列数。
            if (maxColumnCount <= 0)
            {
                // 如果标题行为空,DataTable的最大列数为Sheet的最大列数-起始列的索引。
                if (headRow == null)
                {
                    maxDataTableColumnCount = usedColumnCount - startColumnIndex;
                }
                else // 否则,DataTable的最大列数为标题行的列数-起始列的索引。
                {
                    maxDataTableColumnCount = headRow.LastCellNum - startColumnIndex;
                }
            }
            // 创建DataTable数据列。
            for (int dataTableColumnIndex = 0; dataTableColumnIndex < maxDataTableColumnCount; dataTableColumnIndex++)
            {
                // 如果标题行为空,则添加默认数据列。
                if (headRow == null)
                {
                    dataTable.Columns.Add("", typeof(object));
                }
                else // 如果标题行不为空。
                {
                    // 从设定的起始列开始获取标题行的单元格。
                    NPOI.SS.UserModel.ICell iCell = headRow.GetCell(startColumnIndex + dataTableColumnIndex);
                    // 如果单元格为空,则添加默认数据列。
                    if (iCell == null)
                    {
                        dataTable.Columns.Add("", typeof(object));
                    }
                    else // 如果不为空,则添加名称为单元格值的数据列。
                    {
                        dataTable.Columns.Add(iCell.ToString(), typeof(object));
                    }
                }
            }
            #endregion

            #region 读取数据
            // 如果起始行索引小于0,且首行最为标题行,则起始行的索引设置为1。
            if (startRowIndex <= 0 && firstRowIsColumnHead)
            {
                startRowIndex = 1;
            }
            // 循环读取Sheet中所有的数据行。
            for (int dataTableRowIndex = startRowIndex; dataTableRowIndex < usedRowCount; dataTableRowIndex++)
            {
                // 初始化DataTable数据行,并添加到DataTable。
                System.Data.DataRow dataRow = dataTable.NewRow();
                dataTable.Rows.Add(dataRow);
                // 初始化Sheet中的数据行。
                NPOI.SS.UserModel.IRow iRow = iSheet.GetRow(dataTableRowIndex);
                // 如果Sheet中的数据行不为空。
                if (iRow != null)
                {
                    // 初始化Sheet中的单元格。
                    NPOI.SS.UserModel.ICell iCell = null;
                    // 循环DataRow中的每一列。
                    for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
                    {
                        // 获取Sheet中的单元格,从设定的起始列开始
                        iCell = iRow.GetCell(startColumnIndex + dataTableColumnIndex);
                        // 如果Sheet中的单元格不为空,则将单元格的值赋给DataTable数据行对应的列。
                        if (iCell != null)
                        {
                            dataRow[dataTableColumnIndex] = Extension.NPOIMethod.GetCellValue(iCell);
                        }
                    }
                }
            }
            #endregion
            return(dataTable);
        }
Example #10
0
        // https://localhost:44397/Blog/OnPostImport
        // https://github.com/miladsoft/npoi/blob/master/npoi_Example/Controllers/HomeController.cs
        public ActionResult OnPostImport()
        {
            // using NPOI.HSSF.UserModel;
            // using NPOI.SS.UserModel;
            // using NPOI.XSSF.UserModel;


            // Microsoft.AspNetCore.Http.IFormFile file = Request.Form.Files[0];
            string fullPath = @"C:\Users\Administrator\Downloads\demo2.xlsx";

            System.IO.Stream baseStream = System.IO.File.OpenRead(@"C:\Users\Administrator\Downloads\demo.xlsx");
            Microsoft.AspNetCore.Http.IFormFile file = new Microsoft.AspNetCore.Http.FormFile(baseStream, 0, baseStream.Length, "thePostedFile", "demo.xlsx");

            // string folderName = "Upload";
            // string webRootPath = "_hostingEnvironment.WebRootPath";
            // string newPath = System.IO.Path.Combine(webRootPath, folderName);
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            // if (!System.IO.Directory.Exists(newPath))
            // {
            // System.IO.Directory.CreateDirectory(newPath);
            // }
            if (file.Length > 0)
            {
                string sFileExtension = System.IO.Path.GetExtension(file.FileName).ToLower();

                NPOI.SS.UserModel.IWorkbook workbook;
                NPOI.SS.UserModel.ISheet    sheet;
                // string fullPath = System.IO.Path.Combine(newPath, file.FileName);
                using (var stream = new System.IO.FileStream(fullPath, System.IO.FileMode.Create))
                {
                    file.CopyTo(stream);
                    stream.Position = 0;

                    if (sFileExtension == ".xls")
                    {
                        // This will read the Excel 97-2000 formats
                        // NPOI.HSSF.UserModel.HSSFWorkbook hssfwb = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
                        workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
                    }
                    else
                    {
                        // This will read 2007 Excel format
                        // NPOI.XSSF.UserModel.XSSFWorkbook hssfwb = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
                        workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
                    }

                    sheet = workbook.GetSheetAt(0);                     //get first sheet from workbook

                    NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); //Get Header Row
                    int cellCount = headerRow.LastCellNum;
                    sb.Append("<table class='table'><tr>");
                    for (int j = 0; j < cellCount; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                        if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
                        {
                            continue;
                        }
                        sb.Append("<th>" + cell.ToString() + "</th>");
                    }
                    sb.Append("</tr>");
                    sb.AppendLine("<tr>");
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
                    {
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        if (row.Cells.All(d => d.CellType == NPOI.SS.UserModel.CellType.Blank))
                        {
                            continue;
                        }
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
                            }
                        }
                        sb.AppendLine("</tr>");
                    }
                    sb.Append("</table>");
                }
            }

            return(this.Content(sb.ToString()));
        }
Example #11
0
        //public static System.Data.DataTable GetDataTableFromSheet(NPOI.SS.UserModel.ISheet sheet, bool ignoreBlankRow, int startRowIndex, int startColumnIndex, int columnCount, bool firstRowIsColumnHead)
        //{
        //    if (sheet == null) { return null; }
        //    System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName);
        //    NPOI.SS.UserModel.IRow row = null;



        //}
        #endregion

        #region 从Sheet中获取DataTable
        public static System.Data.DataTable GetDataTableFromSheet(NPOI.SS.UserModel.ISheet sheet, int startRowIndex, int startColumnIndex, bool firstRowIsColumnHead, bool autoAddColumn, bool ignoreBlankRow)
        {
            if (sheet == null)
            {
                return(null);
            }
            System.Data.DataTable  dataTable = new System.Data.DataTable(sheet.SheetName);
            NPOI.SS.UserModel.IRow row       = null;
            if (firstRowIsColumnHead)
            {
                row = sheet.GetRow(0);
            }
            else
            {
                row = sheet.GetRow(startRowIndex);
            }
            if (row != null)
            {
                for (int i = startColumnIndex; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dataTable.Columns.Add();
                    }
                    else
                    {
                        if (firstRowIsColumnHead)
                        {
                            dataTable.Columns.Add(cell.ToString());
                        }
                        else
                        {
                            dataTable.Columns.Add();
                        }
                    }
                }
            }
            if (startRowIndex == 0 && firstRowIsColumnHead)
            {
                startRowIndex = 1;
            }
            for (int rowIndex = startRowIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
            {
                row = sheet.GetRow(rowIndex);
                if (row != null)
                {
                    System.Data.DataRow dataRow = dataTable.NewRow();
                    dataTable.Rows.Add(dataRow);
                    int dataTableColumnIndex = 0;
                    for (int columnIndex = startColumnIndex; columnIndex < row.PhysicalNumberOfCells; columnIndex++)
                    {
                        if (dataTableColumnIndex >= dataTable.Columns.Count)
                        {
                            if (autoAddColumn)
                            {
                                dataTable.Columns.Add();
                            }
                            else
                            {
                                break;
                            }
                        }
                        dataRow[dataTableColumnIndex] = GetCellValue(row.GetCell(columnIndex));
                        dataTableColumnIndex++;
                    }
                }
            }
            return(dataTable);
        }