首页 > 代码库 > C#之Excel操作
C#之Excel操作
下面的这几个方法是我在项目中经常用到的，欢迎大家批评指正
读取Excel表中的数据
第一种：功能丰富，速度慢
/// <summary> /// 从Excel读取数据 /// </summary> /// <param name="path">Excel文档的全路径</param> /// <returns>将一行作为一个对象，所以返回的是很多行数据的对象</returns> public Model[] ReadDataFromExcel(string path) { List<Model> list = new List<Model>(); Application excelApp = null; try { excelApp = new Application() { Visible = false, DefaultFilePath = "", DisplayAlerts = true, SheetsInNewWorkbook = 1 }; Workbooks books = excelApp.Workbooks; if (File.Exists(path)) { Workbook book = books.Open(path); Worksheet sheet = book.Sheets.get_Item(1); if (sheet != null) { int rowNum = sheet.UsedRange.Rows.Count; DateTime timeLimit; timeLimit = GetTime(((Range)sheet.Cells[2, "B"]).Text.ToString()).AddSeconds(sectionNum*timeSpan*60); //从第2行开始读数据 for (int i = 2; i < rowNum; i++) { if (((Range)sheet.Cells[i, "A"]).Text != "" && ((Range)sheet.Cells[i, "B"]).Text != "") { if (timeLimit.CompareTo(GetTime(((Range)sheet.Cells[i, "B"]).Text.ToString())) < 0) { break; } else { Model m = new Model() { SN = ((Range)sheet.Cells[i, "A"]).Text.ToString(), time = GetTime(((Range)sheet.Cells[i, "B"]).Text.ToString()), ch1 = ((Range)sheet.Cells[i, "C"]).Text.ToString(), ch2 = ((Range)sheet.Cells[i, "D"]).Text.ToString(), ch3 = ((Range)sheet.Cells[i, "E"]).Text.ToString(), ch4 = ((Range)sheet.Cells[i, "F"]).Text.ToString() }; list.Add(m); } } } } } } catch (Exception ex) { throw new Exception(ex.Message); } finally { //执行完读取Excel数据后，将关闭该进程 KillExcel(excelApp); } return list.ToArray(); }
第二种：速度快
public DataTable ExcelToDS(string filePath) { string connStr = ""; string fileType = System.IO.Path.GetExtension(Path.GetFileName(filePath)); if (string.IsNullOrEmpty(fileType)) { return null; } if (fileType == ".xls") connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; OleDbConnection conn = new OleDbConnection(connStr); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; DataSet ds = null; strExcel = "select * from [sheet1$]"; myCommand = new OleDbDataAdapter(strExcel, connStr); ds = new DataSet(); myCommand.Fill(ds, "table1"); return ds.Tables[0]; }
关闭Excel进程：
[DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hwnd, out int processId); public void KillExcel(Application excelApp) { try { if(excelApp!=null) { int processId; GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd), out processId); Process p = Process.GetProcessById(processId); p.Kill(); } } catch (Exception ex) { throw ex; } }
读取Excel单元格数据：
/// <summary> /// 获取单元格数据 /// </summary> /// <param name="sheet"></param> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> public string GetValue(Worksheet sheet, int row, int col) { // 取得单元格. var cell = (Range)sheet.Cells[row, col]; if ((bool)cell.MergeCells == true) { // 本单元格是 “合并单元格” if (cell.MergeArea.Row == row && cell.MergeArea.Column == col) { // 当前单元格 就是 合并单元格的 左上角 内容. return cell.Text.ToString(); } else { // 返回 合并单元格的 左上角 内容. return ((Range)(sheet.Cells[cell.MergeArea.Row, cell.MergeArea.Column])).Text.ToString(); } } else { // 本单元格是 “普通单元格” // 获取文本信息. return cell.Text.ToString(); } }
根据文本获取该文本所在行号和所占的行数，一般主要用来读取合并单元格
/// <summary> /// 根据文本获取行号和所占行数 /// </summary> /// <param name="sheet">查询的表单</param> /// <param name="text">查询的文本</param> /// <param name="rowNumber">行号</param> /// <param name="rowCounts">所占行数</param> public void GetRowNumber(Worksheet sheet, string text,out int rowNumber,out int rowCounts) { rowNumber = -1; rowCounts = -1; foreach(Range range in sheet.UsedRange) { if (range.Value =http://www.mamicode.com/= null) continue; if (range.Value.ToString() == text) { rowNumber = range.Row; if ((bool)range.MergeCells == true) rowCounts = range.MergeArea.Rows.Count; else rowCounts = 1; break; } } }
C#之Excel操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。