public static XYDataList ExcelToTable(string file)
{
if (!File.Exists(file))
{
return(null);
}
XYDataList lstXYData = new XYDataList();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
return(null);
}
ISheet sheetXYData = workbook.GetSheet("XYData");
for (int i = 1; i <= sheetXYData.LastRowNum - sheetXYData.FirstRowNum; i++)
{
xyData XYD = new xyData();
XYD.X = Convert.ToDouble(GetValueType(sheetXYData.GetRow(i).GetCell(0)));
XYD.Y = Convert.ToDouble(GetValueType(sheetXYData.GetRow(i).GetCell(1)));
lstXYData.lstXYData.Add(XYD);
}
}
return(lstXYData);
}
public static void TableToExcel(XYDataList lstXYData, string saveFileName)
{
if (lstXYData == null)
{
return;
}
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);
}
IWorkbook workbook;
string type = Path.GetExtension(saveFileName).ToLower();
if (type == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (type == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return;
}
//sheet名
ISheet sheetXYData = workbook.CreateSheet("XYData");
//表头
IRow rowHeadXYData = sheetXYData.CreateRow(0);
ICell cellHeadX = rowHeadXYData.CreateCell(0);
cellHeadX.SetCellValue("频率");
ICell cellHeadY = rowHeadXYData.CreateCell(1);
cellHeadY.SetCellValue("Resp");
//数据
for (int i = 1; i <= lstXYData.lstXYData.Count; i++)
{
xyData XYD = lstXYData.lstXYData[i - 1];
IRow rowXYData = sheetXYData.CreateRow(i);
ICell cellX = rowXYData.CreateCell(0);
cellX.SetCellValue(XYD.X.ToString());
ICell cellY = rowXYData.CreateCell(1);
cellY.SetCellValue(XYD.Y.ToString());
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
