public static void WriteExcelData(string path, ExcelData[] dataList)
{
//创建工作薄
IWorkbook book;
string extension = Path.GetExtension(path);
//根据指定的文件格式创建对应的类
if (extension.Equals(".xls"))
{
book = new HSSFWorkbook();
}
else
{
book = new XSSFWorkbook();
}
for (int i = 0; i < dataList.Length; i++)
{
ExcelData data = dataList[i];
ISheet sheet = book.GetSheet(data.SheetName);
if (sheet == null)
{
sheet = book.CreateSheet(data.SheetName);
}
for (int j = 0; j < data.HeadRowLen; j++)
{
IRow row = sheet.CreateRow(j);
for (int k = 0; k < data.DataColumnLen; k++)
{
row.CreateCell(k).SetCellValue(data.Head[j][k].ToString());
}
}
for (int j = data.HeadRowLen; j < data.HeadRowLen + data.BodyRowLen; j++)
{
IRow row = sheet.CreateRow(j);
for (int k = 0; k < data.DataColumnLen; k++)
{
row.CreateCell(k).SetCellValue(data.Body[j - data.HeadRowLen][k].ToString());
}
}
}
try
{
FileStream fs = File.OpenWrite(path);
book.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Close();
}
catch (Exception e)
{
Debug.LogException(e);
}
}
public static ExcelData[] GetSingleExcelData(string InExcelPath)
{
if (File.Exists(InExcelPath))
{
FileInfo info = new FileInfo(InExcelPath);
if (info.Exists && info.Name[0] != '~' &&
(info.Extension.Equals(".xlsx") || info.Extension.Equals(".xls")))
{
using (FileStream stream = info.Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook book;
if (info.Extension.Equals(".xlsx"))
{
book = new XSSFWorkbook(stream);
}
else
{
book = new HSSFWorkbook(stream);
}
int sheetCount = book.NumberOfSheets;
var dataList = new List <ExcelData>(sheetCount);
for (int i = 0; i < sheetCount; i++)
{
ISheet sheet = book.GetSheetAt(i);
int rowCount = sheet.LastRowNum + 1;
if (rowCount < 2)
{
continue; //no content
}
ExcelData data = new ExcelData
{
SheetName = sheet.SheetName.Equals("Sheet1") || sheet.SheetName.Equals("工作表1")
? info.Name.Replace(info.Extension, string.Empty)
: sheet.SheetName
};
IRow[] headRow =
{
sheet.GetRow(ExcelConfig.NAME_ROW_INDEX_I), //Name
sheet.GetRow(ExcelConfig.TYPE_ROW_INDEX_I), //Type
sheet.GetRow(ExcelConfig.DESCRIBES_ROW_INDEX_I) //Describes
};
int colCount = headRow[ExcelConfig.NAME_ROW_INDEX_I].LastCellNum >
headRow[ExcelConfig.TYPE_ROW_INDEX_I].LastCellNum
? headRow[ExcelConfig.TYPE_ROW_INDEX_I].LastCellNum
: headRow[ExcelConfig.NAME_ROW_INDEX_I].LastCellNum;
if (colCount > 0)
{
data.DataColumnLen = colCount;
data.HeadRowLen = 3;
data.Head = new ICell[data.HeadRowLen][];
data.Head[0] = new ICell[colCount];
data.Head[1] = new ICell[colCount];
data.Head[2] = new ICell[colCount];
//Property comment can be empty.
bool isDescribeFilled = headRow[ExcelConfig.DESCRIBES_ROW_INDEX_I] != null;
for (int j = 0; j < colCount; j++)
{
data.Head[0][j] = headRow[0].GetCell(j);
data.Head[1][j] = headRow[1].GetCell(j);
data.Head[2][j] = isDescribeFilled ? headRow[2].GetCell(j) : null;
}
if (rowCount > ExcelConfig.CONTENT_START_ROW_INDEX_I)
{
int length = rowCount - ExcelConfig.CONTENT_START_ROW_INDEX_I;
List <ICell[]> content = new List <ICell[]>(length);
for (int j = 0, m = ExcelConfig.CONTENT_START_ROW_INDEX_I; j < length; ++j, ++m)
{
var row = sheet.GetRow(m);
if (null == row)
{
continue;
}
ICell[] cells = new ICell[colCount];
for (int k = 0; k < colCount; ++k)
{
cells[k] = row.GetCell(k);
}
content.Add(cells);
}
data.Body = content.ToArray();
data.BodyRowLen = content.Count;
}
if (data.CheckData())
{
dataList.Add(data);
}
}
else
{
Debug.LogError(info.Name + "-" + sheet.SheetName +
" property name and type number does not match.");
}
//else if(0 != rowCount) EditorUtility.DisplayDialog("Error", info.Name + "-" + sheet.SheetName + " missing basic configuration information, property name and type.", "Ok");
}
book.Close();
stream.Close();
return(dataList.ToArray());
}
}
}
else
{
EditorUtility.DisplayDialog("Error",
"Failed to find excel file in this directory.\nPath = " + InExcelPath, "Ok");
}
return(null);
}
public static ExcelData[] GetSingleExcelData(string InExcelPath)
{
if (File.Exists(InExcelPath))
{
List <ExcelData> datas;
FileInfo info = new FileInfo(InExcelPath);
if (info.Exists && info.Name[0] != '~' &&
(info.Extension.Equals(".xlsx") || info.Extension.Equals(".xls")))
{
using (FileStream stream = info.Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook book;
if (info.Extension.Equals(".xlsx"))
{
book = new XSSFWorkbook(stream);
}
else
{
book = new HSSFWorkbook(stream);
}
int sheetCount = book.NumberOfSheets;
datas = new List <ExcelData>(sheetCount);
for (int i = 0; i < sheetCount; i++)
{
ISheet sheet = book.GetSheetAt(i);
int rowCount = sheet.LastRowNum + 1;
if (rowCount >= 2)
{
ExcelData data = new ExcelData();
data.SheetName = sheet.SheetName.Equals("Sheet1") || sheet.SheetName.Equals("工作表1")
? info.Name.Replace(info.Extension, string.Empty) : sheet.SheetName;
IRow[] headRow = { sheet.GetRow(0), sheet.GetRow(1), sheet.GetRow(2) };
int colCount = headRow[0].LastCellNum;
if (colCount == headRow[1].LastCellNum)
{
data.DataColumnLen = colCount;
//Property comment can be empty.
data.HeadRowLen = headRow[2] == null ? 2 : 3;
data.Head = new ICell[data.HeadRowLen][];
for (int j = 0; j < data.HeadRowLen; j++)
{
data.Head[j] = new ICell[colCount];
for (int k = 0; k < colCount; k++)
{
data.Head[j][k] = headRow[j].GetCell(k);
}
}
if (rowCount > 3)
{
int length = rowCount - 3;
List <ICell[]> content = new List <ICell[]>(length);
IRow row;
for (int j = 0, m = 3; j < length; ++j, ++m)
{
row = sheet.GetRow(m);
if (null != row)
{
ICell[] cells = new ICell[colCount];
for (int k = 0; k < colCount; ++k)
{
cells[k] = row.GetCell(k);
}
content.Add(cells);
}
}
data.Body = content.ToArray();
data.BodyRowLen = content.Count;
}
if (data.CheckData())
{
datas.Add(data);
}
}
else
{
EditorUtility.DisplayDialog("Error", info.Name + "-" + sheet.SheetName + " property name and type number does not match.", "Ok");
}
}
//else if(0 != rowCount) EditorUtility.DisplayDialog("Error", info.Name + "-" + sheet.SheetName + " missing basic configuration information, property name and type.", "Ok");
}
book.Close();
stream.Close();
return(datas.ToArray());
}
}
}
else
{
EditorUtility.DisplayDialog("Error", "Failed to find excel file in this directory.\nPath = " + InExcelPath, "Ok");
}
return(null);
}
