/// <summary>
/// 判断文件是否存在，不存在则创建
/// </summary>
/// <param name="fileFullPath">目录名+文件名</param>
private void fileExsitOrCreate(string fileFullPath)
{
if (!File.Exists(fileFullPath))
{
ReflectEntityProp <BillForExcel> goodsReflect = new ReflectEntityProp <BillForExcel>();
ReflectEntityProp <GoodsTypeForExcel> goodsTypeReflect = new ReflectEntityProp <GoodsTypeForExcel>();
HSSFWorkbook wb = new HSSFWorkbook();
//格式
ICellStyle style = wb.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = wb.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 12;
style.SetFont(font);
if (ExcelTool.getSheetDataAt(ExcelTool.getWorkBook(filePath + fileName), TableId.BillForExcel.ToString()) == null)
{
ExcelTool.createExcelColumns <BillForExcel>(ref wb, style);
}
if (ExcelTool.getSheetDataAt(ExcelTool.getWorkBook(filePath + fileName), TableId.GoodsTypeForExcel.ToString()) == null)
{
ExcelTool.createExcelColumns <GoodsTypeForExcel>(ref wb, style);
}
FileStream fs = new FileStream(fileFullPath, FileMode.Create);
wb.Write(fs);
fs.Close();
}
}
void ExecuteCommandName()
{
var folderBase = FolderPath;
if (Directory.Exists(folderBase))
{
BtnEnable = false;
var handledFolder = System.IO.Path.Combine(folderBase, "Handled");
if (!Directory.Exists(handledFolder))
{
Directory.CreateDirectory(handledFolder);
}
else
{
var msgResult = MessageBox.Show("转换后将会覆盖Handled文件夹里的文件", "注意", MessageBoxButton.YesNo);
if (msgResult != MessageBoxResult.Yes)
{
BtnEnable = true;
return;
}
}
var excelTool = new ExcelTool(folderBase, handledFolder);
Task.Run(() => excelTool.Handle(ExcuteMsg));
}
else
{
MessageBox.Show("文件目录不存在");
}
}
private static void ExcelToolWindow()
{
ExcelTool window = EditorWindow.GetWindow <ExcelTool>();
window.Init();
window.Show();
}
public JsonResult Upload()
{
object obj = null;
Dictionary <string, object> jo = new Dictionary <string, object>();
//## 如果有任何檔案類型才做
if (Request.Files.AllKeys.Any())
{
//## 讀取指定的上傳檔案ID
var httpPostedFile = Request.Files["UploadedImage"];
var isHeader = Request.Form["isHead"];
var importStartRow = Request.Form["importStartRow"];
//## 真實有檔案，進行上傳
if (httpPostedFile != null && httpPostedFile.ContentLength != 0)
{
var fileName = Path.GetFileName(httpPostedFile.FileName);
var path = Path.Combine(Server.MapPath("~/FileUploads"), fileName);
httpPostedFile.SaveAs(path);
jo.Add("result", true);
ExcelTool exceltool = new ExcelTool();
obj = exceltool.ExcelToJson(path, isHeader, importStartRow);
//jo.Add("result", obj);
}
}
JsonResult jOBJ = Json(obj);
return(Json(obj));
}
IEnumerator ReadConfigFile(string filename)
{
loadStep++;
string filepath = ExcelTool.GetConfigFilePath(filename);
WWW www = new WWW(filepath);
yield return(www);
while (www.isDone == false)
{
yield return(null);
}
if (www.error == null)
{
byte[] data = www.bytes;
List <ConfigClass> datalist = (List <ConfigClass>)ExcelTool.DeserializeObj(data);
dic.Add(filename, datalist);
}
else
{
//GameLogTools.SetText("wwwError<<" + www.error + "<<" + filepath);
Debug.Log("wwwError<<" + www.error + "<<" + filepath);
}
loadStep--;
if (loadStep <= 0)
{
LogData();
}
}
public static void CreateAllBattleAsset()
{
//===》需要读取的Excel表格
string battleCfgName = ExcelPath.excelsFolderPath + "Info.xlsx";
//===》读取的表
string Enemy = "怪物";
InfoCfg cfg = ScriptableObject.CreateInstance <InfoCfg>();
//===》赋值
cfg.enemyArray = ExcelTool.CreateEnemyData(battleCfgName, Enemy);
//确保文件夹存在
if (!Directory.Exists(ExcelPath.assetPath))
{
Directory.CreateDirectory(ExcelPath.assetPath);
}
//asset文件的路径 要以"Assets/..."开始，否则CreateAsset会报错
string assetPath = string.Format("{0}{1}.asset", ExcelPath.assetPath, "Info");
//生成一个Asset文件
AssetDatabase.CreateAsset(cfg, assetPath);
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
Debug.Log("=========》生成BattleInfo数据成功");
}
private void SalvaToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("Nessuna riga da salvare");
return;
}
data = (DataTable)dataGridView1.DataSource;
SaveFileDialog1.Title = "Salva File";
SaveFileDialog1.Filter = "File Excel (*.xls)|*.xls|Tutti i file (*.*)|*.*";
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
string sFileName = SaveFileDialog1.FileName;
if (sFileName.Trim() != "")
{
try
{
ExcelTool writer = new ExcelTool(sFileName);
writer.SetDriver("NPOI");
writer.FromDataTable(data);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
/// <summary>
/// 计算结果输出为Excel
/// </summary>
private void button5_Click(object sender, EventArgs e)
{
this.toolStripStatusLabel.Text = "导出EXCEL";
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "选择输出模版";
openFileDialog.Filter = "原油数据模版文件 (*.xls)|*.xls";//Excel2010与2003不兼容
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
int outResult = ExcelTool.outDataToExcel(null, this._oil, openFileDialog.FileName);
if (outResult == 1)
{
MessageBox.Show("数据导出成功!", "提示");
}
else if (outResult == -1)
{
MessageBox.Show("当前系统尚未安装EXCEL软件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (outResult == -2)
{
MessageBox.Show("不能打开Excel进程!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (outResult == -3)
{
MessageBox.Show("数据导出失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (outResult == -11)
{
return;
}
}
}
/// <summary>
/// 增加一行账单
/// </summary>
/// <param name="goods"></param>
/// <param name="errorMessage"></param>
/// <returns></returns>
public DataTable addBills(Goods goods, out string errorMessage)
{
errorMessage = "";
ISheet sheet = ExcelTool.getWorkBook(filePath + fileName).GetSheetAt((int)TableId.GoodsTypeForExcel);
ExcelTool.createRow <Goods>(ref sheet, goods);
return(new DataTable());
}
public void CSVReaderTest()
{
CSVWriterTest();
DataTable table = ExcelTool.CSVReader(path);
Assert.AreEqual(table.Rows.Count, 100);
Assert.AreEqual(table.Columns.Count, 2);
}
/// <summary>
/// List<T>转化为Excel文件,并返回FileStreamResult
/// </summary>
/// <param name="list">需要转化的List<T></param>
/// <param name="headerList">Excel标题行的List列表</param>
/// <param name="fileName">Excel的文件名</param>
/// <returns></returns>
public static FileStreamResult ExportListToExcel_MVCResult <T>(IList <T> list, IList <string> headerList, string fileName)
{
var fsr = new FileStreamResult(ExcelTool.ExportListToExcel(list, headerList, null), "application/ms-excel")
{
FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls")
};
return(fsr);
}
public void TestMethod2()
{
var svc = new XBD.Service.NarcoticsSvc();
var filePath = AppDomain.CurrentDomain.BaseDirectory + "/Files/危险化学品名录统计表3.xls";
var dt = ExcelTool.ToDataTable(filePath);
svc.AddAll(dt);
Assert.IsTrue(1 == 1);
}
/// <summary>
/// 下载Excel
/// </summary>
/// <returns></returns>
public async Task <FileResult> DownExcel(GetItemsInput input)
{
MemoryStream msData = new MemoryStream(10);
var result = await _cartAppService.GetCartByIds(input);
//创建一个临时文件
string filePath = $"{_env.WebRootPath }\\TempFile\\";
Stream stream = new ExcelTool <CartItemDto>().GetExcelByList(filePath, result.Items);
return(ToExcel(stream));
}
private void button1_Click(object sender, EventArgs e)
{
string path = @"C:\123.xls";
IWorkbook workBook = ExcelTool.getWorkBook(path);
ExcelTool.getSheetDataAt(workBook, 0);
ISheet sheet = workBook.GetSheetAt(0);
ExcelTool.deleteSheetRow(ref sheet, 14);
Stream fs = new FileStream(path, FileMode.Open);
workBook.Write(fs);
fs.Close();
}
private DataTable LoadFile(string filename)
{
try
{
ExcelTool xls = new ExcelTool(filename);
xls.SetDriver("NPOI");
return(xls.ToDataTable());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(null);
}
public static void CreateEnemyConfig()
{
string fileName = "EnemyAttributionConfig";
EnemyAttributionConfig config = CreateInstance <EnemyAttributionConfig>();
config.attributions = ExcelTool.CreateEnemyModelArrayWithExcel(ExcelConfig.GetExcelPath(fileName));
if (!Directory.Exists(ExcelConfig.ASSETS_FOLDER_PATH))
{
Directory.CreateDirectory(ExcelConfig.ASSETS_FOLDER_PATH);
}
string assetPath = string.Format("{0}{1}.asset", ExcelConfig.ASSETS_FOLDER_PATH, fileName);
AssetDatabase.CreateAsset(config, assetPath);
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
}
public void CSVWriterTest()
{
DataTable table = new DataTable("sheet1");
table.Columns.Add("ColumnA");
table.Columns.Add("ColumnB");
for (int i = 0; i < 100; i++)
{
var row = table.NewRow();
row[0] = "111";
row[1] = 222;
table.Rows.Add(row);
}
ExcelTool.CSVWriter(table, path);
bool exists = File.Exists(path);
Assert.AreEqual(exists, true);
}
public static void toJson()
{
var select = Selection.activeObject;
var path = AssetDatabase.GetAssetPath(select);
Dictionary <string, string[]> dic = ExcelTool.ToArrayListFromExcel(path, 1);
string newPath = path.Replace(path.Substring(path.LastIndexOf(".")), ".json");
if (System.IO.File.Exists(newPath))
{
System.IO.File.Delete(newPath);
}
using (System.IO.FileStream fs = System.IO.File.Create(newPath))// file = new System.IO.File())
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs);
sw.Write(ExcelTool.ToJsonFromDic(dic));
sw.Close();
}
}
public static void CreateItemAsset()
{
ItemManager manager = ScriptableObject.CreateInstance <ItemManager>();
//赋值
manager.dataArray = ExcelTool.CreateItemArrayWithExcel(ExcelConfig.excelsFolderPath + "Demo.xlsx");
//确保文件夹存在
if (!Directory.Exists(ExcelConfig.assetPath))
{
Directory.CreateDirectory(ExcelConfig.assetPath);
}
//asset文件的路径 要以"Assets/..."开始，否则CreateAsset会报错
string assetPath = string.Format("{0}{1}.asset", ExcelConfig.assetPath, "enemyWave01");
//生成一个Asset文件
AssetDatabase.CreateAsset(manager, assetPath);
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
}
public static void LoadJsonFromExcel_pushStr()
{
string epath = Application.streamingAssetsPath + "/pushStrConfig.xlsx";
string jpath = Application.dataPath + "/Resources/pushStr.json";
PushStr ps = null;
Dictionary <string, string[]> strs = ExcelTool.ToArrayListFromExcel(epath);
if (strs != null)
{
ps = new PushStr();
foreach (var tem in strs)
{
Debug.Log(tem.Value.Length + " str = " + tem.Value[0] + " key = " + tem.Key);
ps.Add(tem.Key);
}
}
if (ps != null)
{
try
{
string str = JsonMapper.ToJson(ps);
Debug.Log("json str = " + str);
if (System.IO.File.Exists(jpath))
{
System.IO.File.Delete(jpath);
}
System.IO.FileStream fs = new System.IO.FileStream(jpath, System.IO.FileMode.CreateNew);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs);
sw.WriteLine(str);
sw.Close();
fs.Close();
}
catch (System.Exception ex)
{
Debug.LogError("ex = " + ex.ToString());
}
}
}
private void Button_ClickExport(object sender, RoutedEventArgs e)
{
try
{
List <CheckInfo> list = ToolData.Record.CheckList;
for (int i = 0; i < list.Count; i++)
{
if (list[i].IsCheck)
{
ExcelTool.ExportExcel(list[i].FilePath, ToolData.Record.ExportPath + "\\" + list[i].ExportName + ".txt");
}
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.ToString());
}
finally
{
System.Windows.MessageBox.Show("导出完成");
}
}
public static void RefreshText()
{
string[] sdirs = { "Assets/Art/UIPrefab", "Assets/Editor Default Resources" };
var asstIds = AssetDatabase.FindAssets("t:Prefab", sdirs);
List <GameObject> gameObjects = new List <GameObject>();
foreach (var asstId in asstIds)
{
string path = AssetDatabase.GUIDToAssetPath(asstId);
var pfb = AssetDatabase.LoadAssetAtPath <GameObject>(path);
gameObjects.Add(pfb);
}
int i = 0;
List <UIText> texts = new List <UIText>();
foreach (var pfb in gameObjects)
{
foreach (var text in pfb.GetComponentsInChildren <MyText>(true))
{
bool exist = false;
GameObject go = null;
foreach (var mono in pfb.GetComponentsInChildren <MonoBehaviour>(true))
{
foreach (var field in mono.GetType().GetFields())
{
var xx = mono.GetFieldByReflect(field.Name);
if (xx.IsNotNull() && xx.GetType() == typeof(MyText))
{
if (xx.As <MyText>() == text)
{
go = mono.gameObject;
exist = true;
break;
}
}
}
}
if (!exist && text.GetComponent("TextAdapt") == null)
{
texts.Add(GetText(true, i, pfb.name, text));
}
else
{
texts.Add(GetText(false, i, go?.gameObject.name, text));
}
text.Index = i;
i++;
}
try
{
PrefabUtility.SavePrefabAsset(pfb);
}
catch (Exception e)
{
Debug.LogWarning(e.Message);
}
}
//excel文本
ExcelTool excelTool = new ExcelTool(true);
PropertyExcelTextManager propertyExcelTextManager = ScriptableObject.CreateInstance <PropertyExcelTextManager>();
propertyExcelTextManager.PropertyExcelText = excelTool.CreatePropertyExcelTextArrayWithExcel(propertyExcelTextManager);
List <UIText> excelTexts = new List <UIText>();
LanguageTranslate translate = new LanguageTranslate();
for (int j = 0; j < propertyExcelTextManager.PropertyExcelText.Length; j++)
{
excelTexts.Add(new UIText()
{
Fixed = true,
Index = j,
Name = DataEncryptManager.StringDecoder(propertyExcelTextManager.PropertyExcelText[j].ConstKey),
ParentName = "Excel",
SimplifiedChineses = new List <string>()
{
translate.ConvertChinSimp(propertyExcelTextManager.PropertyExcelText[j].ConstValue)
},
TraditionalChineses = new List <string>()
{
translate.ConvertChinTrad(propertyExcelTextManager.PropertyExcelText[j].ConstValue)
},
});
}
UITextManager textManager = new UITextManager
{
FixedText = texts,
ExcelText = excelTexts
};
AssetDatabase.CreateAsset(textManager, $"{ExcelConfig.assetPath}UITextManager.asset");
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
}
public ActionResult SubmitImport(HttpPostedFileBase filebase, string table)
{
HttpPostedFileBase file = Request.Files["file"];
Stream stream = file.InputStream;
if (file == null || file.ContentLength <= 0)
{
ViewBag.error = "文件不能为空";
return(View("Import"));
}
else
{
//var filename = Path.GetFileName(fullPath);
var filesize = file.ContentLength; //获取上传文件的大小单位为字节byte
var fileEx = System.IO.Path.GetExtension(file.FileName); //获取上传文件的扩展名
//var NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
var Maxsize = 4000 * 1024; //定义上传文件的最大空间大小为4M
var FileType = ".xls,.xlsx"; //定义上传文件的类型字符串
if (!FileType.Contains(fileEx))
{
ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
return(View("Import"));
}
if (filesize >= Maxsize)
{
ViewBag.error = "上传文件超过4M，不能上传";
return(View("Import"));
}
try
{
//导入数据库写法
//var dt = ExcelTool.ExcelToDataTable(true, fullPath);
var dt = ExcelTool.ReadStreamToDataTable(stream);
if (table != "" && table.Length >= 0)
{
if (table == "ManuallyCommInfo")//商品
{
new ManuallyComInfoApp().InputDate(dt);
}
if (table == "ManuallyTradeInfo")//订单
{
new ManuallyTradeInfoApp().InputDate(dt);
}
if (table == "Refund")//退款
{
new RefundApp().InputDate(dt);
}
}
}
catch (Exception ex)
{
ViewData["tableName"] = table;
ViewBag.error = ex.Message;
return(View("Import"));
}
}
ViewData["tableName"] = table;
ViewBag.error = "导入成功";
System.Threading.Thread.Sleep(2000);
return(View("Import"));
}
void OnGUI()
{
CheckData();
//
EditorGUILayout.BeginHorizontal("HelpBox");
EditorGUILayout.LabelField("ConfigPath:", GUILayout.Width(120));
EditorGUILayout.LabelField(configPath);
if (GUILayout.Button("NPOI", GUILayout.Width(BUTTON_WIDTH)))
{
DirectoryInfo dir = new DirectoryInfo(configPath);
if (!dir.Exists)
{
EditorUtility.DisplayDialog("error", "find not directory", "ok");
return;
}
FileInfo[] fileInfos = dir.GetFiles();
foreach (FileInfo fileInfo in fileInfos)
{
if (!ExcelTool.ExcelToLuaTableNPOT(fileInfo.FullName, savePath + ""))
{
continue;
}
}
}
if (GUILayout.Button("Kiang", GUILayout.Width(BUTTON_WIDTH)))
{
DirectoryInfo dir = new DirectoryInfo(configPath);
if (!dir.Exists)
{
EditorUtility.DisplayDialog("error", "find not directory", "ok");
return;
}
FileInfo[] fileInfos = dir.GetFiles();
foreach (FileInfo fileInfo in fileInfos)
{
if (!ExcelTool.ExcelToLuaTableNPOTKiang(fileInfo.FullName, savePath + ""))
{
continue;
}
}
}
if (GUILayout.Button("Select", GUILayout.Width(BUTTON_WIDTH)))
{
string path = EditorUtility.SaveFolderPanel("SelectConfigPath", configPath, string.Empty);
if (!path.Equals(string.Empty))
{
configPath = configBuilderData.ConfigPath = path;
SaveConfigData();
}
}
if (GUILayout.Button("Open", GUILayout.Width(BUTTON_WIDTH)))
{
System.Diagnostics.Process.Start("explorer.exe", configPath.Replace("/", "\\"));
}
//if (GUILayout.Button("EPPlus", GUILayout.Width(BUTTON_WIDTH)))
//{
// DirectoryInfo dir = new DirectoryInfo(configPath);
// if (!dir.Exists)
// {
// EditorUtility.DisplayDialog("error", "find not directory", "ok");
// return;
// }
// FileInfo[] fileInfos = dir.GetFiles();
// foreach (FileInfo fileInfo in fileInfos)
// {
// if (!ExcelTool.ExcelToLuaTableEPPlus(fileInfo.FullName, savePath+ "/EPPlus"))
// {
// continue;
// }
// }
//}
EditorGUILayout.EndHorizontal();
EditorGUILayout.BeginHorizontal("HelpBox");
EditorGUILayout.LabelField("SavePath:", GUILayout.Width(120));
EditorGUILayout.LabelField(savePath);
if (GUILayout.Button("Select", GUILayout.Width(BUTTON_WIDTH)))
{
string path = EditorUtility.SaveFolderPanel("SelectSavePath", savePath, string.Empty);
if (!path.Equals(string.Empty))
{
savePath = configBuilderData.SavePath = path;
SaveConfigData();
}
}
if (GUILayout.Button("Open", GUILayout.Width(BUTTON_WIDTH)))
{
System.Diagnostics.Process.Start("explorer.exe", savePath.Replace("/", "\\"));
}
EditorGUILayout.EndHorizontal();
}
void SelectFile(int index)
{
string filestr = ExcelTool.LoadData(fileList[index]);
EditorUtility.DisplayDialog("导出" + fileList[index] + "成功！", filestr, "确定");
}
public DataTable searchGoodsType()
{
DataTable dt_goodsType = ExcelTool.getSheetDataAt(ExcelTool.getWorkBook(filePath + fileName), (int)TableId.GoodsTypeForExcel);
return(dt_goodsType);
}
public static void GetWhiteList()
{
whiteList = null;
whiteList = new List <Dictionary <string, List <string> > >();
for (int iCount = 0; iCount < EnumCount; iCount++)
{
whiteList.Add(new Dictionary <string, List <string> >());
}
string pathsPath = @"CheckToolFiles\WhiteList.xlsx";
DataSet data = ExcelTool.GetData(pathsPath);
if (data == null)
{
return;
}
if (data.Tables == null)
{
return;
}
try
{
var Rows = data.Tables[0].Rows;
for (int iCount = 1; iCount < Rows.Count; iCount++)
{
bool isWhiteList = Rows[iCount][3].ToString().Trim().Contains("1") ? true : false;
if (isWhiteList)
{
string path = Rows[iCount][0].ToString().Trim();
path = path.Replace(@"\", "/");
var strs = path.Split(new string[1] {
"Assets/"
}, StringSplitOptions.RemoveEmptyEntries);
path = "Assets/" + strs[strs.Length - 1];
string name = Rows[iCount][1].ToString().Trim();
path += "/" + name;
path = path.Replace("//", "/");//以防重复添加斜杠
var indexStrs = Rows[iCount][2].ToString().Trim().Split(',');
for (int jCount = 0; jCount < indexStrs.Length; jCount++)
{
int index = 0;
int.TryParse(indexStrs[jCount], out index);
if (index > 0)
{
index -= 1; //枚举类型从1开始，而whiteList从0开始
}
else
{
continue;
}
if (!whiteList[index].ContainsKey(path))
{
whiteList[index].Add(path, null);
//Debug.Log(path + " " + (index + 1));
}
if (index == (int)CheckType.LostMotion - 1)
{
if (whiteList[index][path] == null)
{
whiteList[index][path] = new List <string>();
}
var stateStrs = Rows[iCount][4].ToString().Trim().Split(',');
foreach (var stateStr in stateStrs)
{
string state = stateStr.Trim();
if (!whiteList[index][path].Contains(state))
{
whiteList[index][path].Add(state);
}
//Debug.Log(stateStr);
}
}
}
}
}
}
catch
{
Debug.LogError("请查看根目录 " + pathsPath + " 是否存在且为最新版本。");
}
}
/// <summary>
/// 导入Excel到库A，并返回原油的oilInfoID
/// </summary>
/// <param name="path">excel的路径</param>
/// <returns></returns>
public static OilInfoEntity importExcel(string fileName)
{
OilInfoEntity oilA = null;
try
{
DataSet ds = ExcelTool.ExcelToDataSet(fileName);
#region "原油信息表的处理"
List <string> oilInfoItemCode = OilTableRowBll._OilTableRow.Where(d => d.oilTableTypeID == (int)EnumTableType.Info).Select(d => d.itemCode).Distinct().ToList();
oilA = findOilInfoTalbe(ds, oilInfoItemCode); //首先要找到原油信息表，没有原油信息表 则没必要继续下去
if (oilA.ID < 0)
{
return(oilA);
}
#endregion
List <string> allItemCode = OilTableRowBll._OilTableRow.Where(d => new int[] { (int)EnumTableType.Whole, (int)EnumTableType.Light, (int)EnumTableType.Narrow, (int)EnumTableType.Wide, (int)EnumTableType.Residue }.Contains(d.oilTableTypeID)).Select(d => d.itemCode).Distinct().ToList();
List <WCol> tableColList = new List <WCol>(); //记录所有窄馏分、宽馏分、渣油 数据列集合
foreach (DataTable table in ds.Tables) //找到原油信息表后，遍历每个表
{
tableColList.AddRange(getColListFromTable(table, allItemCode));
}
#region "对宽馏分数据处理,如果出现ICP，ECP相同的重复列则合并。"
var items = tableColList.Where(d => d.TableType == EnumTableType.Wide).GroupBy(x => new { x.ICP, x.ECP }).Select(d => new { keys = d.Key, Count = d.Count() });
foreach (var i in items)
{
if (i.Count > 1)
{
List <WCol> mergeList = tableColList.Where(d => d.TableType == EnumTableType.Wide && d.ICP == i.keys.ICP && d.ECP == i.keys.ECP).ToList();
WCol newCol = new WCol();
newCol.ECP = i.keys.ECP;
newCol.ICP = i.keys.ICP;
newCol.TableType = EnumTableType.Wide;
foreach (var mergeCol in mergeList)
{
newCol.Cells.AddRange(mergeCol.Cells);
tableColList.Remove(mergeCol);
}
tableColList.Add(newCol);
}
}
#endregion
//排序，找出宽馏分的，根据iep排序，渣油也排序
List <WCol> wideData = tableColList.Where(d => d.TableType == EnumTableType.Wide).OrderBy(d => d.MCP).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Wide);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Whole).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Whole);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Narrow).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Narrow);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Residue).OrderBy(d => d.MCP).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Residue);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Light).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Light);
}
catch (Exception ex)
{
Log.Error("数据管理,镇海导入Excel到库原始库:" + ex);
return(null);
}
return(oilA);
}
public ActionResult GetPageList(PageParameter param, OrderFilter filter,
OrderTeamSelect Type, ReportStyle ReportStyle, string subject = "", bool IsExport = false)
{
filter.LangType = SystemLanguage;
ViewBag.ReportStyle = ReportStyle;
string fileName = $"{Type.GetDisplayName()}-{subject}-" + string.Format("{0:MMddHHmmss}", DateTime.Now) + ".xlsx";
if (Type == OrderTeamSelect.CompetitionUnits)
{
var model = itemService.GetCompetitionUnits(filter.ArticleID.Value);
if (IsExport)
{
#region dataTable
List <CompetitionUnitsModelAll> dt = model
.SelectMany(x => x.Members.Select(y => new CompetitionUnitsModelAll
{
Creater = x.Creater,
Phone = x.Phone,
Email = x.Email,
County = x.County,
UnitTempNo = x.TempNo,
Unit = x.Unit,
UnitShort = x.UnitShort,
Coach = x.Coach,
Leader = x.Leader,
Manager = x.Manager,
OrderNumber = x.OrderNumber,
//TotalPrice = x.TotalPrice,
MemberCount = x.MemberCount,
MemberTempNo = y.TempNo,
MemberName = y.MemberName
})).ToList();
#endregion
var exportResult = ExcelTool.Create(dt);
return(File(exportResult, "application/vnd.ms-excel", fileName));
}
return(PartialView("_PageList_Units", model));
}
else if (Type == OrderTeamSelect.CompetitionItems)
{
var model = itemService.GetCompetitionItems(filter.ArticleID.Value);
if (IsExport)
{
#region dataTable
List <CompetitionItemsModelAll> dt = model
.SelectMany(x => x.Teams.SelectMany(y => y.Members.Select(z => new CompetitionItemsModelAll
{
Subject = x.Subject,
Option = x.Option,
TeamCount = x.TeamCount,
DetailTeamName = y.DetailTeamName,
FilePath = y.FilePath,
DetailPrice = y.DetailPrice,
DiscountPrice = y.DiscountPrice,
TempNo = z.TempNo,
MemberName = z.MemberName,
Unit = z.Unit
}))).ToList();
#endregion
var exportResult = ExcelTool.Create(dt);
return(File(exportResult, "application/vnd.ms-excel", fileName));
}
return(PartialView("_PageList_Items", model));
}
else if (Type == OrderTeamSelect.CompetitionMembers)
{
var model = itemService.GetCompetitionMembers(filter.ArticleID.Value);
if (IsExport)
{
#region dataTable
List <CompetitionMemberItemAll> dt = model
.SelectMany(x => x.Items.Select(y => new CompetitionMemberItemAll
{
TempNo = x.TempNo,
MemberName = x.MemberName,
IdentityCard = x.IdentityCard,
Birthday = x.Birthday,
Gender = x.Gender,
County = x.County,
UnitTempNo = x.UnitTempNo,
Unit = x.Unit,
Coach = x.Coach,
ItemCount = x.ItemCount,
Subject = y.Subject,
Option = y.Option,
DetailPrice = y.DetailPrice,
DiscountPrice = y.DiscountPrice
})).ToList();
#endregion
var exportResult = ExcelTool.Create(dt);
return(File(exportResult, "application/vnd.ms-excel", fileName));
}
return(PartialView("_PageList_Members", model));
}
else
{
var structure = structureService.Get(filter.StructureID);
ViewBag.Structure = structure;
var model = service.GetTeamList(param, filter);
return(PartialView("_PageList", model));
}
}
private void button3_Click(object sender, EventArgs e)
{
ExcelTool d = new ExcelTool();
d.OutputAsExcelFile(dataGridView1);
}
