public string TestOpenAndSaveExcel() // 已测试成功读取到公式
{
// Step 1 Aspose.Cell 直接读取 -- 公式Cell的值为null
// Step 2 InteropExcel 打开(自动计算公式值), 保存计算好公式值的文件
// Step 3 Aspose.Cell 再次读取 -- 公式Cell的值不为null
string path = System.IO.Path.Combine(Server.MapPath("~/bin"), "TestAspose2.xlsx");
string saveFilePath = path;
System.Data.DataTable dt = Util.Excel.ExcelUtils_Aspose.Excel2DataTable
(
path: saveFilePath,
exportColumnName: false
);
StringBuilder sb = new StringBuilder();
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(dt.Rows[rowIndex][i]).Append(" ");
}
sb.Append(";");
sb.AppendLine();
}
using (var excelUtilObj = new Util.Excel.ExcelUtil_InteropExcel(filePath: path, isWebApp: true))
{
// excelUtilObj.SaveCopyAs();
excelUtilObj.Save();
}
dt = Util.Excel.ExcelUtils_Aspose.Excel2DataTable
(
path: saveFilePath,
exportColumnName: false
);
sb.AppendLine("********* 用Excel程序打开并保存后 *********");
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(dt.Rows[rowIndex][i]).Append(" ");
}
sb.Append(";");
sb.AppendLine();
}
return(sb.ToString());
}
public SOAPResult Upload_Open_Save_GetBack(string base64Str)
{
// 1. 接收上传文件
// 2. 用 ExcelUtil_InteropExcel 打开并保存文件 ( 计算公式的值 )
// 3. 返回文件
SOAPResult r = new SOAPResult();
try
{
// 1. 接收上传文件
string uploadFilePath = string.Empty;
string localDirectory = System.Web.Hosting.HostingEnvironment.MapPath("~/Upload/Excel");
if (System.IO.Directory.Exists(localDirectory) == false)
{
System.IO.Directory.CreateDirectory(localDirectory);
}
uploadFilePath = System.IO.Path.Combine(localDirectory, Guid.NewGuid().ToString());
Util.IO.FileUtils.SaveBase64StrToFile(uploadFilePath, base64Str, true);
// 2. 用 ExcelUtil_InteropExcel 打开并保存文件 ( 计算公式的值 )
string savedFormulaFilePath = string.Empty;
using (var excelUtilObj = new Util.Excel.ExcelUtil_InteropExcel(filePath: uploadFilePath, isWebApp: true))
{
// Test 1
excelUtilObj.Save();
savedFormulaFilePath = uploadFilePath;
// Test 2
savedFormulaFilePath = excelUtilObj.SaveCopyAs();
}
// 3. 返回文件
r.SuccessNonJsonConvert(Util.IO.FileUtils.GetFileToBase64Str(savedFormulaFilePath));
}
catch (Exception e)
{
r.Error(e);
}
return(r);
}