/// <summary>
/// 创建workbook
/// created by sang at 2013.09.17
/// </summary>
/// <param name="companyXmlFileName">公司信息配置文件</param>
/// <returns></returns>
private static HSSFWorkbook ReBuildWorkbook(string companyXmlFileName = "CompanyConfig.xml")
{
//实例化工作薄
HSSFWorkbook _workbook = new HSSFWorkbook();
//添加工作薄的属性（鼠标右键->属性->详细信息）
//实例化公司信息的xml
XmlTwoHandler XmlCompany = new XmlTwoHandler(companyXmlFileName);
DataTable result = XmlCompany.GetAllData();
//文件的大概信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = result.Select("InnerText='Company'")[0]["cn"].ToString();
//主要信息
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = result.Select("InnerText='Author'")[0]["cn"].ToString(); //作者信息
si.ApplicationName = result.Select("InnerText='ApplicationName'")[0]["cn"].ToString(); //填加xls文件创建程序信息
si.LastAuthor = result.Select("InnerText='LastAuthor'")[0]["cn"].ToString(); //填加xls文件最后保存者信息
si.Comments = result.Select("InnerText='Comments'")[0]["cn"].ToString(); //填加xls文件作者信息
si.Title = result.Select("InnerText='Title'")[0]["cn"].ToString(); //填加xls文件标题信息
si.Subject = result.Select("InnerText='Subject'")[0]["cn"].ToString(); //填加文件主题信息
si.CreateDateTime = DateTime.Now;
_workbook.DocumentSummaryInformation = dsi;
_workbook.SummaryInformation = si;
return(_workbook);
}
/// <summary>
/// 构造函数
/// <param name="connectionString">连接数据库字符串</param>
/// </summary>
public ImportHelper(string connectionString)
{
this._importValidate = new ImportValidate(connectionString);
XmlTwoHandler xmlRegex = new XmlTwoHandler("RegexConfig.xml");//读取正则配置文件
if (xmlRegex != null)
{
_dtRegexConfig = xmlRegex.GetAllData();
}
#region 1、存储错误信息的表结构
this._errorInfo = new DataTable();
this._errorInfo.Columns.Add("数据表名", typeof(System.String));
this._errorInfo.Columns.Add("验证方式", typeof(System.String));
this._errorInfo.Columns.Add("行号", typeof(System.String));
this._errorInfo.Columns.Add("错误原因", typeof(System.String));
this._errorInfo.Columns.Add("主键", typeof(System.String));
#endregion
}
/// <summary>
/// 获取验证实体类
/// </summary>
/// <param name="XmlTwoHandler"></param>
/// <param name="dtXml"></param>
/// <param name="sourceDataTable"></param>
/// <returns></returns>
private TableValidate GetTableValidate(XmlTwoHandler XmlTwoHandler, DataTable dtXml, DataTable sourceDataTable)
{
string pkName = XmlTwoHandler.GetRootNodeAttr("pk");
string fkName = XmlTwoHandler.GetRootNodeAttr("fk");
string dbTableName = XmlTwoHandler.GetRootNodeAttr("tableName");
string dbPkName = string.IsNullOrWhiteSpace(pkName) ? string.Empty : dtXml.Select(string.Format("cn='{0}'", pkName))[0]["InnerText"].ToString();
string dbFkName = string.IsNullOrWhiteSpace(fkName) ? string.Empty : dtXml.Select(string.Format("cn='{0}'", fkName))[0]["InnerText"].ToString();
TableValidate validate = new TableValidate()
{
SourceTable = sourceDataTable,
PK = pkName,
FK = fkName,
DbPK = dbPkName,
DbFK = dbFkName,
DbTableName = dbTableName
};
return(validate);
}
/// <summary>
/// 获取一个XML配置中有Path路径
/// 对应的XML文件的内容
/// </summary>
/// <param name="dtXmlData">获取到的xml配置文件数据表</param>
/// <returns>键值对集合《列名，path对应的xml文件的值<cn名称，值>》</returns>
private Dictionary <string, Dictionary <string, string> > GetDicXmlPathData(DataTable dtXmlData)
{
XmlTwoHandler XmlTwoHandler;
DataTable dtPathXml;
var dic = new Dictionary <string, Dictionary <string, string> >();
for (int i = 0; i < dtXmlData.Rows.Count; i++)
{
if ((dtXmlData.Rows[i]["valid"] != null && Convert.ToBoolean(dtXmlData.Rows[i]["valid"])) &&
(dtXmlData.Rows[i]["path"] != null && !string.IsNullOrWhiteSpace(dtXmlData.Rows[i]["path"].ToString())))
{
XmlTwoHandler = new XmlTwoHandler(dtXmlData.Rows[i]["path"].ToString());
dtPathXml = XmlTwoHandler.GetAllData();
Dictionary <string, string> dicPath = new Dictionary <string, string>();
foreach (DataRow item in dtPathXml.Rows)
{
dicPath.Add(item["cn"].ToString(), item["InnerText"].ToString());
}
dic.Add(dtXmlData.Rows[i]["cn"].ToString(), dicPath);
}
}
return(dic);
}
/// <summary>
/// 从数据源中获取有效的数据集
/// </summary>
/// <param name="dtList">数据源</param>
/// <param name="xmlPath">XML文件路径</param>
/// <param name="mainTableName">数据源中的主表名</param>
/// <param name="isDeleteExists">是否删除数据库中已存在的数据</param>
/// <returns>有效的数据集信息</returns>
public DataSet ValidateData(string xmlPath, string mainTableName, bool isDeleteExists)
{
if (null == this._allData)
{
return(null);
}
#region 2、验证是否有主表
//如果主表名为空就把第一个数据表作为主表
if (string.IsNullOrEmpty(mainTableName))
{
mainTableName = this._allData.Tables[0].TableName;
}
#endregion
#region 3、数据验证
/*
* 1. 主表中的主键在sheet中重复
* 2. 所有表中主键pk或主外键组合的PK+FK为空的数据忽略，并做提示
* 6. 根据主键在数据库中验证是否已经存在
* 2. 子表中的fk在主表中不存在
* 3. 子表中的fk+pk在sheet中重复
* =====以上数据库处理======
*
* 4. 每个表需要进行数据格式验证的列中的数据是否符合正则
* 5. 每个表的代码列将文本替换为代码（如浙江省替换为330000）
*/
//读取xml,保存每个表的基础信息
for (int i = 0; i < this._allData.Tables.Count; i++)
{
XmlTwoHandler XmlTwoHandler = new XmlTwoHandler(string.Format(@"{0}\{1}.xml", xmlPath, this._allData.Tables[i].TableName));
_dicXmls.Add(this._allData.Tables[i].TableName, new XmlTwoHandler(String.Format("{0}\\{1}.xml", xmlPath, this._allData.Tables[i].TableName))); //获取XMl
DataTable dtXml = XmlTwoHandler.GetAllData();
TableValidate validate = GetTableValidate(XmlTwoHandler, dtXml, this._allData.Tables[i]);
_dicValidate.Add(this._allData.Tables[i].TableName, validate);
}
if (_dicValidate.Count > 0 && _dicValidate.Keys.Contains(mainTableName))
{
//todo:判断sheet中的主表是否有主键列， 判断从表中有没有主键列和外键列
//如果主表主键不为空时，才进行数据库数据的匹配验证
if (!string.IsNullOrEmpty(_dicValidate[mainTableName].PK))
{
//执行数据库验证
if (!_importValidate.SqlBulkCopyInsertDataForValidate(_dicValidate))
{
//执行数据库临时表写入失败
DataRow dr = this._errorInfo.NewRow();
dr[0] = "数据库临时表";
dr[1] = "写入比对数据";
dr[3] = "写入比对数据失败";
this._errorInfo.Rows.Add(dr);
}
else
{
//插入数据库临时表成功后执行数据库已存在记录的比对验证
DataSet dsError = _importValidate.DatabaseDataValidate(_dicValidate, mainTableName, isDeleteExists);
if (dsError.Tables.Count > 0)
{
for (int i = 0; i < dsError.Tables.Count; i++)
{
this._errorInfo.Merge(dsError.Tables[i]);
}
}
}
}
//如果数据库验证通过,则开始做正则验证
if (this._errorInfo.Rows.Count == 0)
{
for (int i = 0; i < this._allData.Tables.Count; i++)
{
XmlTwoHandler XmlTwoHandler = new XmlTwoHandler(string.Format(@"{0}\{1}.xml", xmlPath, this._allData.Tables[i].TableName));
DataTable dtXml = XmlTwoHandler.GetAllData();
Dictionary <string, string> dicRegex = GetDicRegexData(dtXml); //获取xml配置表中所有需要验证正则的数据 <"样本数据","001">
Dictionary <string, Dictionary <string, string> > dicPath = GetDicXmlPathData(dtXml); //获取xml配置表中所有需要通过path获取的对应xml文件数据<列名，<cn名,值>>
//如果有需要进行正则验证的列则进行验证,否则跳过
if (dicRegex.Count > 0 || dicPath.Count > 0)
{
ValidateDataTableData(this._allData.Tables[i], dicRegex, dicPath);
}
}
}
}
#endregion
return(this._allData); //2013.11.27 by sang 暂时返回的是全部数据
}
/// <summary>
/// 为工作簿创建一个新的工作表
/// Created by sang at 2013.09.17
/// </summary>
/// <param name="dtSource">数据源表</param>
/// <param name="XmlName">配置文件</param>
private static void CreateSheets(HSSFWorkbook workbook, DataTable dtSource, string PathName, string XmlName)
{
ISheet sheet; //当前操作的sheet, 由AddNewSheet方法实例化
XmlTwoHandler XmlH = new XmlTwoHandler(PathName + XmlName); //实例化XMl
DataTable XmlTable = XmlH.GetAllData(); //获取XML中的数据
string sheetName = XmlH.GetRootNodeAttr("xmlns:p1"); //定义工作表名称
int rowIndex; //定义行索引, 从第2行开始写入, 因为第一行是列头
int rowStart; //数据起始写入行, 创建sheet后输出
sheet = AddNewSheet(workbook, XmlH, sheetName, out rowStart);
rowIndex = rowStart + 1; //从开始行的下一行开始写入数据
int columnMaxWidth = 100 * 256; //excel中列的最大宽度
ICellStyle dateStyle = workbook.CreateCellStyle(); //创建单元格显示样式
IDataFormat format = workbook.CreateDataFormat(); //创建数据信息
foreach (DataRow row in dtSource.Rows)
{
//如果大于65535行则另外新建一个sheet
if (rowIndex == 65535)
{
sheet = AddNewSheet(workbook, XmlH, sheetName + ((int)rowIndex / 65535).ToString(), out rowStart);
rowIndex = rowStart + 1;
}
//填充内容
IRow xlsNewRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < XmlH.NodeCount(); i++)
{
ICell xlsNewCell = xlsNewRow.CreateCell(i);
string columnName = XmlTable.Rows[i]["InnerText"].ToString();
//获取当前行当前列的值
string drValue = row[columnName].ToString();
//当前列配置文件的路径
string path = Convert.ToString(XmlTable.Rows[i]["path"]);
if (!string.IsNullOrEmpty(path))
{ //当配置文件不为空时,将drValue转换为配置文件中的值
XmlTwoHandler xmlconfig = new XmlTwoHandler(path);
DataTable configtable = xmlconfig.GetAllData();
drValue = configtable.Select("InnerText='" + drValue + "'")[0]["cn"].ToString();
xlsNewCell.SetCellValue(drValue);//给数据当前行当前列赋值,无论列是什么类型都作为string写入
}
else
{ //没有配置文件时根据类型写入drValue值
#region 根据类型写入单元格数据
switch (dtSource.Columns[columnName].DataType.ToString())
{
case "System.String": //字符串类型
xlsNewCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (!string.IsNullOrEmpty(drValue))
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
xlsNewCell.SetCellValue(dateV);
//ICellStyle dateStyle = workbook.CreateCellStyle();//创建单元格显示样式
//IDataFormat format = workbook.CreateDataFormat();//创建数据信息
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
xlsNewCell.CellStyle = dateStyle;
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
xlsNewCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
xlsNewCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
xlsNewCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
xlsNewCell.SetCellValue("");
break;
default:
xlsNewCell.SetCellValue("");
break;
}
#endregion
}
//根据数据重新设置列宽
int columnWidth = sheet.GetColumnWidth(i);
int dataWidth = (Encoding.GetEncoding(936).GetBytes(drValue).Length + 1) * 256;
if (columnWidth > 0 && dataWidth > columnWidth)
{
if (dataWidth > columnMaxWidth)
{
sheet.SetColumnWidth(i, columnMaxWidth);
//HSSFCellStyle cellStyle = (HSSFCellStyle)xlsNewCell.CellStyle;
//cellStyle.WrapText = true;
//xlsNewCell.CellStyle = cellStyle;
}
else
{
sheet.SetColumnWidth(i, dataWidth);
}
}
}
rowIndex++;
}
}
/// <summary>
/// 为工作簿增加一个工作表, 增加时会根据配置文件创建列头并根据列头设置列宽
/// Added by sang at 2013.09.18
/// </summary>
/// <param name="workbook"></param>
/// <param name="XmlH"></param>
/// <param name="sheetName"></param>
/// <param name="rowStart">数据开始行号</param>
/// <returns>增加的工作表</returns>
private static ISheet AddNewSheet(HSSFWorkbook workbook, XmlTwoHandler XmlH, string sheetName, out int rowStart)
{
ISheet sheet;
DataTable XmlTable = XmlH.GetAllData();
//如果没有定义sheetName则默认为"Sheet"
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "Sheet";
}
//如果工作表的名字已存在则在后面加(1)
if (workbook.GetSheet(sheetName) != null)
{
string tempSheetName = sheetName;
for (int i = 1; workbook.GetSheet(tempSheetName) != null; i++)
{
tempSheetName = sheetName + "(" + i.ToString() + ")";
}
sheetName = tempSheetName;
}
//为工作簿新增工作表
sheet = workbook.CreateSheet(sheetName);
//数据起始写入行
rowStart = 0;
IRow headerRow = sheet.CreateRow(rowStart);
//表头及样式
if (XmlH.GetRootNodeAttr("xmlns:p2") != "无")
{
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(XmlH.GetRootNodeAttr("xmlns:p2"));
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, XmlH.NodeCount() - 1));
//定义数据开始写入行
rowStart = 1;
}
//根据列头为工作表的每一列设置列宽
ICellStyle headStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
for (int columnCount = 0; columnCount < XmlH.NodeCount(); columnCount++)
{
string colName = XmlTable.Rows[columnCount]["InnerText"].ToString();
int textLength = 0;
//获取列头的字符串长度
textLength = Encoding.GetEncoding(936).GetBytes(colName).Length;//获取当前字符串的长度；
//列名和列头格式
ICell cell = headerRow.CreateCell(columnCount);
cell.SetCellValue(XmlTable.Rows[columnCount]["cn"].ToString());
headStyle.Alignment = HorizontalAlignment.Center;
//font.FontHeightInPoints = 12;
font.Boldweight = (short)FontBoldWeight.Bold;
headStyle.SetFont(font);
cell.CellStyle = headStyle;
//隐藏列设置列宽为0, 否则为列指定一个长度
if (Convert.ToBoolean(XmlTable.Rows[columnCount]["hidden"]))
{
sheet.SetColumnWidth(columnCount, 0);
}
else
{
sheet.SetColumnWidth(columnCount, (textLength + 1) * 256);
}
}
return(sheet);
}
