excel服务器草稿位置,文件读取草稿（excel，csv）

usingNPOI.XSSF.UserModel;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.IO;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceConsoleApplication3

{public classExcelHelper

{public static System.Data.DataSet GetTablesFromTxt(string path, string splitChar, int startLine, stringendWith)

{int i = 0;

System.Collections.ArrayList tablelist= newSystem.Collections.ArrayList();

System.Data.DataTable table= null;string s = "";

System.Data.DataSet ds= newSystem.Data.DataSet();//using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//{

System.Data.DataTable tmp= null;using (StreamReader sr = newStreamReader(path, System.Text.Encoding.Default))

{while (!string.IsNullOrEmpty(s =sr.ReadLine()))

{if (i >= startLine - 1)

{string[] list = s.Split(new string[] { splitChar }, StringSplitOptions.None);if (tmp == null)

{

tmp= newSystem.Data.DataTable();//table = new System.Data.DataTable();

foreach (string t inlist)

{

tmp.Columns.Add(newSystem.Data.DataColumn());

}

table=tmp.Clone();if (!string.IsNullOrEmpty(endWith) && list[0].Contains(endWith))

{break;

}var row =table.NewRow();for (var k = 0; k < list.Length; k++)

{

row[k]=list[k];

}

table.Rows.Add(row);

}else{var row =table.NewRow();for (var k = 0; k < list.Length; k++)

{

row[k]=list[k];

}

table.Rows.Add(row);/*if ((i + 1) % 200000 == 0)

{

ds.Tables.Add(table);

table = new System.Data.DataTable();

table = tmp.Clone();

}*/}

}

i++;

}if (table.Rows.Count > 0)

{

ds.Tables.Add(table);

}

}//}

returnds;

}///

///读取指定Excel所有Sheet///

/// 文件路径

///

public static DataSet ReadDataSet(stringpath)

{

DataSet retSet= newDataSet();using (FileStream stream = newFileStream(path, FileMode.Open, FileAccess.Read))

{

IWorkbook workbook=WorkbookFactory.Create(stream);var sheetCount =workbook.NumberOfSheets;for (int i = 0; i < sheetCount; i++)

{var sheet =workbook.GetSheetAt(i);

retSet.Tables.Add(ReadTable(sheet,0, 0));

}

}returnretSet;

}///

///读取指定索引Sheet的Excel文件内容，返回DataTable///

/// excel文件物理路径

/// 页签索引，从0开始

/// 表头索引，从0开始，如果没有表头，请填-1，如果表头在第二行，请填1

/// 数据最后一行索引，如果后三行是统计之类的，请填-3

/// 返回DataTable，TableName为对应SheetName

public static DataTable ReadTable(string path, int sheetIndex, int titleIndex, intlastRowDeduction)

{using (FileStream stream = newFileStream(path, FileMode.Open, FileAccess.Read))

{

IWorkbook workbook=WorkbookFactory.Create(stream);

ISheet sheet=workbook.GetSheetAt(sheetIndex);returnReadTable(sheet, titleIndex, lastRowDeduction);

}

}private static DataTable ReadTable(ISheet sheet, int titleIndex, intlastRowDeduction)

{var retDatTable = newDataTable();

retDatTable.TableName=sheet.SheetName;if (titleIndex < -2)

{throw new Exception("无效的表头索引值！最小值为-1！");

}

IRow headerRow= null;var hasHead = true;//无表头，纯数据

if (titleIndex == -1)

{

headerRow= sheet.GetRow(0);//仅用于取列数用

hasHead = false;

}else{

headerRow=sheet.GetRow(titleIndex);

}if (headerRow == null)

{returnretDatTable;

}int cellCount =headerRow.LastCellNum;for (int i = headerRow.FirstCellNum; i < cellCount; i++)

{//无表头

if (!hasHead)

{

retDatTable.Columns.Add("Column" +i);continue;

}//处理有表头的

var cell =headerRow.GetCell(i);var title = string.Empty;if (cell != null)

{

headerRow.GetCell(i).SetCellType(CellType.String);

title=cell.StringCellValue;

}else{

title=Guid.NewGuid().ToString();

}

retDatTable.Columns.Add(title);

}//最后一行的标号 即总的行数

int rowCount =sheet.LastRowNum;

rowCount+=lastRowDeduction;for (int i = (titleIndex + 1 - 1); i <= rowCount; i++)

{var row =sheet.GetRow(i);

DataRow dataRow=retDatTable.NewRow();for (int j = row.FirstCellNum; j < cellCount; j++)

{var cell =row.GetCell(j);if (cell != null)try{switch(cell.CellType)

{caseCellType.Numeric://NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型

if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型

{

dataRow[j]=cell.DateCellValue;

}else//其他数字类型

{

dataRow[j]=cell.NumericCellValue;

}break;caseCellType.Formula:

IFormulaEvaluator eva= null;var workType =sheet.Workbook.GetType();if (workType.Name == "XSSFWorkbook")

{

eva= newXSSFFormulaEvaluator(sheet.Workbook);

}else{

eva= newHSSFFormulaEvaluator(sheet.Workbook);

}

dataRow[j]=eva.Evaluate(cell).FormatAsString();break;caseCellType.Blank:

dataRow[j]= "";break;caseCellType.Unknown:caseCellType.Boolean:caseCellType.Error:caseCellType.String:

dataRow[j]=cell.StringCellValue; ;break;default:break;

}

}catch{ }

}

retDatTable.Rows.Add(dataRow);

}returnretDatTable;

}

}

}