public void TestExcel2DataSetStepByStep_Without_ExcelReaderConfig()
{
var path = System.IO.Path.Combine(Environment.CurrentDirectory, "Util.Excel.Aspose", "Excel2DataSetTest.xlsx");
var ds = new Util.Excel.ExcelUtils_Aspose().Excel2DataSetStepByStep(path);
// 含有 3 个 Sheet
Assert.AreEqual <int>(3, ds.Tables.Count);
// 读取信息
int expectedNumber = 1;
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
string tmp = dr[0].ToString();
Assert.AreEqual <int>(expectedNumber++, Convert.ToInt32(tmp));
}
}
}
public void TestExcel2DataSetStepByStep_With_ExcelReaderConfig()
{
var path = System.IO.Path.Combine(Environment.CurrentDirectory, "Util.Excel.Aspose", "Excel2DataSetWithExcelReaderConfig.xlsx");
var excelReaderConfig = new Util.Excel.ExcelReaderConfig();
excelReaderConfig.Config = new System.Collections.Generic.List <Util.Excel.SheetReadConfig>();
// 读取Sheet2
var cellReadRuleDict_2 = new System.Collections.Generic.Dictionary <int, Util.Excel.CellType>();
cellReadRuleDict_2.Add(0, Util.Excel.CellType.Blank);
cellReadRuleDict_2.Add(1, Util.Excel.CellType.Formula);
cellReadRuleDict_2.Add(2, Util.Excel.CellType.DateTime);
var toAdd_2 = new Util.Excel.SheetReadConfig()
{
SheetName = "Sheet2",
CellReadRule = cellReadRuleDict_2
};
excelReaderConfig.Config.Add(toAdd_2);
// 读取sheetIndex=2的工作表
var cellReadRuleDict_3 = new System.Collections.Generic.Dictionary <int, Util.Excel.CellType>();
cellReadRuleDict_3.Add(2, Util.Excel.CellType.Blank);
cellReadRuleDict_3.Add(3, Util.Excel.CellType.Formula);
cellReadRuleDict_3.Add(4, Util.Excel.CellType.DateTime);
var toAdd_3 = new Util.Excel.SheetReadConfig()
{
SheetIndex = 2,
StartCellRowIndex = 2,
StartCellColumnIndex = 2,
CellReadRule = cellReadRuleDict_3
};
excelReaderConfig.Config.Add(toAdd_3);
var toAdd_4 = new Util.Excel.SheetReadConfig()
{
SheetIndex = 3,
IsContainColumnHeader = false
};
excelReaderConfig.Config.Add(toAdd_4);
var ds = new Util.Excel.ExcelUtils_Aspose().Excel2DataSetStepByStep(path, excelReaderConfig);
// 由于加上读取配置, 故ds只有 3 个 Sheet
Assert.AreEqual <int>(3, ds.Tables.Count);
// 测试 Sheet2
DataTable dt_Sheet2 = ds.Tables[0];
Assert.AreEqual <string>("读取空", dt_Sheet2.Columns[0].ColumnName);
Assert.AreEqual <string>("读取公式", dt_Sheet2.Columns[1].ColumnName);
Assert.AreEqual <string>("读取时间", dt_Sheet2.Columns[2].ColumnName);
Assert.AreEqual <string>("读取数值", dt_Sheet2.Columns[3].ColumnName);
DataRow dr = dt_Sheet2.Rows[0];
Assert.AreEqual <string>(string.Empty, dr["读取空"].ToString());
Assert.AreEqual <string>("=A2+2", dr["读取公式"].ToString());
Assert.AreEqual <string>("2018-12-06 17:30:26", Util.CommonDal.ReadDateTime(dr["读取时间"]).ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual <decimal>(32.123M, Util.CommonDal.ReadDecimal(dr["读取数值"].ToString()));
dr = dt_Sheet2.Rows[1];
Assert.AreEqual <string>(string.Empty, dr["读取空"].ToString());
Assert.AreEqual <string>("=A3+2", dr["读取公式"].ToString());
Assert.AreEqual <string>("2018-12-06 17:30:28", Util.CommonDal.ReadDateTime(dr["读取时间"]).ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual <decimal>(123.456M, Util.CommonDal.ReadDecimal(dr["读取数值"].ToString()));
// 测试 Sheet3
DataTable dt_Sheet3 = ds.Tables[1];
Assert.AreEqual <string>("读取空", dt_Sheet3.Columns[0].ColumnName);
Assert.AreEqual <string>("读取公式", dt_Sheet3.Columns[1].ColumnName);
Assert.AreEqual <string>("读取时间", dt_Sheet3.Columns[2].ColumnName);
Assert.AreEqual <string>("读取数值", dt_Sheet3.Columns[3].ColumnName);
dr = dt_Sheet3.Rows[0];
Assert.AreEqual <string>(string.Empty, dr["读取空"].ToString());
Assert.AreEqual <string>("=C4+2", dr["读取公式"].ToString());
Assert.AreEqual <string>("2018-12-06 17:30:26", Util.CommonDal.ReadDateTime(dr["读取时间"]).ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual <decimal>(32.123M, Util.CommonDal.ReadDecimal(dr["读取数值"].ToString()));
dr = dt_Sheet3.Rows[1];
Assert.AreEqual <string>(string.Empty, dr["读取空"].ToString());
Assert.AreEqual <string>("=C5+2", dr["读取公式"].ToString());
Assert.AreEqual <string>("2018-12-06 17:30:28", Util.CommonDal.ReadDateTime(dr["读取时间"]).ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual <decimal>(123.456M, Util.CommonDal.ReadDecimal(dr["读取数值"].ToString()));
// 测试 Sheet4 - 测试200格空行仍然能读取信息
DataTable dt_Sheet4 = ds.Tables[2];
Assert.AreEqual <int>(2, dt_Sheet4.Rows.Count);
Assert.AreEqual <string>("Column1", dt_Sheet4.Columns[0].ColumnName);
Assert.AreEqual <string>("Column2", dt_Sheet4.Columns[1].ColumnName);
dr = dt_Sheet4.Rows[0];
Assert.AreEqual <int>(300, Util.CommonDal.ReadInt(dr["ExcelRowNumber"])); // 测试Excel行号
Assert.AreEqual <string>("300A", Util.CommonDal.ReadString(dr["Column1"]));
Assert.AreEqual <string>("300B", Util.CommonDal.ReadString(dr["Column2"]));
dr = dt_Sheet4.Rows[1];
Assert.AreEqual <int>(304, Util.CommonDal.ReadInt(dr["ExcelRowNumber"])); // 测试Excel行号
Assert.AreEqual <string>("300", Util.CommonDal.ReadString(dr["Column1"]));
Assert.AreEqual <string>("301", Util.CommonDal.ReadString(dr["Column2"]));
}