/// <summary>
/// 文件流转成DataTable
/// </summary>
/// <param name="file">文件</param>
/// <returns></returns>
public DataTable GetExcelData(HttpPostedFileBase file)
{
var ext = Path.GetExtension(file.FileName);
var bytes = StreamToBytes(file.InputStream);
ISheet sheet = null;
using (var memstream = new MemoryStream(bytes))
{
switch (ext)
{
case ".xlsx":
sheet = new XSSFWorkbook(memstream).GetSheetAt(0);
break;
case ".xls":
sheet = new HSSFWorkbook(memstream).GetSheetAt(0);
break;
}
}
var dt = new DataTable();
if (sheet == null)
{
return(dt);
}
var headerRow = sheet.GetRow(0); //获取sheet首行
int cellCount = headerRow.LastCellNum; //获取总列数
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
static void Main(string[] args)
{
using (HttpClient client = new HttpClient())
{
HttpResponseMessage namesF = client.GetAsync(url + 1).Result; // List of female names
HttpResponseMessage namesM = client.GetAsync(url + 2).Result; // List of male names
// HttpResponseMessage namesAll = client.GetAsync(url + 2).Result; // List of all names
HttpResponseMessage namesU = client.GetAsync(url + 4).Result; // List of unisex names
List <Stream> streams = new List <Stream>()
{
namesF.Content.ReadAsStreamAsync().Result,
namesM.Content.ReadAsStreamAsync().Result,
namesU.Content.ReadAsStreamAsync().Result
};
foreach (var stream in streams)
{
var sheet = new HSSFWorkbook(stream).GetSheetAt(0); // Old excel format (xls)
// var sheet = new XSSFWorkbook(stream).GetSheetAt(0); // New excel format (xlsx)
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
row.ToList().ForEach(System.Console.WriteLine);
}
}
}
}
