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);
}
// 表格头部设置
private void HeaderSetting(IWorkbook workbook, ISheet sheet)
{
IRow dataRow = sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = BorderAndFontSetting(workbook, fieldInfo, Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
int colWith = ExcelUtil.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
index++;
}
}
// 设置DataTable的值
private static void ReadColumn(DataRow dataRow, string columnName,
Tk5FieldInfoEx fieldInfo, string strValue, int indexOfRow, ImportResultData result)
{
ImportWarningItem imResult = null;
string asgValue = null;
if (fieldInfo != null)
{
bool valueError = false;
if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
IEnumerable <IDecoderItem> data = ExcelUtil.GetDecoderItem(fieldInfo, null);
if (string.IsNullOrEmpty(strValue))
{
valueError = true;
}
else
{
foreach (IDecoderItem item in data)
{
if (item.Name == strValue)
{
asgValue = item.Value;
valueError = true;
break;
}
}
}
}
else
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
if (strValue == "√")
{
asgValue = ((fieldInfo.Extension == null) ? "1" : fieldInfo.Extension.CheckValue);
valueError = true;
}
if (strValue == "X" || string.IsNullOrEmpty(strValue))
{
asgValue = ((fieldInfo.Extension == null) ? "0" : fieldInfo.Extension.UnCheckValue);
valueError = true;
}
}
else
{
asgValue = strValue;
valueError = true;
}
}
try
{
if (!valueError)
{
throw new Exception("value in the cell is invalid");
}
else
{
dataRow[fieldInfo.NickName] = asgValue;
}
}
catch (Exception ex)
{
imResult = new ImportWarningItem(indexOfRow, columnName, asgValue, ex.Message);
}
}
//return imResult;
}
// 由DataTable生成Excel
private void DataTableExport(Dictionary <string, ICellStyle> ContentStyles,
ISheet sheet, DataTable dt)
{
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
string strValue = string.Empty;
Tk5ExtensionConfig ex = fieldInfo.Extension;
SimpleFieldControl sfctrl = fieldInfo.InternalControl;
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = (row[fieldInfo.NickName]).ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null && sfctrl.SrcControl == ControlType.CheckBox)
{
if ((ex != null && strValue == ex.CheckValue) ||
(ex == null && strValue == "1"))
{
cell.SetCellValue("√");
}
}
else
{
ExcelUtil.CellPadding(strValue, cell, fieldInfo);
}
}
}
else
{
strValue = row[fieldInfo.NickName + "_Name"].ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null &&
(sfctrl.SrcControl == ControlType.CheckBoxList ||
sfctrl.SrcControl == ControlType.MultipleEasySearch))
{
MultipleDecoderData data = MultipleDecoderData.ReadFromString(strValue);
cell.SetCellValue(string.Join(", ", data));
}
else
{
cell.SetCellValue(strValue);
}
}
}
cell.CellStyle = ContentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}