/// <summary> /// 将EXCEL导出到DataTable /// </summary> /// <param name="excelPath">excel路径</param> /// <param name="sheetIndex">Worksheets『从0开始』</param> /// <returns>DataTable</returns> public static DataTable ToDataTable(string excelPath, int sheetIndex) { CheckedHanlder.CheckedExcelFileParamter(excelPath, true); XlsDocument _excelDoc = new XlsDocument(excelPath); DataTable _table = new DataTable(); Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex]; ushort _colCount = _sheet.Rows[1].CellCount; ushort _rowCount = (ushort)_sheet.Rows.Count; for (ushort j = 1; j <= _colCount; j++) { _table.Columns.Add(new DataColumn(j.ToString())); } for (ushort i = 1; i <= _rowCount; i++) { DataRow _row = _table.NewRow(); for (ushort j = 1; j <= _colCount; j++) { _row[j - 1] = _sheet.Rows[i].GetCell(j).Value; } _table.Rows.Add(_row); } return(_table); }
/// <summary> /// 遍历Excel 数据行 /// </summary> /// <param name="excelPath">excel路径</param> /// <param name="sheetName">sheet名称</param> /// <param name="startRowIndex">遍历起始行『从0开始』</param> /// <param name="foreachRowFactory">遍历规则『委托』</param> /// 时间:2015-12-08 10:46 /// 备注: public static void ForeachExcel(string excelPath, string sheetName, ushort startRowIndex, Action <Row> foreachRowFactory) { CheckedHanlder.CheckedExcelFileParamter(excelPath, true); XlsDocument _excelDoc = new XlsDocument(excelPath); Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetName]; int _colCount = _sheet.Rows[startRowIndex].CellCount; int _rowCount = _sheet.Rows.Count; for (ushort i = startRowIndex; i < _rowCount; i++) { Row _rows = _sheet.Rows[i]; foreachRowFactory(_rows); } }
/// <summary> /// 遍历excel数据 /// </summary> /// <param name="excelPath">excel路径</param> /// <param name="sheetIndex">Worksheets『从0开始』</param> /// <param name="startRowIndex">遍历起始行『从0开始』</param> /// <param name="startColIndex">遍历起始列『从0开始』</param> /// <param name="foreachRowFactory">遍历规则『委托』</param> public static void ForeachExcel(string excelPath, int sheetIndex, ushort startRowIndex, ushort startColIndex, Action <string, int, int, object> foreachRowFactory) { CheckedHanlder.CheckedExcelFileParamter(excelPath, true); XlsDocument _excelDoc = new XlsDocument(excelPath); Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex]; int _colCount = _sheet.Rows[startRowIndex].CellCount; int _rowCount = _sheet.Rows.Count; for (ushort i = startRowIndex; i < _rowCount; i++) { for (ushort j = startColIndex; j <= _colCount; j++) { string _colName = _sheet.Rows[i].GetCell(j).Value.ToString(); object _value = _sheet.Rows[i].GetCell(j).Value; foreachRowFactory(_colName, j, i, _value); } } }
/// <summary> /// 将集合导出到excel /// </summary> /// <typeparam name="T">泛型</typeparam> /// <param name="source">数据源</param> /// <param name="excelPath">保存路径</param> /// <param name="sheetName">sheet名称</param> public static void ToExecel <T>(IEnumerable <T> source, string excelPath, string sheetName) where T : class { CheckedHanlder.CheckedExcelFileParamter(excelPath, false); CheckedHanlder.CheckedExcelExportParamter(source, sheetName); int _recordCnt = source.Count(); XlsDocument _xls = new XlsDocument(); string _savePath = FileHelper.GetExceptName(excelPath); _xls.FileName = FileHelper.GetFileName(excelPath); XF _columnStyle = SetColumnStyle(_xls); Worksheet _sheet = _xls.Workbook.Worksheets.Add(sheetName); int _celIndex = 0, _rowIndex = 1; Cells _cells = _sheet.Cells; IDictionary <string, string> _fields = ReflectHelper.GetPropertyName <T>(); string[] _colNames = new string[_fields.Count]; _fields.Values.CopyTo(_colNames, 0); foreach (string col in _colNames) { _celIndex++; _cells.Add(1, _celIndex, col, _columnStyle); } foreach (T item in source) { _rowIndex++; _celIndex = 0; foreach (KeyValuePair <string, string> proItem in _fields) { _celIndex++; object _provalue = typeof(T).InvokeMember(proItem.Key, BindingFlags.GetProperty, null, item, null); XF _cellStyle = SetCellStyle(_xls, _provalue.GetType()); _cells.Add(_rowIndex, _celIndex, _provalue.ToStringOrDefault(string.Empty), _cellStyle); } } _xls.Save(_savePath, true); }
/// <summary> /// 将DataTable导出到Excel /// </summary> /// <param name="table">DataTable</param> /// <param name="excelPath">保存路径</param> /// <param name="sheetName">Sheet名字</param> /// 时间:2015-12-08 11:13 /// 备注: public static void ToExecel(DataTable table, string excelPath, string sheetName) { CheckedHanlder.CheckedExcelFileParamter(excelPath, false); CheckedHanlder.CheckedExcelExportParamter(table, sheetName); int _recordCnt = table.Rows.Count; XlsDocument _xls = new XlsDocument(); string _savePath = excelPath.Substring(0, excelPath.LastIndexOf(@"\") + 1); _xls.FileName = FileHelper.GetFileName(excelPath); XF _columnStyle = SetColumnStyle(_xls); Worksheet _sheet = _xls.Workbook.Worksheets.Add(sheetName); int _celIndex = 1, _rowIndex = 2; Cells _cells = _sheet.Cells; foreach (DataColumn column in table.Columns) { _cells.Add(1, _celIndex, column.ColumnName, _columnStyle); _celIndex++; } _celIndex = 1; foreach (DataRow t in table.Rows) { foreach (DataColumn column in table.Columns) { XF _cellStyle = SetCellStyle(_xls, column.DataType); Cell _cell = _cells.Add(_rowIndex, _celIndex, t[column.ColumnName], _cellStyle); _celIndex++; } _celIndex = 1; _rowIndex++; } _xls.Save(_savePath, true); }