|
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.Office.Core; using Microsoft.Office.Interop.Excel; using System.IO; using System.Reflection; namespace Utility { public class ExcelHelper { private Application app; private Workbooks wbks; private _Workbook _wbk; private Sheets shs; private _Worksheet _wsh; private String filePath; public ExcelHelper(String filePath) { this.filePath = filePath; create(filePath); } public void create(String filePath) { app = new Application(); wbks = app.Workbooks; _wbk = wbks.Add(filePath); shs = _wbk.Sheets; if (shs.Count > 0) _wsh = shs[1]; } public Boolean setCellsTo(int row, int Column, String value) { Range r = _wsh.Cells[row, Column]; r.set_Value(value); return true; } public Boolean setCellsTo(int _WorksheetIndex, int row, int Column, String value) { if (!setWorksheet(_WorksheetIndex)) { return false; } return setCellsTo(row, Column, value); } public Boolean setCellsTo(String _WorksheetName, int row, int Column, String value) { return setCellsTo(getWorksheet(_WorksheetName), row, Column, value); } public Boolean setWorksheet(int index) { _wsh = shs[index]; return true; } public Boolean setWorksheet(String sheetName) { int index = getWorksheet(sheetName); if (index == -1) return false; return setWorksheet(index); } public int getWorksheet(String sheetName) { int index = 1; for (; index <= shs.Count; index++) { _Worksheet w = shs[index]; if (sheetName.Trim().Equals(w.Name.Trim())) return index; } return -1; } public String getCellsValue(int row, int Column) { Range r = _wsh.Cells[row, Column]; return r.Text; } public String getCellsValue(int _WorksheetIndex, int row, int Column) { if (!setWorksheet(_WorksheetIndex)) { throw new Exception("找不到该工作簿"); } return getCellsValue(row, Column); } public String getCellsValue(String _WorksheetIndex, int row, int Column) { if (!setWorksheet(_WorksheetIndex)) { throw new Exception("找不到该工作簿"); } return getCellsValue(row, Column); } public void close() { _wbk.Close(null, null, null); wbks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } public void save() { save(filePath); } public void save(String filePath) { //屏蔽掉系统跳出的Alert app.AlertBeforeOverwriting = false; //保存到指定目录 _wbk.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } // public List<String> getRow(int row, int startColumn, int endColumn, String stopStr) { List<String> cols = new List<string>(); for (int n = startColumn; startColumn < endColumn; n++) { String value = this.getCellsValue(row, n); if (value.Trim().Equals(stopStr) && stopStr != null) break; cols.Add(value); } return cols; } /// <summary> /// 遇到value= "" 返回 /// </summary> /// <param name="row"></param> /// <param name="startColumn"></param> /// <returns></returns> public List<String> getRow(int row, int startColumn) { return getRow(row, startColumn, int.MaxValue, ""); } public String[] getSheetNames() { String[] Names = new String[shs.Count]; for (int n = 1; n <= shs.Count; n++) { Names[n] = shs[n]; } return Names; } } } |
|
|
