private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet)
{
IDrawing part = sheet.CreateDrawingPatriarch();
Dictionary <string, int> indexOfName = new Dictionary <string, int>();
int i = 0;
foreach (var info in metaInfos.Table.TableList)
{
indexOfName.Add(info.DisplayName, i);
i++;
}
foreach (var err in importError)
{
IRow row = sheet.GetRow(err.IndexOfRow);
IComment comment = null;
ICell cell = row.GetCell(indexOfName[err.ColumnName]);
ICreationHelper factory = workBook.GetCreationHelper();
IClientAnchor anchor = null;
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;
anchor.Col2 = cell.ColumnIndex + 4;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = part.CreateCellComment(anchor);
comment.Author = "mitu";
comment.String = new HSSFRichTextString(err.ErrorMsg);
cell.CellComment = comment;
}
}
public override OutputData DoAction(IInputData input)
{
string source = input.QueryString["Source"];
string path = Path.Combine(BaseAppSetting.Current.XmlPath, @"Import", source + ".xml");
ImportConfigXml config = new ImportConfigXml();
config.ReadXmlFromFile(path);
string filePath = @"C:\Users\zll\Downloads\角色.xls";
var meta = config.Import.MetaData.CreateObject(input);
Tk5ListMetaData data = meta as Tk5ListMetaData;
var resolver = config.Import.Resolver.CreateObject(this);
MetaDataTableResolver metaResolver = resolver as MetaDataTableResolver;
TkDebug.AssertNotNull(metaResolver, "metaResolver", this);
ImportError errResult = new ImportError();
var dataSet = ExcelImporter.ExcelImport(filePath, data, errResult);
FieldErrorInfoCollection importResult = metaResolver.Import(dataSet, input);
if (importResult.Count > 0)
{
var positions = (from item in importResult
orderby item.Position descending
select item.Position).Distinct();
foreach (var errorInfo in importResult)
{
DataRow errorRow = dataSet.Tables[errorInfo.TableName].Rows[errorInfo.Position];
ImportWarningItem errorItem = new ImportWarningItem(errorRow["OriginalRowNum"].Value <int>(),
resolver.GetFieldInfo(errorInfo.NickName).DisplayName, (string)errorRow[errorInfo.NickName], errorInfo.Message);
errResult.Add(errorItem);
}
foreach (var index in positions)
{
resolver.HostTable.Rows.RemoveAt(index);
}
}
DataSet webReport = null;
if (errResult.Count > 0)
{
byte[] dataFile = ExcelUtil.ExcelReport(filePath, data, errResult);
webReport = ExcelUtil.DataSetReport(filePath, data, errResult);
}
return(OutputData.CreateObject(new ImportResultData(DataSet, webReport, errResult)));
}
private static void RowFilter(ImportError importError, ISheet sheet)
{
int counter = 0;
var res = (from err in importError
select err.IndexOfRow).Distinct();
foreach (var rowNum in res)
{
sheet.ShiftRows(rowNum, rowNum, 1 - rowNum + counter);
counter++;
}
for (int rowNum = counter + 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
sheet.RemoveRow(row);
}
}
private static void SheetImport(Tk5ListMetaData metaInfos, DataTable dataTable, ISheet sheet,
ImportError importError)
{
if (sheet != null)
{
Dictionary <string, Tk5FieldInfoEx> dicOfInfo = new Dictionary <string, Tk5FieldInfoEx>();
foreach (Tk5FieldInfoEx info in metaInfos.Table.TableList)
{
dicOfInfo.Add(info.DisplayName, info);
}
IRow headerRow = sheet.GetRow(0);
IRow row = null;
string columnName = string.Empty;
string strValue = string.Empty;
ICell cell = null;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
bool rowError = false;
for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
{
columnName = headerRow.GetCell(j).ToString();
cell = row.GetCell(j);
strValue = ((cell == null) ? null : cell.ToString());
var imResult = TablePadding(dataRow, columnName, dicOfInfo, strValue, i);
if (imResult != null)
{
importError.Add(imResult);
rowError = true;
}
}
if (!rowError)
{
dataRow["OriginalRowNum"] = i;
dataTable.Rows.Add(dataRow);
}
}
}
}
public static byte[] ExcelReport(string strFileName, Tk5ListMetaData metaInfos, ImportError importError)
{
MemoryStream ms = new MemoryStream();
ExcelExporter exporter =
new ExcelExporter(false, ExcelContentFormat.DefaultHeader, ExcelContentFormat.DefaultContent, metaInfos);
using (ms)
{
IWorkbook workBook;
ISheet sheet;
ReadExcle(strFileName, metaInfos.Table.TableDesc, out workBook, out sheet);
PostilAdd(metaInfos, importError, workBook, sheet);
RowFilter(importError, sheet);
workBook.Write(ms);
byte[] filedata = ms.ToArray();
ms.Flush();
using (FileStream fs = new FileStream(@"C:\Users\zll\Downloads\ImportReport.xls", FileMode.Create))
{
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(filedata);
bw.Close();
fs.Close();
}
return(filedata);
}
}
public static DataSet DataSetReport(string strFileName, Tk5ListMetaData metaInfos, ImportError importError)
{
DataSet dataSet = new DataSet();
DataTable dataTable = CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
dataTable.Columns.Add(ORIGINAL_ROWNUM, typeof(int));
IWorkbook workbook;
ISheet sheet;
string columnName = string.Empty;
ReadExcle(strFileName, metaInfos.Table.TableDesc, out workbook, out sheet);
var positions = (from index in importError
orderby index.IndexOfRow ascending
select index.IndexOfRow).Distinct();
IRow headerRow = sheet.GetRow(0);
foreach (var index in positions)
{
IRow row = sheet.GetRow(index);
DataRow dataRow = dataTable.NewRow();
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
{
dataRow[headerRow.GetCell(i).ToString()] = row.GetCell(i);
dataRow[ORIGINAL_ROWNUM] = index;
}
dataTable.Rows.Add(dataRow);
}
dataSet.Tables.Add(dataTable);
return(dataSet);
}
public static byte[] ExcelReport(string strFileName, Tk5ListMetaData metaInfos, ImportError importError)
{
MemoryStream ms = new MemoryStream();
ExcelExporter exporter =
new ExcelExporter(false, ExcelContentFormat.DefaultHeader, ExcelContentFormat.DefaultContent, metaInfos);
using (ms)
{
string sheetName = metaInfos.Table.TableDesc;
//HSSFWorkbook newWorkbook = exporter.CreateWorkbookTemplate();
//HSSFSheet newSheet = (HSSFSheet)newWorkbook.CreateSheet(sheetName);
IWorkbook workBook = null;
ISheet sheet = null;
Dictionary <string, int> indexOfName = new Dictionary <string, int>();
int i = 0;
foreach (var info in metaInfos.Table.TableList)
{
indexOfName.Add(info.DisplayName, i);
i++;
}
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
workBook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
workBook = new XSSFWorkbook(file);
}
}
sheet = workBook.GetSheet(sheetName);
IDrawing part = sheet.CreateDrawingPatriarch();
var res = (from err in importError
select err.IndexOfRow).Distinct();
int[] arr = new int[sheet.LastRowNum];
for (int index = sheet.FirstRowNum + 1, j = 0; index <= sheet.LastRowNum; index++)
{
arr[j] = index;
j++;
}
int[] arrExc = arr.Except(res).ToArray();
foreach (var err in importError)
{
IRow row = sheet.GetRow(err.IndexOfRow);
IComment comment = null;
ICell cell = row.GetCell(indexOfName[err.ColumnName]);
ICreationHelper factory = workBook.GetCreationHelper();
IClientAnchor anchor = null;
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;
anchor.Col2 = cell.ColumnIndex + 4;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = part.CreateCellComment(anchor);
comment.Author = "mitu";
comment.String = new HSSFRichTextString(err.ErrorMsg);
cell.CellComment = comment;
}
int counter = 0;
foreach (var rowNum in res)
{
sheet.ShiftRows(rowNum, rowNum, 1 - rowNum + counter);
counter++;
}
for (int rowNum = counter + 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
sheet.RemoveRow(row);
}
workBook.Write(ms);
ms.Flush();
byte[] filedata = ms.ToArray();
using (FileStream fs = new FileStream(@"C:\Users\zll\Downloads\ImportReport.xls", FileMode.Create))
{
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(filedata);
bw.Close();
fs.Close();
}
return(filedata);
}
}
public static DataSet ExcelImport(string strFileName, Tk5ListMetaData metaInfos, ImportError importError)
{
DataSet dataSet = new DataSet();
DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
dataTable.Columns.Add("OriginalRowNum", typeof(int));
IWorkbook workbook;
ISheet sheet;
ExcelUtil.ReadExcle(strFileName, metaInfos.Table.TableDesc, out workbook, out sheet);
SheetImport(metaInfos, dataTable, sheet, importError);
dataSet.Tables.Add(dataTable);
return(dataSet);
}
