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 void ExcelImport(TkDbContext context, string strFileName,
Tk5ListMetaData metaData, ImportResultData result)
{
string sheetName = metaData.Table.TableDesc;
ISheet sheet = null;
string fileExt = Path.GetExtension(strFileName).ToLower(ObjectUtil.SysCulture);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
sheet = hssfworkbook.GetSheet(sheetName);
}
else if (fileExt == ".xlsx")
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
sheet = xssfworkbook.GetSheet(sheetName);
}
else
{
throw new WebPostException("上传的文件不是Excel文件，请确认上传文件的格式");
}
}
if (sheet != null)
{
SheetImport(context, metaData, sheet, result);
}
}
public static DataSet ExcelImport(string strFileName, Tk5ListMetaData metaInfos, ResultHolder resultHolder)
{
DataSet dataSet = new DataSet();
DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
string sheetName = metaInfos.Table.TableDesc;
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
ISheet sheet = null;
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
hssfworkbook = new HSSFWorkbook(file);
else if (fileExt == ".xlsx")
xssfworkbook = new XSSFWorkbook(file);
}
if (hssfworkbook != null)
{
sheet = hssfworkbook.GetSheet(sheetName);
}
else if (xssfworkbook != null)
{
sheet = xssfworkbook.GetSheet(sheetName);
}
SheetImport(metaInfos, dataTable, sheet, resultHolder);
dataSet.Tables.Add(dataTable);
return dataSet;
}
private static void SheetImport(Tk5ListMetaData metaInfos, DataTable dataTable, ISheet sheet,
ResultHolder resultHolder)
{
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();
for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
{
columnName = headerRow.GetCell(j).ToString();
cell = row.GetCell(j);
strValue = ((cell == null) ? string.Empty : cell.ToString());
ImportResult imResult = TablePadding(dataRow, columnName, dicOfInfo, strValue, i);
if (imResult != null)
{
resultHolder.Add(imResult);
}
}
dataTable.Rows.Add(dataRow);
}
}
}
private static void SheetImport(Tk5ListMetaData metaInfos, DataTable dataTable, ISheet sheet,
ResultHolder resultHolder)
{
if (sheet != null)
{
// resultHolder.SheetName = metaInfos.Table.TableDesc;
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);
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string columnName = headerRow.GetCell(j).ToString();
string strValue = row.GetCell(j).ToString();
ImportResult imResult = TablePadding(dataRow, columnName, dicOfInfo, strValue, i);
if (imResult != null)
{
resultHolder.Add(imResult);
}
}
dataTable.Rows.Add(dataRow);
}
}
}
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);
}
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;
}
}
private IMetaData CreateMetaDataWithIndex(IPageData pageData)
{
IMetaData metaData = CreateImportMetaData(pageData);
Tk5ListMetaData listMeta = metaData.Convert <Tk5ListMetaData>();
Tk5ListMetaData result = new Tk5ListMetaData(listMeta, Index);
return(result);
}
public ExcelExporter(bool useBorder, ExcelContentFormat header,
ExcelContentFormat content, Tk5ListMetaData metaData)
{
TkDebug.AssertArgumentNull(header, "header", null);
TkDebug.AssertArgumentNull(content, "content", null);
TkDebug.AssertArgumentNull(metaData, "metaData", null);
fUseBorder = useBorder;
fHeader = header;
fContent = content;
fMetaData = metaData;
}
public ExcelExporter(bool useBorder, ExcelContentFormat header,
ExcelContentFormat content, Tk5ListMetaData metaData)
{
TkDebug.AssertArgumentNull(header, "header", null);
TkDebug.AssertArgumentNull(content, "content", null);
TkDebug.AssertArgumentNull(metaData, "metaData", null);
fUseBorder = useBorder;
fHeader = header;
fContent = content;
fMetaData = metaData;
}
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)));
}
// 模板
public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
ExportExcelPageMaker headerConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = true
};
ExportExcelPageMaker contentConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = false
};
foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
{
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
contentConfigData, fieldInfo, NPOIWrite.Model.Content);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
headerConfigData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
//string strFileName = @"D:\EmportTemplateTest.xls";
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
//{
// workbook.Write(fs);
//}
workbook.Write(ms);
ms.Flush();
return(ms.ToArray());
}
}
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;
}
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);
}
public ImportResultData(Tk5ListMetaData metaData)
{
Key = Guid.NewGuid().ToString();
ImportDataSet = new DataSet(ToolkitConst.TOOLKIT)
{
Locale = ObjectUtil.SysCulture
};
ImportTable = DataSetUtil.CreateDataTable(metaData.Table.TableName,
metaData.Table.TableList);
ImportTable.Columns.Add(ROW_INDEX, typeof(string));
ImportDataSet.Tables.Add(ImportTable);
var tableList = (from item in metaData.Table.TableList select item.NickName).ToArray();
ErrorTable = DataSetUtil.CreateDataTable(metaData.Table.TableName, tableList);
ErrorTable.Columns.Add(ROW_INDEX, typeof(string));
fErrors = new Dictionary <Tuple <int, string>, string>();
}
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 override OutputData DoAction(IInputData input)
{
Tk5ListMetaData metaData = fMetaData as Tk5ListMetaData;
if (metaData != null)
{
string tableName = metaData.Table.TableName;
var scheme = fMetaData.GetTableScheme(tableName);
MetaDataTableResolver resolver = new MetaDataTableResolver(scheme, this);
using (resolver)
{
DataTable table = resolver.CreateVirtualTable();
table.TableName = "Condition";
DataRow row = table.NewRow();
resolver.SetDefaultValue(row);
table.Rows.Add(row);
resolver.FillCodeTable((PageStyleClass)PageStyle.List);
}
}
return(OutputData.Create(DataSet));
}
public static DataSet ExcelImport(string strFileName, Tk5ListMetaData metaInfos, ResultHolder resultHolder)
{
DataSet dataSet = new DataSet();
DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
string sheetName = metaInfos.Table.TableDesc;
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
ISheet sheet = null;
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);
}
}
if (hssfworkbook != null)
{
sheet = hssfworkbook.GetSheet(sheetName);
}
else if (xssfworkbook != null)
{
sheet = xssfworkbook.GetSheet(sheetName);
}
SheetImport(metaInfos, dataTable, sheet, resultHolder);
dataSet.Tables.Add(dataTable);
return(dataSet);
}
private static void TablePadding(DataRow dataRow, string columnName, Tk5ListMetaData metaInfos, string strValue)
{
Tk5FieldInfoEx fieldInfo = metaInfos.Table.TableList.Find(ex => ex.NickName == columnName);
if (fieldInfo != null)
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
if (strValue == "√")
dataRow[columnName] = ((fieldInfo.Extension == null) ? "1" : fieldInfo.Extension.CheckValue);
else
dataRow[columnName] = ((fieldInfo.Extension == null) ? "0" : fieldInfo.Extension.UnCheckValue);
}
else
{
if (!string.IsNullOrEmpty(strValue))
{
try
{
dataRow[columnName] = strValue;
}
catch (Exception e)
{
Console.WriteLine("the Exception: {0}", e.Message);
Console.WriteLine("Excel导入时：单元格的值与数据类型不匹配");
}
}
}
}
}
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 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 void SetMetaData(IPageStyle style, IMetaData metaData)
{
fMetaData = metaData as Tk5ListMetaData;
}
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;
}
}
private static void SheetImport(TkDbContext context, Tk5ListMetaData metaInfos,
ISheet sheet, ImportResultData result)
{
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);
int firstCell = headerRow.FirstCellNum;
int colLength = headerRow.LastCellNum - firstCell;
List <BaseColumnReader> readers = new List <BaseColumnReader>(colLength);
string[] colNames = new string[colLength];
for (int i = 0; i < colLength; i++)
{
int cellIndex = i + firstCell;
string name = headerRow.GetCell(cellIndex).ToString();
Tk5FieldInfoEx fieldInfo = dicOfInfo[name];
if (fieldInfo != null)
{
BaseColumnReader reader = null;
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
reader = new BoolColumnReader(fieldInfo, cellIndex);
}
else if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
reader = new CodeTableColumnReader(fieldInfo, context, cellIndex);
}
else if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.EasySearch)
{
reader = new EasySearchColumnReader(fieldInfo, cellIndex);
}
else
{
reader = new NormalColumnReader(fieldInfo, cellIndex);
}
readers.Add(reader);
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = result.ImportTable.NewRow();
try
{
dataRow.BeginEdit();
try
{
foreach (var reader in readers)
{
reader.ReadColumn(dataRow, row, context);
}
dataRow[ImportResultData.ROW_INDEX] = i;
}
finally
{
dataRow.EndEdit();
}
result.ImportTable.Rows.Add(dataRow);
}
catch (ImportConvertException ex)
{
result.AddErrorItem(ex.Row, ex.NickName, ex.Message);
DataRow errorRow = result.ErrorTable.NewRow();
errorRow.BeginEdit();
try
{
foreach (var reader in readers)
{
reader.ReadErrorColumn(errorRow, row);
}
errorRow[ImportResultData.ROW_INDEX] = i;
}
finally
{
errorRow.EndEdit();
}
}
}
}
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;
}
//#region 相关方法1
//void CreateSheet()
//{
// IWorkbook workbook = new HSSFWorkbook();
// ISheet sheet = workbook.CreateSheet("Sheet1");
// IRow row = sheet.CreateRow(0);
// ICell cell = row.CreateCell(0);
// cell.SetCellValue("test");
//}
//void GetSheet(Stream stream)
//{
// IWorkbook workbook = new HSSFWorkbook(stream);
// ISheet sheet = workbook.GetSheetAt(0);
// IRow row = sheet.GetRow(0);
// ICell cell = row.GetCell(0);
// string value = cell.ToString();
//}
//public static void GetAndEditSheet()
//{
// string tempPath = "d:\\excel.xls";
// HSSFWorkbook wk = null;
// using (FileStream fs = File.Open(tempPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
// {
// wk = new HSSFWorkbook(fs);
// fs.Close();
// }
// var sheet = wk.GetSheetAt(0);
// ICell cell = sheet.CreateRow(1).CreateCell(0);
// cell.SetCellValue("nihao");
// sheet.GetRow(0).GetCell(0).SetCellValue("编辑的值");
// using (FileStream fileStream = File.Open(tempPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
// {
// wk.Write(fileStream);
// fileStream.Close();
// }
//}
//public static MemoryStream RenderDataTableToExcel(DataTable SourceTable)
//{
// MemoryStream ms = new MemoryStream();
// HSSFWorkbook workbook = new HSSFWorkbook();
// ISheet sheet = workbook.CreateSheet();
// IRow headerRow = sheet.CreateRow(0);
// foreach (DataColumn column in SourceTable.Columns)
// {
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
// }
// int rowIndex = 1;
// foreach (DataRow row in SourceTable.Rows)
// {
// IRow dataRow = sheet.CreateRow(rowIndex);
// foreach (DataColumn column in SourceTable.Columns)
// {
// dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
// }
// rowIndex++;
// }
// //using (FileStream fs = File.OpenWrite("d:\\excel1.xls"))
// //{
// // workbook.Write(fs);
// //}
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// return ms;
//}
//#endregion
#region 根据模板导入Excel
public static DataTable ImportExcel(string strFileName, Tk5ListMetaData metaInfos, int sheetIndex = 0)
{
DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
hssfworkbook = new HSSFWorkbook(file);
else if (fileExt == ".xlsx")
xssfworkbook = new XSSFWorkbook(file);
}
if (hssfworkbook != null)
{
HSSFSheet hSheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (hSheet != null)
{
HSSFRow headerRow = (HSSFRow)hSheet.GetRow(0);
for (int i = (hSheet.FirstRowNum + 1); i <= hSheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)hSheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string columnName = headerRow.GetCell(j).ToString();
string strValue = row.GetCell(j).ToString();
TablePadding(dataRow, columnName, metaInfos, strValue);
}
dataTable.Rows.Add(dataRow);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string columnName = headerRow.GetCell(j).ToString();
string strValue = row.GetCell(j).ToString();
TablePadding(dataRow, columnName, metaInfos, strValue);
}
dataTable.Rows.Add(dataRow);
}
}
}
return dataTable;
}
public void SetMetaData(IPageStyle style, IMetaData metaData)
{
fMetaData = metaData as Tk5ListMetaData;
}
// 模板
public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
ExportExcelPageMaker headerConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = true };
ExportExcelPageMaker contentConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = false };
foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
{
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
contentConfigData, fieldInfo, NPOIWrite.Model.Content);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
headerConfigData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
//string strFileName = @"D:\EmportTemplateTest.xls";
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
//{
// workbook.Write(fs);
//}
workbook.Write(ms);
ms.Flush();
return ms.ToArray();
}
}
