/// <summary>
/// 从Excel文件中导入数据
/// </summary>
public void Import()
{
try
{
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook = WorkbookFactory.Create((Stream)fs);
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
//{
// workbook = new XSSFWorkbook(fs);
// excelVersion = ExcelVersion.XLSX;
//}
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
//{
// workbook = new HSSFWorkbook(fs);
// excelVersion = ExcelVersion.XLS;
//}
SheetMetadata sheetMetadata = this.CollectMetadata(workbook);
this.Import(workbook, sheetMetadata);
}
}
catch (Exception ex)
{
throw new FapException(ex.Message, ex);
}
}
/// <summary>
/// 导入数据的具体方法
/// </summary>
/// <param name="workbook"></param>
public override void Import(IWorkbook workbook, SheetMetadata sheetMetadata)
{
int sheetCount = workbook.NumberOfSheets;
if (sheetCount == 0)
{
return;
}
this.sheetMetadata = sheetMetadata;
FapTable table = _dataAccessor.Table(tableName);
if (table == null)
{
return;
}
//if (mode == ImportMode.FORCE) //强制导入
//{
// _dataAccessor.DeleteExec(tableName);
// if (!string.IsNullOrWhiteSpace(table.ExtTable))
// {
// _dataAccessor.DeleteExec(table.ExtTable);
// }
//}
ImportDataToTable(workbook, workbook.GetSheetAt(0), this.tableName);
}
public void Test_ReadMetadata_27728( )
{
var service = GetService( );
DataFileReaderSettings settings = new DataFileReaderSettings( );
using (Stream stream = SheetTestHelper.GetStream("Qualification.xlsx"))
using (IDataFile reader = service.OpenDataFile(stream, settings))
{
SheetMetadata metadata = reader.ReadMetadata( );
Assert.That(metadata.Fields, Has.Count.EqualTo(2));
Assert.That(metadata.Fields[0].Key, Is.EqualTo("A"));
Assert.That(metadata.Fields [1].Key, Is.EqualTo("B"));
Assert.That(metadata.Fields [0].Title, Is.EqualTo("Name"));
Assert.That(metadata.Fields [1].Title, Is.EqualTo("Qualifcation code"));
}
}
/// <summary>
/// 收集模板的元数据信息
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
protected virtual SheetMetadata CollectMetadata(IWorkbook workbook)
{
ISheet sheet = workbook.GetSheet(SheetMetadata.METADATA_SHEET_NAME);
if (sheet == null)
{
return(null);
}
ICell cell = sheet.GetRow(0).GetCell(0);
if (cell == null)
{
return(null);
}
object obj = ExcelUtils.GetCellValue(cell);
string xmlString = obj.ToString();
SheetMetadata sheetMetadata = ExcelUtils.XmlDeserialize <SheetMetadata>(xmlString);
return(sheetMetadata);
}
/// <summary>
/// Read sample data from a spreadsheet reader.
/// </summary>
/// <param name="stream">The spreadsheet.</param>
/// <param name="settings">Settings.</param>
/// <param name="service">The reader.</param>
/// <returns></returns>
public SampleTable CreateSample(Stream stream, DataFileReaderSettings settings, IDataFileReaderService service)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream));
}
if (settings == null)
{
throw new ArgumentNullException(nameof(settings));
}
if (service == null)
{
throw new ArgumentNullException(nameof(service));
}
// Read field list
using (IDataFile dataFile = service.OpenDataFile(stream, settings))
{
SheetMetadata metadata = dataFile.ReadMetadata( );
SampleTable table = new SampleTable
{
Columns = metadata.Fields.Select(
field =>
new SampleColumn
{
ColumnName = field.Key,
Name = field.Title
}
).ToList( )
};
// Read records
var records = dataFile.GetObjects( ).Take(NumberOfSampleRows);
// Convert to sample rows
List <SampleRow> sampleRows = new List <SampleRow>( );
foreach (IObjectReader record in records)
{
// Read values
var values = metadata.Fields.Select(field =>
{
try
{
return(record.GetString(field.Key));
}
catch
{
return(string.Empty);
}
}).ToList( );
// Create sample row
SampleRow row = new SampleRow
{
Values = values
};
sampleRows.Add(row);
}
table.Rows = sampleRows;
return(table);
}
}
public abstract void Import(IWorkbook workbook, SheetMetadata sheetMetadata);
