public ActionResult Load(string ModeType, string Format, string SettingName, string FieldName, string BeforeValue)
{
model.UserID = userInfo.Account;
if (string.IsNullOrEmpty(ModeType) ||
string.IsNullOrEmpty(Format) ||
string.IsNullOrEmpty(SettingName) ||
string.IsNullOrEmpty(FieldName) ||
string.IsNullOrEmpty(BeforeValue))
{
return(View("New", model));
}
model.ViewStatus = "E";
using (vwCodeMappingRepository rep = new vwCodeMappingRepository())
{
vwCodeMapping map = rep.get(SettingName, ModeType, Format, FieldName, BeforeValue);
model.CustomerName = map.CustomerName;
model.ModeType = map.ModeType;
model.Format = map.Format;
model.SettingName = map.SettingName;
model.FieldName = map.FieldName;
model.BeforeValue = map.BeforeValue;
model.AfterValue = map.AfterValue;
}
return(View("Edit", model));
}
static bool appendXmlByRow(DataRow row, List <tblXMLMapping> mappings, XmlDocument xmlDoc, XmlElement Node, XmlElement parentNode, string FatherTag, int layer)
{
bool output = true;
// 根節點
var roots = mappings.Where(x => x.FatherTag.Equals(FatherTag, StringComparison.OrdinalIgnoreCase)).OrderBy(x => x.Idx);
foreach (var root in roots)
{
string value = string.Empty;
if (!string.IsNullOrEmpty(root.FieldName) || !string.IsNullOrEmpty(root.DefaultValue))
{
if (string.IsNullOrEmpty(root.FieldName))
{
value = root.DefaultValue;
}
else
{
value = (string.IsNullOrEmpty(row[root.FieldName].ToString())) ? root.DefaultValue : row[root.FieldName].ToString();
}
// 代碼轉換
if (codeMap.Where(x => x.FieldName.Equals(root.TagName, StringComparison.OrdinalIgnoreCase)).Count() > 0 &&
codeMap.Where(x => x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase)).Count() > 0)
{
vwCodeMapping map = codeMap.Find(x => x.FieldName.Equals(root.TagName, StringComparison.OrdinalIgnoreCase) && x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase));
if (map != null)
{
value = map.AfterValue;
}
}
}
XmlNode node;
if (parentNode == null || parentNode.ChildNodes.Count == 0)
{
node = Node.SelectSingleNode(strRepeat("/", layer) + root.TagName);
}
else
{
node = parentNode.SelectSingleNode(strRepeat("/", layer + 1) + root.TagName);
}
if (node == null)
{
XmlElement rootNode = xmlDoc.CreateElement(root.TagName);
if (!string.IsNullOrEmpty(value))
{
rootNode.InnerText = value;
}
appendXmlByRow(row, mappings, xmlDoc, rootNode, Node, root.TagName, layer);
Node.AppendChild(rootNode);
}
else
{
XmlNodeList nodes = node.ParentNode.SelectNodes(strRepeat("/", layer + 1) + root.TagName);
//// 單純為階層節點時、允許重複
if (string.IsNullOrEmpty(root.FieldName) && string.IsNullOrEmpty(root.DefaultValue) && root.CanRepeat)
{
XmlElement rootNode = xmlDoc.CreateElement(root.TagName);
if (!string.IsNullOrEmpty(value))
{
rootNode.InnerText = value;
}
if (appendXmlByRow(row, mappings, xmlDoc, rootNode, Node, root.TagName, layer))
{
Node.AppendChild(rootNode);
}
}
else
{
int nodeCount = 0;
XmlNodeList existedNode = parentNode.SelectNodes(strRepeat("/", layer + 1) + root.TagName);
if (!string.IsNullOrEmpty(root.FieldName))
{
nodeCount = existedNode.Cast <XmlNode>().Where(n => n.FirstChild.InnerText == row[root.FieldName].ToString()).Count();
}
if (nodes.Count > 0 && !string.IsNullOrEmpty(root.FieldName) && nodeCount == 0)
{
XmlElement rootNode = xmlDoc.CreateElement(root.TagName);
if (!string.IsNullOrEmpty(value))
{
rootNode.InnerText = value;
}
appendXmlByRow(row, mappings, xmlDoc, rootNode, Node, root.TagName, layer);
Node.AppendChild(rootNode);
}
else
{
XmlElement element;
if (string.IsNullOrEmpty(root.FieldName))
{
element = existedNode.Cast <XmlElement>().FirstOrDefault();
}
else
{
element = existedNode.Cast <XmlElement>().Where(n => n.FirstChild.InnerText == row[root.FieldName].ToString()).FirstOrDefault();
}
appendXmlByRow(row, mappings, xmlDoc, element, (XmlElement)element.ParentNode, root.TagName, layer);
output = false;
}
}
}
}
return(output);
}
/// <summary>
/// 輸出Excel(重複不顯示)
/// </summary>
/// <param name="FileName">輸出檔名</param>
/// <param name="sheetName">Sheet名稱</param>
/// <param name="Datas">填入之資料</param>
/// <param name="mappings">填入之資料</param>
public static HSSFWorkbook GenerateExcel(DataTable dt, List <tblExcelMapping> mappings)
{
string ExcelName = mappings[0].ExcelName;
// 取得代碼轉換資料
using (vwCodeMappingRepository rep = new vwCodeMappingRepository())
{
codeMap = rep.query("", "EXPORT", "EXCEL", ExcelName, "");
}
HSSFWorkbook book = new HSSFWorkbook();
IEnumerable <string> sheetNames = mappings.Select(x => x.SheetName).Distinct();
foreach (var name in sheetNames)
{
ISheet sheet;
sheet = (HSSFSheet)book.GetSheet(name);
if (sheet == null)
{
sheet = (HSSFSheet)book.CreateSheet(name);
}
sheet.DisplayZeros = true;
List <tblExcelMapping> Columns = mappings.Where(x => x.SheetName.Equals(name, StringComparison.OrdinalIgnoreCase)).OrderBy(x => x.X).ToList();
#region 表頭
IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < Columns.Count(); i++)
{
int X = Columns[i].X - 1;
ICell cell = headerrow.CreateCell(X);
cell.CellStyle = style;
cell.SetCellValue(Columns[i].ColumnName);
}
#endregion
#region 填入內容
for (int i = 0; i < Columns.Count(); i++)
{
int X = Columns[i].X - 1;
ICellStyle dataStyle = book.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.Center;
dataStyle.VerticalAlignment = VerticalAlignment.Center;
if (!string.IsNullOrEmpty(Columns[i].NewLineChar))
{
dataStyle.WrapText = true; // 有設定換行字元時，啟動自動換行
}
if (Columns[i].DataType == "Date")
{
//顯示日期格式
HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd");
}
if (Columns[i].DataType == "DateTime")
{
////顯示日期格式
HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
}
else if (Columns[i].DataType == "Integer")
{
////顯示有逗號區分的數值資料
HSSFDataFormat numericformat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = numericformat.GetFormat("###,##0");
}
else if (Columns[i].DataType == "Decimal")
{
////顯示有逗號區分的小數資料
HSSFDataFormat decimalFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = decimalFormat.GetFormat("###,##0.0000");
}
int fixRow = -1;
for (int row = 0; row < dt.Rows.Count; row++)
{
IRow dataRow;
if (i == 0)
{
dataRow = sheet.CreateRow(row + 1);
}
else
{
dataRow = sheet.GetRow(row + 1);
}
string value = string.Empty;
if (!string.IsNullOrEmpty(Columns[i].FieldName) || !string.IsNullOrEmpty(Columns[i].DefaultValue))
{
if (string.IsNullOrEmpty(Columns[i].FieldName))
{
value = Columns[i].DefaultValue;
}
else
{
value = (string.IsNullOrEmpty(dt.Rows[row][Columns[i].FieldName].ToString())) ? Columns[i].DefaultValue : dt.Rows[row][Columns[i].FieldName].ToString();
}
// 代碼轉換
if (codeMap.Where(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase)).Count() > 0 &&
codeMap.Where(x => x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase)).Count() > 0)
{
vwCodeMapping map = codeMap.Find(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase) && x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase));
if (map != null)
{
value = map.AfterValue;
}
}
if (!string.IsNullOrEmpty(Columns[i].NewLineChar))
{
value = value.Replace(Columns[i].NewLineChar, "\n");
}
}
// 沒有指定 Field 時，代入 Default Value
if (string.IsNullOrEmpty(Columns[i].FieldName))
{
ICell cell = dataRow.CreateCell(X);
cell.CellStyle = dataStyle;
SetCellValue(ref cell, Columns[i].DataType, value);
}
// 判斷是否和上筆資料一致
else if (row == 0 || Columns[i].CanRepeat || (dt.Rows[row][Columns[i].FieldName].ToString() != dt.Rows[row - 1][Columns[i].FieldName].ToString()))
{
ICell cell = dataRow.CreateCell(X);
cell.CellStyle = dataStyle;
SetCellValue(ref cell, Columns[i].DataType, value);
}
}
sheet.AutoSizeColumn(X);
}
#endregion
}
return(book);
}
