本文为人人分享了C#导入导出Excel数据的详细代码,供人人参考,详细内容以下
注:关于实体类对象最好新建一个而且继续原有实体类,如许能够将范例举行修正;
要领一:此种要领是用EPPLUS中的FileInfo流举行读取的(是不是是流我还真不太相识,如有晓得请留言,非常感谢了)
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Abp.Extensions; namespace HYZT.Ltxy.International.Ctrip.Exporting { public class ExcelLib { public ICtripPolicyExcelImport GetExcel(string filePath) { if (filePath.Trim() .IsNullOrEmpty()) throw new Exception("文件名不能为空"); //由于这儿用得是EPPLUS对Excel举行的操纵,所以只能操纵 //2007今后的版本今后的(即扩大名为.xlsx) if (!filePath.Trim().EndsWith("xlsx")) throw new Exception("请使用office Excel 2007版本或2010版本"); else if (filePath.Trim().EndsWith("xlsx")) { ICtripPolicyExcelImport res = new CtripPolicyExcelImport(filePath.Trim()); return res; } else return null; } } }
要领接口:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HYZT.Ltxy.International.Ctrip.Exporting { public interface ICtripPolicyExcelImport { /// <summary> 翻开文件 </summary> bool Open(); //ExcelVersion Version { get; } /// <summary> 文件途径 </summary> string FilePath { get; set; } /// <summary> 文件是不是已翻开 </summary> bool IfOpen { get; } /// <summary> 文件包括事情表的数目 </summary> int SheetCount { get; } /// <summary> 当前事情表序号 </summary> int CurrentSheetIndex { get; set; } /// <summary> 猎取当前事情表中行数 </summary> int GetRowCount(); /// <summary> 猎取当前事情表中列数 </summary> int GetColumnCount(); /// <summary> 猎取当前事情表中某一行中单元格的数目 </summary> /// <param name="Row">行序号</param> int GetCellCountInRow(int Row); /// <summary> 猎取当前事情表中某一单元格的值(按字符串返回) </summary> /// <param name="Row">行序号</param> /// <param name="Col">列序号</param> string GetCellValue(int Row, int Col); /// <summary> 封闭文件 </summary> void Close(); } }
要领完成:
using OfficeOpenXml; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HYZT.Ltxy.International.Ctrip.Exporting { public class CtripPolicyExcelImport:ICtripPolicyExcelImport { public CtripPolicyExcelImport() { } public CtripPolicyExcelImport(string path) { filePath = path; } private string filePath = ""; private ExcelWorkbook book = null; private int sheetCount = 0; private bool ifOpen = false; private int currentSheetIndex = 0; private ExcelWorksheet currentSheet = null; private ExcelPackage ep = null; public bool Open() { try { ep = new ExcelPackage(new FileInfo(filePath)); if (ep == null) return false; book =ep.Workbook; sheetCount = book.Worksheets.Count; currentSheetIndex = 0; currentSheet = book.Worksheets[1]; ifOpen = true; } catch (Exception ex) { throw new Exception(ex.Message); } return true; } public void Close() { if (!ifOpen || ep == null) return; ep.Dispose(); } //public ExcelVersion Version //{ get { return ExcelVersion.Excel07; } } public string FilePath { get { return filePath; } set { filePath = value; } } public bool IfOpen { get { return ifOpen; } } public int SheetCount { get { return sheetCount; } } public int CurrentSheetIndex { get { return currentSheetIndex; } set { if (value != currentSheetIndex) { if (value >= sheetCount) throw new Exception("事情表序号超出范围"); currentSheetIndex = value; currentSheet =book.Worksheets[currentSheetIndex+1]; } } } public int GetRowCount() { if (currentSheet == null) return 0; return currentSheet.Dimension.End.Row; } public int GetColumnCount() { if (currentSheet == null) return 0; return currentSheet.Dimension.End.Column; } public int GetCellCountInRow(int Row) { if (currentSheet == null) return 0; if (Row >= currentSheet.Dimension.End.Row) return 0; return currentSheet.Dimension.End.Column; } //依据行号和列号猎取指定单元格的数据 public string GetCellValue(int Row, int Col) { if (currentSheet == null) return ""; if (Row >= currentSheet.Dimension.End.Row || Col >= currentSheet.Dimension.End.Column) return ""; object tmpO =currentSheet.GetValue(Row+1, Col+1); if (tmpO == null) return ""; return tmpO.ToString(); } } }
要领挪用完成功用:
//用于顺序是在当地,所以此时的途径是当地电脑的绝对路劲; //当顺序宣布后此途径应该是服务器上的绝对途径,所以在此之前还要有 //一项功用是将当地文件上传到服务器上的指定位置,此时在猎取途径即可 public string GetExcelToCtripPolicy(string filePath) { ExcelLib lib = new ExcelLib(); if (filePath == null) return new ReturnResult<bool>(false, "未找到响应文件"); string str= tmp.GetCellValue(i, j); return str; }
要领二:将Excel表格转化成DataTable表,然后在对DataTable举行营业操纵
using Abp.Application.Services; using OfficeOpenXml; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HYZT.Ltxy.International.Ctrip.GetExcelToDataTable { public class EPPlusHelperAppService:ApplicationService,IEPPlusHelperAppService { private static string GetString(object obj) { try { return obj.ToString(); } catch (Exception ex) { return ""; } } /// <summary> ///将指定的Excel的文件转换成DataTable (Excel的第一个sheet) /// </summary> /// <param name="fullFielPath">文件的绝对途径</param> /// <returns></returns> public DataTable WorksheetToTable(string filePath) { try { FileInfo existingFile = new FileInfo(filePath); ExcelPackage package = new ExcelPackage(existingFile); ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//选定 指定页 return WorksheetToTable(worksheet); } catch (Exception) { throw; } } /// <summary> /// 将worksheet转成datatable /// </summary> /// <param name="worksheet">待处置惩罚的worksheet</param> /// <returns>返回处置惩罚后的datatable</returns> public static DataTable WorksheetToTable(ExcelWorksheet worksheet) { //猎取worksheet的行数 int rows = worksheet.Dimension.End.Row; //猎取worksheet的列数 int cols = worksheet.Dimension.End.Column; DataTable dt = new DataTable(worksheet.Name); DataRow dr = null; for (int i = 1; i <= rows; i++) { if (i > 1) dr = dt.Rows.Add(); for (int j = 1; j <= cols; j++) { //默许将第一行设置为datatable的题目 if (i == 1) dt.Columns.Add(GetString(worksheet.Cells[i, j].Value)); //剩下的写入datatable else dr[j - 1] = GetString(worksheet.Cells[i, j].Value); } } return dt; } } }
之前我有一个顺序用的是要领一举行Excel导入的,速率不是很快,厥后我又用了第二种要领然则速率更慢了,究竟这两种要领哪一种快,请指点,照样我用第二种要领的时刻营业推断有题目,不得而知,就请明白人指点我究竟这两种要领哪一种比较好些。
以上就是C#完成导入导出Excel数据的两种要领详解的细致内容,更多请关注ki4网别的相干文章!