打开跟读取excel的内容

最新推荐文章于 2024-07-07 17:59:21 发布

weixin_34409357

最新推荐文章于 2024-07-07 17:59:21 发布

阅读量69

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/modu-xl/archive/2011/11/23/2259943.html

本文介绍了一种利用Excel模板和VB.NET代码实现的数据批量导入方法，具体步骤包括打开模板、遍历工作表、读取指定单元格数据并插入数据库。

 1     private void OpenExcel(string strExcelName,string strTempletPath, string dayTime)
 2     {
 3         ExcelApplication exc = new ExcelApplication();
 4         exc.DisplayAlerts = false;
 5         exc.EnableEvents = false;
 6         DirectoryInfo dir = new DirectoryInfo(ReportPathDispatch);
 7         Workbook workbook = exc.Workbooks.Open(strTempletPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 8            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 9            Type.Missing, Type.Missing, Type.Missing);
10         exc.DisplayAlerts = false;
11         exc.AlertBeforeOverwriting = false;
12         exc.AskToUpdateLinks = false;
13         for (int k = 1; k <= workbook.Worksheets.Count; k++)//这只是为了找到excel中相应的sheet文件，没找到直接的方法，只能遍历下所有sheet来寻找
14         {
15             _Worksheet worksheet = (_Worksheet)workbook.Worksheets.get_Item(k);
16             worksheet = (_Worksheet)workbook.Worksheets.get_Item(k);
17             if (worksheet.Name == dayTime)
18             {
19 
20                 for (int row = 5; row < worksheet.UsedRange.Rows.Count + 1; row++)
21                 {
22                     string strNYCode = worksheet.get_Range(worksheet.Cells[row, 2], worksheet.Cells[row, 2]).Text.ToString().Trim();
23                     //string strGetStr = worksheet.get_Range("D5", System.Type.Missing).Text.ToString().Trim();
24                     string strNYName = worksheet.get_Range(worksheet.Cells[row, 3], worksheet.Cells[row, 3]).Text.ToString().Trim();
25                     string strActWT = worksheet.get_Range(worksheet.Cells[row, 4], worksheet.Cells[row, 4]).Text.ToString().Trim();
26                     string strComName = worksheet.get_Range(worksheet.Cells[row, 5], worksheet.Cells[row, 5]).Text.ToString().Trim();
27                     string strNote = worksheet.get_Range(worksheet.Cells[row, 6], worksheet.Cells[row, 6]).Text.ToString().Trim();
28                     insertIntoMes(dayTime, strNYCode, strNYName, strActWT, strComName, strNote);
29                 }
30             }
31             
32         }
33  }

strTempletPath是包括文件名在内的路径，例如：D:\ems\EMSReport\Report\调度报表\能源调度日报表_2011-10.xls

ExcelApplication exc = newExcelApplication(); 是添加了这个引用的缘故

using ExcelApplication = Excel.Application;

另外22跟23行都是读取对应单元格的方法。

转载于:https://www.cnblogs.com/modu-xl/archive/2011/11/23/2259943.html