public void ActionExcelData(FileExcelDatas exceldatas, string strnamespace, Action <string> show)
{
StringBuilder javaBuilder = new StringBuilder();
javaBuilder.AppendLine("package " + strnamespace + ";")
.AppendLine()
.AppendLine()
.AppendLine("import java.util.List;")
.AppendLine("import java.util.ArrayList;")
.AppendLine("import javax.persistence.Column;")
.AppendLine("import javax.persistence.Entity;")
.AppendLine("import javax.persistence.Id;")
.AppendLine("import javax.xml.bind.annotation.XmlRootElement;")
.AppendLine("import javax.persistence.Table;")
.AppendLine("import javax.persistence.Basic;")
.AppendLine()
.AppendLine()
.AppendLine("/* ")
.AppendLine("* Data Source To java Class ")
.AppendLine("* ")
.AppendLine("* Create Code：失足程序员<br>")
.AppendLine("* blog http://www.cnblogs.com/ty408/<br>")
.AppendLine("* Create Time：" + (DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss:sss")) + "<br>")
.AppendLine("* Phone：13882122019<br>")
.AppendLine("* email：[email protected]<br>")
.AppendLine("* ")
.AppendLine("*/")
.AppendLine("@XmlRootElement")
//.AppendLine("public class " + sheetName)
.AppendLine("{");
}
public void ActionExcelData(FileExcelDatas exceldatas, string strSplit, Action <string> show)
{
foreach (var datas in exceldatas.Datas)
{
StringBuilder txtBuilder = new StringBuilder();
string sheetName = datas.Value.SheetName;
string fileName = datas.Key;
show("创建文本数据：" + sheetName);
var rows = datas.Value.Rows.Values.ToArray();
for (int i = 0; i < rows.Length; i++)
{
var cells = rows[i].Cells;
for (int k = 0; k < cells.Count; k++)
{
txtBuilder.Append(cells[k].CellValue);
if (k < cells.Count - 1)
{
txtBuilder.Append(strSplit);
}
}
txtBuilder.AppendLine();
}
show("创建文本数据：" + fileName + " 共 " + rows.Length + " 行");
show("存储文件：" + fileName + " 目录：" + TextPath);
CreateClassFile(fileName, txtBuilder.ToString());
}
}
public void ActionExcelData(FileExcelDatas exceldatas, string strnamespace, Action <string> show)
{
Dictionary <string, ExcelDatas> datas = exceldatas.Datas;
List <string> snames = new List <string>();
StringBuilder builderDB = new StringBuilder();
builderDB.AppendLine("using System;");
builderDB.AppendLine("using System.Collections.Generic;");
builderDB.AppendLine("using System.Linq;");
builderDB.AppendLine("using System.Text;");
builderDB.AppendLine("");
builderDB.AppendLine("");
builderDB.AppendLine("namespace " + strnamespace);
builderDB.AppendLine("{");
builderDB.AppendLine(" /// <summary>");
builderDB.AppendLine(" ///");
builderDB.AppendLine(" /// <para>PS:</para>");
builderDB.AppendLine(" /// <para>@author 失足程序员</para>");
builderDB.AppendLine(" /// <para>@Blog http://www.cnblogs.com/ty408/</para>");
builderDB.AppendLine(" /// <para>@mail [email protected]</para>");
builderDB.AppendLine(" /// <para>@phone 13882122019</para>");
builderDB.AppendLine(" /// <para> @Create Time " + (DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss:sss")) + "</para>");
builderDB.AppendLine(" /// </summary>");
builderDB.AppendLine(" public class Gamesr_Data : Net.Sz.Framework.DB.Mysql.MysqlDbContext");
builderDB.AppendLine(" {");
builderDB.AppendLine(" static GameSr_Data()");
builderDB.AppendLine(" {");
builderDB.AppendLine(" Net.Sz.Framework.DB.ReportingDbMigrationsConfiguration<GameSr_Data>.Initializer();");
builderDB.AppendLine(" }");
builderDB.AppendLine("");
builderDB.AppendLine(" public Gamesr_Data(string nameOrConnectionString)");
builderDB.AppendLine(" : base(nameOrConnectionString)");
builderDB.AppendLine(" {");
builderDB.AppendLine(" }");
builderDB.AppendLine("");
HashSet <string> filenames = new HashSet <string>();
foreach (var data in datas)
{
string filename = "Q" + data.Value.SheetName.Replace("q_", "");
if (data.Value.Rows == null || data.Value.Rows.Count == 0 || !filenames.Add(filename))
{
continue;
}
CreateClassFile("Q" + data.Value.SheetName.Replace("q_", ""), CreateCode(datas, data.Value, strnamespace, show, builderDB));
}
builderDB.AppendLine("");
builderDB.AppendLine(" }");
builderDB.AppendLine("}");
CreateClassFile("Gamesr_Data", builderDB.ToString());
}
public void ActionExcelData(FileExcelDatas exceldatas, string strnamespace, Action <string> show)
{
Dictionary <string, ExcelDatas> datas = exceldatas.Datas;
HashSet <string> filenames = new HashSet <string>();
foreach (var data in datas)
{
string filename = "Q" + data.Value.SheetName.Replace("q_", "");
if (data.Value.Rows == null || data.Value.Rows.Count == 0 || !filenames.Add(filename))
{
continue;
}
CreateClassFile(filename, CreateCode(datas, data.Value, strnamespace, show, " ", true));
}
}
public void ActionExcel(FileExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action <string> show)
{
try
{
StringBuilder builder = new StringBuilder();
if (inDB)
{
builder.AppendLine("USE " + useStr + ";");
}
CreateTableHeadInster(builder, exceldatas, cellgs, useStr, inDB, show);
CreateTableRowInster(builder, exceldatas, cellgs, useStr, inDB, show);
if (inDB)
{
try
{
MySqlDB.Instance().ReMySqlConnection();
int count = MySqlDB.Instance().ExecuteNonQuery(builder.ToString());
show("写入数据 影响行数 " + count);
}
catch (Exception ex)
{
show("数据库操作失败：" + ex.ToString());
return;
}
finally
{
try
{
MySqlDB.Instance().Close();
}
catch (Exception ex)
{
show("数据库关闭错误：" + ex.ToString());
}
}
}
else
{
CreateClassFile("mysql", builder.ToString());
}
}
catch (Exception ex)
{
show("请仔细检查配置表 错误信息：" + ex.ToString());
}
}
public bool CreateTableHeadInster(StringBuilder builder, FileExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action <string> show)
{
HashSet <string> tablenames = new HashSet <string>();
foreach (var datas in exceldatas.Datas)
{
if (datas.Value.Rows.Count > 0)
{
string fileName = datas.Key;
string sheetName = datas.Value.SheetName;
if (tablenames.Add(sheetName))
{
show("创建数据表：" + sheetName);
builder.AppendLine("drop table if exists " + sheetName + ";")
.AppendLine("CREATE TABLE " + sheetName + "(")
.AppendLine(datas.Value.Rows.Values.ToList()[0].ToMysqlString(cellgs))
.AppendLine(");");
}
}
}
return(true);
}
public bool CreateTableRowInster(StringBuilder builder, FileExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action <string> show)
{
HashSet <string> tablenames = new HashSet <string>();
foreach (var itemkey in exceldatas.SheetName_And_FileName.Keys)
{
var datas = exceldatas.Datas[itemkey];
StringBuilder sqlfileBuilder = new StringBuilder();
string sheetName = datas.SheetName;
string fileName = itemkey;
if (!tablenames.Add(sheetName))
{
continue;
}
StringBuilder sqlBuilder = new StringBuilder();
List <ExcelCell> cellNames = new List <ExcelCell>();
var rows = datas.Rows.Values.ToArray();
{
var cells = rows[0].Cells;
sqlBuilder.Append("INSERT " + sheetName + " (");
bool isdouhao = false;
for (int k = 0; k < cells.Count; k++)
{
if (cells[k].Cellgs.Equals("ALL") || cellgs.Equals("ALL") || cells[k].Cellgs.Equals(cellgs))
{
cellNames.Add(cells[k]);
if (isdouhao)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("`" + cells[k].CellName + "`");
isdouhao = true;
}
if (k == cells.Count - 1)
{
sqlBuilder.AppendLine(")");
sqlBuilder.AppendLine(" VALUES ");
}
}
}
for (int i = 0; i < rows.Length; i++)
{
sqlBuilder.Append("(");
var cells = rows[i].Cells;
bool isdouhao = false;
for (int k = 0; k < cellNames.Count; k++)
{
ExcelCell cellK = cellNames[k];
ExcelCell cell = null;
foreach (var item in cells)
{
if (item.CellName.Equals(cellK.CellName))
{
cell = item;
break;
}
}
if (cell == null)
{
cell = cellK;
}
if (cell.Cellgs.Equals("ALL") || cellgs.Equals("ALL") || cell.Cellgs.Equals(cellgs))
{
if (isdouhao)
{
sqlBuilder.Append(",");
}
if (cell.CellValueType.ToUpper().Equals("STRING"))
{
sqlBuilder.Append("'" + cell.CellValue + "'");
}
else
{
sqlBuilder.Append(cell.CellValue);
}
isdouhao = true;
}
}
if ((i > 0 && i % 1000 == 0))
{
sqlBuilder.AppendLine(");");
sqlfileBuilder.Append(sqlBuilder.ToString());
sqlBuilder = new StringBuilder();
cellNames = new List <ExcelCell>();
{
sqlBuilder.Append("INSERT " + sheetName + " (");
isdouhao = false;
for (int k = 0; k < cells.Count; k++)
{
if (cells[k].Cellgs.Equals("ALL") || cellgs.Equals("ALL") || cells[k].Cellgs.Equals(cellgs))
{
cellNames.Add(cells[k]);
if (isdouhao)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("`" + cells[k].CellName + "`");
isdouhao = true;
}
if (k == cells.Count - 1)
{
sqlBuilder.AppendLine(")");
sqlBuilder.AppendLine(" VALUES ");
}
}
}
}
else if (i == rows.Length - 1)
{
sqlBuilder.AppendLine(");");
sqlfileBuilder.Append(sqlBuilder.ToString());
}
else
{
sqlBuilder.AppendLine("),");
}
}
builder.Append(sqlfileBuilder.ToString());
//CreateClassFile(fileName + "_Data", sqlfileBuilder.ToString());
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="exceldatas"></param>
/// <param name="cellgs"></param>
/// <param name="is_Not_Null">是否过滤空字符</param>
/// <param name="show"></param>
public void ActionExcelData(FileExcelDatas exceldatas, string cellgs, bool is_Not_Null, Action <string> show)
{
Dictionary <string, ExcelDatas> datas = exceldatas.Datas;
foreach (var file in datas)
{
StringBuilder notesBuilder = new StringBuilder();
string notes = "";
List <string> snames = new List <string>();
if (!GetNotes(datas, file.Key, ref snames, ref notesBuilder, show))
{
return;
}
while (snames.Count > 0)
{
notesBuilder.AppendLine(notes);
List <string> names = new List <string>();
foreach (var item in snames)
{
if (!GetNotes(datas, item, ref names, ref notesBuilder, show))
{
return;
}
}
snames.Clear();
snames.AddRange(names);
}
{
StringBuilder xmlBuilder = new StringBuilder();
string fileName = file.Key;
string SheetName = file.Value.SheetName;
xmlBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
xmlBuilder.Append(notesBuilder.ToString());
xmlBuilder.AppendLine("<" + SheetName + "Manager>");
foreach (var row in file.Value.Rows)
{
xmlBuilder.AppendLine(ExcelDataToXml(" ", SheetName, cellgs, row.Value, exceldatas, datas, is_Not_Null, false));
}
xmlBuilder.AppendLine("</" + SheetName + "Manager>");
show("创建 XML文件数据 共 " + file.Value.Rows.Count + " 行");
show("生成 CSharp XML文件：" + fileName + ".xml 文件目录：" + CSharpPathXml);
CreateCSharpXml(fileName, xmlBuilder.ToString());
}
{
StringBuilder xmlBuilder = new StringBuilder();
string fileName = file.Key;
string SheetName = file.Value.SheetName;
xmlBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
xmlBuilder.Append(notesBuilder.ToString());
xmlBuilder.AppendLine("<" + SheetName + "Manager>");
xmlBuilder.AppendLine(" <" + SheetName + "s>");
foreach (var row in file.Value.Rows)
{
xmlBuilder.AppendLine(ExcelDataToXml(" ", SheetName, cellgs, row.Value, exceldatas, datas, is_Not_Null, true));
}
xmlBuilder.AppendLine(" </" + SheetName + "s>");
xmlBuilder.AppendLine("</" + SheetName + "Manager>");
show("创建 XML文件数据 共 " + file.Value.Rows.Count + " 行");
show("生成 Java XML文件：" + fileName + ".xml 文件目录：" + JavaPathXml);
CreateJavaXml(fileName, xmlBuilder.ToString());
}
}
}
string ExcelDataToXml(string spces, string keyname, string cellgs, ExcelRow model, FileExcelDatas exceldatas, Dictionary <string, ExcelDatas> datas, bool is_Not_Null, bool isJava)
{
StringBuilder xmlBuilder = new StringBuilder();
bool isChild = model.Cells.Where(l => !string.IsNullOrWhiteSpace(l.CellFileName) && !string.IsNullOrWhiteSpace(l.CellValue) && (!l.CellValue.Equals("0"))).Count() > 0;
if (isChild)
{
xmlBuilder.AppendLine(spces + "<" + keyname + model.ToXmlString(cellgs, is_Not_Null) + ">");
foreach (var cell in model.Cells)
{
string dataKey = cell.CellFileName;
if (cell.Cellgs.Equals("ALL") || cell.Cellgs.Equals(cellgs))
{
if (!string.IsNullOrWhiteSpace(dataKey) && datas.ContainsKey(dataKey))
{
string[] keys = cell.CellValue.Split(',');
string sname = datas[cell.CellFileName].SheetName;
if (isJava)
{
xmlBuilder.AppendLine(spces + " <" + sname + "s>");
}
foreach (var keyID in keys)
{
if (!keyID.Equals("0"))
{
ExcelRow _cellModel = null;
if (!datas[cell.CellFileName].Rows.ContainsKey(keyID))
{
if (exceldatas.FileName_And_SheetName.ContainsKey(cell.CellFileName))
{
foreach (var item in exceldatas.FileName_And_SheetName[cell.CellFileName])
{
if (exceldatas.Datas[item].Rows.ContainsKey(keyID))
{
_cellModel = exceldatas.Datas[item].Rows[keyID];
break;
}
}
}
}
else
{
_cellModel = datas[cell.CellFileName].Rows[keyID];
}
if (_cellModel != null)
{
if (isJava)
{
xmlBuilder.AppendLine(ExcelDataToXml(spces + " ", sname, cellgs, _cellModel, exceldatas, datas, is_Not_Null, isJava));
}
else
{
xmlBuilder.AppendLine(ExcelDataToXml(spces + " ", sname, cellgs, _cellModel, exceldatas, datas, is_Not_Null, isJava));
}
}
}
}
if (isJava)
{
xmlBuilder.AppendLine(spces + " </" + sname + "s>");
}
}
}
}
xmlBuilder.Append(spces + "</" + keyname + ">");
}
else
{
string tmp = model.ToXmlString(cellgs, is_Not_Null);
//if (!string.IsNullOrWhiteSpace(tmp))
{
xmlBuilder.Append(spces + "<" + keyname + tmp + " />");
}
}
return(xmlBuilder.ToString());
}
