之前用过Spire.Xls处理Excel,但是unity好像不支持,反正我是没弄对,导致又使用Epplus操作,下面记录一下我的使用。我要做的是将配餐食谱(已确定的数据结构)导入到提前设定好的Excel模板中,然后保存至选定位置。
1.导入Epplus插件;
2.代码中using System.IO;using OfficeOpenXml;using OfficeOpenXml.Style;
2.在点击导出按钮时执行ExportMenuBtnEvent;
/// <summary>
/// 导出餐谱
/// </summary>
public void ExportMenuBtnEvent()
{
SaveFileDlg pth = new SaveFileDlg();
pth.structSize = Marshal.SizeOf(pth);
pth.filter = "ExcelFile (*.xlsx)";
pth.file = new string(new char[256]);
pth.maxFile = pth.file.Length;
pth.fileTitle = new string(new char[64]);
pth.maxFileTitle = pth.fileTitle.Length;
pth.initialDir = Application.dataPath; //默认路径
pth.title = "保存餐谱";
pth.defExt = "xlsx";
pth.flags = 0x00080000 | 0x00001000 | 0x00000800 | 0x00000200 | 0x00000008;
if (SaveFileDialog.GetSaveFileName(pth))
{
File.Copy(Application.streamingAssetsPath + "/餐谱模板.xlsx", pth.file);
ReadExcel(pth.file);
}
}
3.打开复制出的Excel模板准备进行添加数据等操作;
/// <summary>
/// 读取Excel文件并填入数据
/// </summary>
/// <param name="url">路径</param>
public void ReadExcel(string url)
{
//创建Excel表格
FileInfo newFile = new FileInfo(url);
//判断当前路径的表格是否存在
if (!newFile.Exists){return;}
//通过ExcelPackage打开文件
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[1];//根据下标获取Sheet,从1开始
//对sheet进行操作
package.Save();
}
}
4.对sheet进行操作中使用到的方法:
sheet.SetValue(int row,int startRow,object value);//对某行某列的单元格赋值
sheet.InsertRow(int rowFrom, int rows, int copyStylesFromRow);//插入数行并复制某行的Style
sheet.Cells[int row,int column].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
sheet.Cells[int row,int column].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
sheet.Cells[int row,int column].Style.WrapText = true;//自动换行
sheet.Row(int row).Height = 25;//设置行高为25
sheet.Cells[startRow, startColumn, endRow, endColumn].Merge = true;//合并单元格