EXCEL读

来源：互联网发布：淘宝魔镜怎么使用编辑：程序博客网时间：2024/05/17 01:34

            object missing = System.Reflection.Missing.Value;            Application excel = new Application();//lauch excel application            if (excel == null)            {                return list;            }            else            {                excel.Visible = false;                excel.UserControl = true;                // 以只读的形式打开EXCEL文件                Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,                 missing, missing, missing, true, missing, missing, missing, missing, missing);                //取得第一个工作薄                string[] names = new string[wb.Worksheets.Count];                for (int i = 0; i < wb.Worksheets.Count; i++)                {                    names[i] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i + 1]).Name;                    if (!sheetNames.Contains(names[i])) continue;                    list.Add(new KeyValuePair<string, List<BatchTemplate1>>(names[i], new List<BatchTemplate1>()));                    Worksheet ws = (Worksheet)wb.Worksheets.get_Item(i + 1);                    //取得总记录行数   (包括标题列)                    int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数                    //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数                    //取得数据范围区域 (不包括标题列)                     Range rng1 = ws.Cells.get_Range("A2", "C" + rowsint);   //item                    object[,] arryItem = (object[,])rng1.Value2;   //get range's value                    for (int j = 1; j < rowsint; j++)                    {                        if (arryItem[j, 1] == null) continue;                        if (arryItem[j, 2] == null) continue;                        list[names[i]].Add(new BatchTemplate1() { Number_OldName = arryItem[j, 1].ToString(), Name = arryItem[j, 2].ToString() });                    }                }            }            excel.Quit();            excel = null;            Process[] procs = Process.GetProcessesByName("excel");            foreach (Process pro in procs)            {                pro.Kill();//没有更好的方法,只有杀掉进程            }            GC.Collect();

NPOI：

            IWorkbook workbook = null;            string extension = System.IO.Path.GetExtension(strFileName);            try            {                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))                {                    if (extension.Equals(".xls"))                    {                        workbook = new HSSFWorkbook(file);                    }                    else                    {                        workbook = new XSSFWorkbook(file);                    }                }            }            catch (Exception e)            {                throw e;            }            string[] names = new string[workbook.NumberOfSheets];                        for (int i = 0; i < workbook.NumberOfSheets; i++)            {                ISheet sheet = workbook.GetSheetAt(i);                names[i] = sheet.SheetName;                if (!sheetNames.Contains(names[i])) continue;                list.Add(new KeyValuePair<string, List<BatchTemplate2>>(names[i], new List<BatchTemplate2>()));                //取得总记录行数   (包括标题列)                int rowsint = sheet.LastRowNum; //得到行数                //取得数据范围区域 (不包括标题列)                                 for (int j = 1; j < rowsint; j++)                {                    IRow row = sheet.GetRow(j);  //读取当前行数据                    if (row != null)                    {                        if (row.GetCell(0) == null || row.GetCell(0).ToString().Length == 0) continue;                        if (row.GetCell(1) == null || row.GetCell(1).ToString().Length == 0) continue;                        if (row.GetCell(2) == null || row.GetCell(2).ToString().Length == 0) continue;                        list[names[i]].Add(new BatchTemplate2()                        {                            Number_OldName = row.GetCell(0).ToString(),                            X = row.GetCell(1).ToString(),                            Y = row.GetCell(2).ToString(),                            Z = row.GetCell(3).ToString(),                        });                    }                }                                }

阅读全文

0 0