コード例 #1
0
        public static void Import(string fileName, ushort startRow = 2, ushort startCol = 1, int colCount = 4, ImportDataHandle callback = null)
        {
            try
            {
                //XlsDocument xls = new XlsDocument(fileName);//新建Excel
                IWorkbook         xls     = null;
                IFormulaEvaluator formula = null;
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    xls     = new XSSFWorkbook(fs);
                    formula = new XSSFFormulaEvaluator(xls);
                }

                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    xls     = new HSSFWorkbook(fs);
                    formula = new HSSFFormulaEvaluator(xls);
                }


                if (xls.NumberOfSheets > 0)
                {
                    ISheet ws = xls.GetSheetAt(0);
                    for (ushort i = (ushort)(startRow - 1); i <= ws.LastRowNum; i++)
                    {
                        string[] strs     = new string[colCount];
                        bool     isnull   = true;
                        bool     iserror  = false;
                        int      errorRow = 0;
                        string   msg      = "";
                        int      end      = colCount + startCol;
                        //                         if (end > ws.Rows[i].CellCount)
                        //                         {
                        //                             end = ws.Rows[i].CellCount;
                        //                         }
                        IRow row = ws.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        for (ushort j = (ushort)(startCol - 1); j < end - 1; j++)
                        {
                            try
                            {
                                var cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    continue;
                                }
                                string str = cell.ToString();
                                switch (cell.CellType)
                                {
                                case CellType.Blank:     //空数据类型处理
                                {
                                    str = "";
                                }
                                break;

                                case CellType.Formula:
                                {
                                    cell = formula.EvaluateInCell(cell);
                                    str  = cell.ToString();
                                }
                                break;

                                case CellType.String:
                                {
                                    str = cell.StringCellValue;
                                }
                                break;

                                case CellType.Numeric:     //数字类型
                                {
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                                    {
                                        str = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                                    }
                                    else
                                    {
                                        str = cell.NumericCellValue.ToString();
                                    }
                                }
                                break;

                                default:
                                    break;
                                }

                                strs[j + 1 - startCol] = str;
                                if (!string.IsNullOrWhiteSpace(str))
                                {
                                    isnull = false;
                                }
                            }
                            catch (Exception ex)
                            {
                                iserror  = true;
                                msg      = ex.Message;
                                errorRow = i + 1;
                                //throw;
                            }
                        }


                        if (isnull)
                        {
                            continue;
                        }
                        if (callback != null)
                        {
                            callback(strs, iserror, errorRow, msg);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
コード例 #2
0
 public static void ImportEx(out bool iscancel, ushort startRow = 2, ushort startCol = 1, int colCount = 4, ImportDataHandle callback = null)
 {
     try
     {
         string    fileName = null;
         Exception ex1      = null;
         iscancel = false;
         Application.OpenForms[0].Invoke(new Action(() =>
         {
             try
             {
                 OpenFileDialog ofd = new OpenFileDialog();
                 ofd.Filter         = "Excel(*.xls)|*.xls|所有文件(*.*)|*.*";
                 if (ofd.ShowDialog() == DialogResult.OK)
                 {
                     fileName = ofd.FileName;
                 }
             }
             catch (Exception ex)
             {
                 ex1 = ex;
             }
         }));
         if (ex1 != null)
         {
             throw ex1;
         }
         if (fileName == null)
         {
             iscancel = true;
             return;
         }
         Import(fileName, startRow, startCol, colCount, callback);
     }
     catch (Exception ex)
     {
         throw;
     }
 }
コード例 #3
0
        public static void Import(string fileName, ushort startRow = 2, ushort startCol = 1, int colCount = 4, ImportDataHandle callback = null)
        {
            try
            {
                XlsDocument xls = new XlsDocument(fileName);//新建Excel
                if (xls.Workbook.Worksheets.Count > 0)
                {
                    Worksheet ws = xls.Workbook.Worksheets[0];
                    for (ushort i = startRow; i < ws.Rows.Count; i++)
                    {
                        string[] strs     = new string[colCount];
                        bool     isnull   = true;
                        bool     iserror  = false;
                        int      errorRow = 0;
                        string   msg      = "";
                        int      end      = colCount + startCol;
//                         if (end > ws.Rows[i].CellCount)
//                         {
//                             end = ws.Rows[i].CellCount;
//                         }

                        for (ushort j = startCol; j < end; j++)
                        {
                            try
                            {
                                if (ws.Rows[i].CellExists(j))
                                {
                                    string str = Convert.ToString(ws.Rows[i].CellAtCol(j).Value);
                                    strs[j - startCol] = str;
                                    if (!string.IsNullOrWhiteSpace(str))
                                    {
                                        isnull = false;
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                iserror  = true;
                                msg      = ex.Message;
                                errorRow = i + 1;
                                //throw;
                            }
                        }


                        if (isnull)
                        {
                            continue;
                        }
                        if (callback != null)
                        {
                            callback(strs, iserror, errorRow, msg);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }