devbox
小小林熬夜学编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

导入导出excel

作者：小小林熬夜学编程 | 2024-03-09 18:31:52

赞

踩

导入导出excel

调用Office组件

  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Text;
  7. using System.Windows.Forms;
  8.  
  9. using ZG.ERP.App.AppPublic;
  10. using ZG.ERP.Common.SysParameter;
  11. using ZG.ERP.Common.Data;
  12. using ZG.ERP.Common.Utility;
  13. using System.IO;
  14. using System.Diagnostics;
  15. using System.Data.OleDb;
  16. using System.Runtime.InteropServices;
  17.  
  18.  
  19.  
  20.  //打开并读取Excel模板
  21.             Microsoft.Office.Interop.Excel.Application application; //这是一个客户端
  22.             Microsoft.Office.Interop.Excel.Workbooks workbooks; //所有工作薄
  23.             Microsoft.Office.Interop.Excel.Worksheet worksheet;//工作表
  24.             Microsoft.Office.Interop.Excel.Workbook workbook; //所用到的工作表
  25.  
  26.             application = new Microsoft.Office.Interop.Excel.Application();
  27.  
  28.             try
  29.             {
  30.                 workbooks = application.Workbooks;
  31.                 workbook = workbooks.Open(
  32.                         filePath, Type.Missing, Type.Missing,
  33.                         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  34.                         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  35.                         Type.Missing, Type.Missing);
  36.                 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
  37.                 Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
  38.  
  39.                 //dtExcelData.Columns.Add("SKU编号");
  40.                 dtExcelData.Columns.Add("品名");
  41.                 dtExcelData.Columns.Add("材质");
  42.                 dtExcelData.Columns.Add("钢厂");
  43.                 dtExcelData.Columns.Add("规格");
  44.                 dtExcelData.Columns.Add("预定数量(件)");
  45.                 dtExcelData.Columns.Add("预定重量(吨)");
  46.                 dtExcelData.Columns.Add("预付单价(元)");
  47.                 dtExcelData.Columns.Add("预付款小计(元)");              
  48.                 dtExcelData.Columns.Add("备注");
  49.  
  50.               
  51.                 string productName;
  52.                 string material;
  53.                 string factory;
  54.                 string specification;
  55.                 string FLAN_num;
  56.                 string Quantity;
  57.                 string FLAN_price;
  58.                 string totalPiece;                
  59.                 string remark;
  60.  
  61.                 //循环读取Excel内容放入DataTable
  62.                 for (int i = 2; i <= worksheet.UsedRange.Rows.Count; i++)
  63.                 {
  64.                     if (((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2 == null)
  65.                     {
  66.                         continue;
  67.                     }                 
  68.                     productName = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 1]).Value2 != null ?
  69.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 1]).Value2.ToString() : string.Empty;
  70.                     material = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2 != null ?
  71.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 2]).Value2.ToString() : string.Empty;
  72.                     factory = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 3]).Value2 != null ?
  73.                       ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 3]).Value2.ToString() : string.Empty;
  74.                     specification = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 4]).Value2 != null ?
  75.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 4]).Value2.ToString() : string.Empty;
  76.                     Quantity = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 5]).Value2 != null ?
  77.                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 5]).Value2.ToString() : string.Empty;
  78.                     FLAN_num = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 6]).Value2 != null ?
  79.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 6]).Value2.ToString() : string.Empty;
  80.                     FLAN_price = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 7]).Value2 != null ?
  81.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 7]).Value2.ToString() : string.Empty;
  82.                     totalPiece = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 8]).Value2 != null ?
  83.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 8]).Value2.ToString() : string.Empty;
  84.                     remark = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 9]).Value2 != null ?
  85.                         ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, 9]).Value2.ToString() : string.Empty;
  86.  
  87.                     dtExcelData.Rows.Add(new object[] { productName, material,factory, specification,Quantity, FLAN_num,
  88.                                                     FLAN_price, totalPiece, remark});
  89.                 }
  90.  
  91.                 workbook.Close(Type.Missing, filePath, Type.Missing);
  92.                 workbooks.Close();
  93.             }
  94.             catch { }
  95.             finally
  96.             {
  97.                 application.Quit();
  98.  
  99.                 //杀Excel进程
  100.                 IntPtr t = new IntPtr(application.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 
  101.                 int k = 0;
  102.                 GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k 
  103.                 System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 
  104.                 p.Kill(); //关闭进程k 
  105.             }
  106.

 

第二种，利用NPOI开源组件导入导出

 

  1. //===============================================================================
  2. //功    能：NPOI开源组件导出导入EXCEL
  3. //作    者：段晓锋
  4. //创建日期：2011年11月11日
  5. //修改历史
  6. //修 改 人：
  7. //修改日期：
  8. //修改描述：
  9. //===============================================================================
  10. using System;
  11. using System.Collections.Generic;
  12. using System.Linq;
  13. using System.Text;
  14. using System.IO;
  15. using System.Data;
  16.  
  17. using ZG.ERP.Common.SysParameter;
  18. using ZG.ERP.Common.Data;
  19. using ZG.ERP.Common.Utility;
  20.  
  21. using NPOI;
  22. using NPOI.HPSF;
  23. using NPOI.HSSF;
  24. using NPOI.POIFS;
  25. using NPOI.HSSF.Util;
  26. using NPOI.SS.UserModel;
  27. using NPOI.HSSF.UserModel;
  28. using NPOI.Util;
  29. namespace ZG.ERP.Common.Utility
  30. {
  31.     /// <summary>
  32.     /// NPOI导出导入帮助类
  33.     /// </summary>
  34.     public  class NPOIHelper
  35.     {
  36.         #region NPOI开源组件导出EXCEL方法
  37.         /// <summary>
  38.         /// DataTable导出到Excel的MemoryStream
  39.         /// </summary>
  40.         /// <param name="dtSource">源DataTable</param>
  41.         /// <param name="strHeaderText">表头文本</param>
  42.         public static MemoryStream Export(DataTable dtSource, string strHeaderText, out string errorstr)
  43.         {
  44.             errorstr = "";
  45.             try
  46.             {               
  47.                 HSSFWorkbook workbook = new HSSFWorkbook();
  48.                 ISheet sheet = workbook.CreateSheet();
  49.  
  50.                 #region 右击文件 属性信息
  51.                 {
  52.                     DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
  53.                     dsi.Company = "NPOI";
  54.                     workbook.DocumentSummaryInformation = dsi;
  55.  
  56.                     SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
  57.                     si.Author = "找钢网"; //填加xls文件作者信息
  58.                     si.ApplicationName = "找钢网ERP"; //填加xls文件创建程序信息
  59.                     si.LastAuthor = SysConfig.LoginUserInfo.LoginName; //填加xls文件最后保存者信息
  60.                     si.Comments = SysConfig.LoginUserInfo.LoginName;  //填加xls文件作者信息
  61.                     si.Title = "找钢网"; //填加xls文件标题信息
  62.                     si.Subject = "找钢网的码单表格";//填加文件主题信息
  63.                     si.CreateDateTime = DateTime.Now;
  64.                     workbook.SummaryInformation = si;
  65.                 }
  66.                 #endregion
  67.  
  68.  
  69.                 //取得列宽
  70.                 int[] arrColWidth = new int[dtSource.Columns.Count];
  71.                 foreach (DataColumn item in dtSource.Columns)
  72.                 {
  73.                     arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  74.                 }
  75.                 for (int i = 0; i < dtSource.Rows.Count; i++)
  76.                 {
  77.                     for (int j = 0; j < dtSource.Columns.Count; j++)
  78.                     {
  79.                         int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
  80.                         if (intTemp > arrColWidth[j])
  81.                         {
  82.                             arrColWidth[j] = intTemp;
  83.                         }
  84.                     }
  85.                 }
  86.                 int rowIndex = 0;
  87.  
  88.                 IRow row0 = sheet.CreateRow(0);//在工作表中添加一行 
  89.                 ICellStyle headStyle = workbook.CreateCellStyle();
  90.                 //headStyle.Alignment =CellHorizontalAlignment.CENTER;
  91.                 IFont font = workbook.CreateFont();
  92.                 font.FontHeightInPoints = 10;
  93.                 font.Boldweight = 700;
  94.                 headStyle.SetFont(font);
  95.                 foreach (DataColumn column in dtSource.Columns)
  96.                 {
  97.                     row0.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  98.                     row0.GetCell(column.Ordinal).CellStyle = headStyle;
  99.  
  100.                     //设置列宽
  101.                     sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
  102.                 }
  103.                 rowIndex = 1;
  104.                 foreach (DataRow row in dtSource.Rows)
  105.                 {
  106.                     //#region 新建表，填充表头，填充列头，样式
  107.                     //if (rowIndex == 65535 || rowIndex == 0)
  108.                     //{
  109.                     //    if (rowIndex != 0)
  110.                     //    {
  111.                     //        sheet = workbook.CreateSheet();
  112.                     //    }
  113.                     //    rowIndex = 2;
  114.                     //}
  115.                     //#endregion 
  116.  
  117.                     #region 填充内容
  118.                     IRow dataRow = sheet.CreateRow(rowIndex);
  119.                     foreach (DataColumn column in dtSource.Columns)
  120.                     {
  121.                         ICell newCell = dataRow.CreateCell(column.Ordinal);
  122.  
  123.                         string drValue = row[column].ToString();
  124.  
  125.                         switch (column.DataType.ToString())
  126.                         {
  127.                             case "System.String"://字符串类型
  128.                                 newCell.SetCellValue(drValue);
  129.                                 break;
  130.                             case "System.DateTime"://日期类型
  131.                                 DateTime dateV;
  132.                                 DateTime.TryParse(drValue, out dateV);
  133.                                 newCell.SetCellValue(dateV);
  134.                                 break;
  135.                             case "System.Boolean"://布尔型
  136.                                 bool boolV = false;
  137.                                 bool.TryParse(drValue, out boolV);
  138.                                 newCell.SetCellValue(boolV);
  139.                                 break;
  140.                             case "System.Int16"://整型
  141.                             case "System.Int32":
  142.                             case "System.Int64":
  143.                             case "System.Byte":
  144.                                 int intV = 0;
  145.                                 int.TryParse(drValue, out intV);
  146.                                 newCell.SetCellValue(intV);
  147.                                 break;
  148.                             case "System.Decimal"://浮点型
  149.                             case "System.Double":
  150.                                 double doubV = 0;
  151.                                 double.TryParse(drValue, out doubV);
  152.                                 newCell.SetCellValue(doubV);
  153.                                 break;
  154.                             case "System.DBNull"://空值处理
  155.                                 newCell.SetCellValue("");
  156.                                 break;
  157.                             default:
  158.                                 newCell.SetCellValue("");
  159.                                 break;
  160.                         }
  161.  
  162.                     }
  163.                     #endregion
  164.  
  165.                     rowIndex++;
  166.                 }
  167.                 using (MemoryStream ms = new MemoryStream())
  168.                 {
  169.                     workbook.Write(ms);
  170.                     ms.Flush();
  171.                     ms.Position = 0;
  172.                     //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
  173.                     return ms;
  174.                 }
  175.             }
  176.             catch(Exception ex)
  177.             {
  178.                 errorstr = ex.Message.ToString();
  179.                 return null;
  180.             }
  181.         }
  182.  
  183.         #endregion
  184.  
  185.         #region NPOI开源组件导入EXCEL方法
  186.         /// <summary>
  187.         /// xls导入到datatable
  188.         /// </summary>
  189.         /// <param name="dtSource">源DataTable</param>
  190.         /// <param name="strHeaderText">表头文本</param>
  191.         public static DataTable Import(string filePath, out string errorStr)
  192.         {
  193.             try
  194.             {
  195.                 errorStr = "";
  196.                 HSSFWorkbook hssfworkbook;
  197.                 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
  198.                 {
  199.                     hssfworkbook = new HSSFWorkbook(file);
  200.                 }
  201.                 ISheet sheet = hssfworkbook.GetSheetAt(0);
  202.                 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
  203.  
  204.                 DataTable dt = new DataTable();
  205.                 for (int j = 0; j < 12; j++)
  206.                 {
  207.                     dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
  208.                 }
  209.                 while (rows.MoveNext())
  210.                 {
  211.                     IRow row = (HSSFRow)rows.Current;
  212.                     DataRow dr = dt.NewRow();
  213.  
  214.                     for (int i = 0; i < row.LastCellNum; i++)
  215.                     {
  216.                         ICell cell = row.GetCell(i);
  217.                         if (cell == null)
  218.                         {
  219.                             dr[i] = null;
  220.                         }
  221.                         else
  222.                         {
  223.                             dr[i] = cell.ToString();
  224.                         }
  225.                     }
  226.                     dt.Rows.Add(dr);
  227.                 }
  228.                 return dt;
  229.             }
  230.             catch(Exception ex)
  231.             {
  232.                 errorStr = ex.Message.ToString();
  233.                 return null;
  234.             }
  235.         }
  236.         #endregion
  237.     }
  238. }

 

 　NPOI开源地址：http://npoi.codeplex.com/

 
　　MyXls开源地址： http://sourceforge.net/projects/myxls/
 

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/216109
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号