private IEnumerable<SaleReportDto> CreateSalesReports(DateTime date, IExcelDataReader dataReader)
{
var reports = new List<SaleReportDto>();
var worksheet = dataReader.AsDataSet().Tables["Sales"];
IList<DataRow> rows = (from DataRow r in worksheet.Rows select r).ToList();
var location = rows[1].ItemArray[1].ToString();
var rowsCount = rows.Count();
for (int i = 3; i < rowsCount - 1; i++)
{
var productName = rows[i].ItemArray[1].ToString();
var quantity = Convert.ToInt32(rows[i].ItemArray[2]);
var price = Convert.ToDecimal(rows[i][3]);
var report = new SaleReportDto(productName, quantity, price, date, location);
reports.Add(report);
}
return reports;
}
private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate)
{
using (var db = new ChainOfSupermarketsContext())
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName, db);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
productName = Regex.Replace(productName, @"[^\w'\. ]", string.Empty);
var currentProduct = GetOrCreateProduct(productName, db);
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
db.Sales.Add(new Sale
{
Location = currentLocation,
DateOfSale = currentDate,
Product = currentProduct,
Quantity = (decimal)quantity,
PricePerUnit = (decimal)pricePerUnit
});
}
db.SaveChanges();
}
}
private Dictionary<string, string> FetchData(IExcelDataReader excelData)
{
DataTable excelDataTable = excelData.AsDataSet().Tables[0];
int columnCount = excelDataTable.Columns.Count;
var query = from a in excelDataTable.AsEnumerable()
select a;
Dictionary<string, string> ColumnValues = new Dictionary<string, string>();
foreach (var item in query)
{
ColumnValues.Add(ValidateStudentNumber(item.Field<double>(0)),
item.Field<string>(1));
}
return ColumnValues;
}
public static void setExcelFilePath(String filePath)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
result = excelReader.AsDataSet();
}
private void AddSalesToDB(IExcelDataReader excelReader, DateTime currentDate)
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
var currentProduct = GetOrCreateProduct(productName,pricePerUnit);
Sale currentSale = new Sale
{
LocationId = currentLocation.Id,
DateOfSale = currentDate,
ProductId = currentProduct.Id,
Quantity = (decimal) quantity,
PricePerUnit = (decimal) pricePerUnit
};
this.db.Sales.Add(currentSale);
}
this.db.SaveChanges();
}
public List <List <object>[]> ReadFileJvnlp(StreamReader fileMemoryStream, string fileName)
{
IExcelDataReader bookExcel = null;
// register provide encoding 1252 to Excel
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
try
{
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (fileName.Split(".").LastOrDefault().Equals("xls"))
{
bookExcel = ExcelReaderFactory.CreateBinaryReader(fileMemoryStream.BaseStream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (fileName.Split(".").LastOrDefault().Equals("xlsx"))
{
bookExcel = ExcelReaderFactory.CreateOpenXmlReader(fileMemoryStream.BaseStream);
}
//DataSet - The result of each spreadsheet will be created in the result.Tables
var tableJvnlp = bookExcel?.AsDataSet();
CreateTablesJvnlpAsync(tableJvnlp);
if (Drugs == null || RemovedDrugs == null)
{
return(new List <List <object>[]>());
}
var drugs = Drugs.Rows.Cast <DataRow>().Select(x => x.ItemArray.ToList()).ToArray();
var rmDrugs = RemovedDrugs.Rows.Cast <DataRow>().Select(x => x.ItemArray.ToList()).ToArray();
RemoveNullColumns(drugs, rmDrugs);
bookExcel?.Close();
fileMemoryStream.Close();
LoadDateUpdate();
return(new List <List <object>[]>
{
drugs,
rmDrugs
});
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
private void importExcelToDb(string fullName)
{
using (var unit = GetUnitOfWork())
{
//var currentConnectorID = Client.User.ConnectorID;
//var currentConnectorID = 1;
if (System.IO.File.Exists(fullName))
{
FileStream excelStream = System.IO.File.Open(fullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream);
var ds = excelReader.AsDataSet();
var temp = (from p in ds.Tables[0].AsEnumerable().Skip(2) select p).FirstOrDefault();
var content = (from p in ds.Tables[0].AsEnumerable().Skip(2)
let numberOfFields = p.ItemArray.Count()
select new
{
connectorID = Convert.ToInt32(p.Field <object>(numberOfFields - 1)),
vendorID = Convert.ToInt32(p.Field <object>(numberOfFields - 2)),
productID = Convert.ToInt32(p.Field <object>(numberOfFields - 3)),
Label = Convert.ToString(p.Field <object>(numberOfFields - 4)),
Price = Convert.ToDecimal(p.Field <object>(numberOfFields - 5)),
toDate = DateTime.FromOADate(Convert.ToDouble(p.Field <object>(numberOfFields - 6))),
fromDate = DateTime.FromOADate(Convert.ToDouble(p.Field <object>(numberOfFields - 7)))
}).ToList();
//insert the content into the db
foreach (var c in content)
{
var rows = unit.Scope.Repository <ContentPrice>().GetAll().Where(x => (x.ProductID == c.productID) && (x.ConnectorID == c.connectorID) && (x.VendorID == c.vendorID)).ToList();
if (rows != null)
{
if (rows.Count != 0)
{
foreach (var r in rows)
{
if (!String.IsNullOrEmpty(c.Label))
{
r.ContentPriceLabel = c.Label;
r.FromDate = c.fromDate;
r.ToDate = c.toDate;
r.FixedPrice = c.Price;
}
}
}
else
{
//create a new row
var product = unit.Scope.Repository <Product>().GetAll().Where(x => x.ProductID == c.productID).SingleOrDefault();
if (product != null)
{
// find the brandid
var brandID = product.BrandID;
var ContentPriceRuleIndex = 4;
var PriceRuleType = 1;
var Margin = "+";
var VendorID = Convert.ToInt32(c.vendorID);
ContentPrice cp = new ContentPrice()
{
VendorID = VendorID, ConnectorID = c.connectorID, BrandID = brandID, ProductID = product.ProductID, Margin = Margin, CreatedBy = Client.User.UserID, CreationTime = DateTime.Now, ContentPriceRuleIndex = ContentPriceRuleIndex, PriceRuleType = PriceRuleType, FromDate = c.fromDate, ToDate = c.toDate, FixedPrice = c.Price, ContentPriceLabel = c.Label
};
unit.Scope.Repository <ContentPrice>().Add(cp);
// //unit.Save();
}
}
}
}
unit.Save();
}
}
}
public static List <ImporterResult> ImportEnsayosStep1() //verifica si el formato de los headers es el correcto
{
List <ImporterResult> importerResults = new List <ImporterResult>();
int count = 0;
int rowCount = 0;
int tableCount = 0;
string validHeader = "provincia|localidad||establecimiento|campaña|fecha siembra|fecha cosecha|producto||rinde|indice %|quebrado|vuelco|altura de la planta|humedad|espromedio?|plantas x hectárea|días a floración (integer)|emergencia a floración|observaciones|archivo asociado|";
IExcelDataReader excelReader = null;
try
{
//Check Prerequisites
//1. Chequeo que esté el archivo
string sourceDirectoryPath = Configuration.ImportersPath;
DirectoryInfo di = new DirectoryInfo(sourceDirectoryPath);
FileInfo[] files = di.GetFiles("*.xls*", SearchOption.TopDirectoryOnly);
if (files.Length == 0)
{
importerResults.Add(new ImporterResult
{
TipoError = TipoError.SinArchivo,
Severity = Entities.Severity.Error,
Description = "No hay archivos a importar en el servidor.",
});
}
foreach (FileInfo info in files)
{
FileStream stream = File.Open(info.FullName, FileMode.Open, FileAccess.Read);
if (info.Extension.Equals(".xls"))
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (info.Extension.Equals(".xlsx"))
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
excelReader.IsFirstRowAsColumnNames = false;
DataSet result = excelReader.AsDataSet();
for (int t = 0; t < result.Tables.Count; t++)
{
tableCount = t;
rowCount = 0;
string header = string.Empty;
for (int c = 1; c < 22; c++)
{
header += result.Tables[t].Rows[rowCount][c].ToString().Trim().ToLower() + "|";
}
header = header.Replace('\n', ' ');
if (!header.Equals(validHeader))
{
count++;
importerResults.Add(new ImporterResult
{
TipoError = TipoError.FormatoIncorrecto,
Severity = Entities.Severity.Error,
Description = (string.Format("La planilla {0} no tiene el formato correcto!. Verificar el nombre de las columnas del encabezado,", info.Name) + Environment.NewLine + String.Format("Formato adecuado: {0}", validHeader)),
});
}
}
}
}
catch (Exception ex)
{
count++;
importerResults.Add(new ImporterResult
{
Severity = Entities.Severity.Error,
Description = ex.Message,
});
}
finally
{
//7. Free resources (IExcelDataReader is IDisposable)
if (excelReader != null)
{
excelReader.Close();
}
}
return(importerResults); // si el count es 0 el header esta bien, sino especifica que tipo de error fue.
}
/// <summary>
/// 只支持.xlsx格式，不支持03版本excel(.xls)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List <ExcelTable> Read(string filePath)
{
Debug.Log("read file:" + filePath);
List <ExcelTable> excelTables = new List <ExcelTable>();
string fileName = Path.GetFileName(filePath);
FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet result = excelReader.AsDataSet();
//获得一个xlsx文件中所有的tables
DataTableCollection tables = result.Tables;
fs.Close();
foreach (DataTable table in tables)
{
ExcelTable et = new ExcelTable();
et.tableName = table.TableName;
////中文表不读，用于整体性的说明、注释作用
////要导出的表需要以t_开头
if (et.tableName.ContainChinese() || !et.tableName.StartsWith("t_"))
{
continue;
}
Debug.Log("begin read------>fileName:" + fileName + ",tableName:" + et.tableName);
et.rowCount = table.Rows.Count;
if (et.rowCount < 6)
{
Debug.LogWarnning("fileName:" + fileName + ", tableName:" + et.tableName + ", line count less than 6, we will not handle with it");
continue;
}
for (int i = 0; i < table.Columns.Count; i++)
{
//init ExcelField
string name = table.Rows[0][i].ToString();
if (name.ContainChinese())//中文字段用于注释，不处理
{
continue;
}
string typeDes = table.Rows[1][i].ToString();
string tag = table.Rows[3][i].ToString();
if (i == 0 && tag == "KVT")//check KVT format whether correct
{
et.isKVT = true;
if (table.Rows[0][0].ToString() != "Key" ||
table.Rows[0][1].ToString() != "Value" ||
table.Rows[0][2].ToString() != "KeyDes")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but field name is not correct!");
}
if (table.Rows[1][0].ToString() != "string")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but key's type is not string");
}
}
string defaultValue = table.Rows[2][i].ToString();
string fieldDes = table.Rows[4][i].ToString();
string fieldDesMore = table.Rows[5][i].ToString();
ExcelField field = new ExcelField(et.tableName, name, typeDes, defaultValue, tag, fieldDes, fieldDesMore, i == 0 ? true : false);
//read Field datas
for (int j = 6; j < et.rowCount; j++)
{
string tmpValue = table.Rows[j][i].ToString().Trim();
field.AddData(tmpValue == "" ? defaultValue : tmpValue);
}
et.AddExcelField(field);
}
#region old
//////for (int i = 0; i < et.rowCount; i++)
//////{
////// if (i == 0)//获得key
////// {
////// for (int j = 0; j < et.columnCount; j++)
////// {
////// var key = table.Rows[0][j].ToString();
////// if (j == 0)//主键
////// {
////// et.primaryKeyName = key;
////// et.primaryKeyType = table.Rows[1][j].ToString();
////// if (table.Rows[2][j].ToString().Contains("KVT"))
////// {
////// et.isKVPairTable = true;
////// et.valueKeyType = table.Rows[1][1].ToString();
////// if (table.Rows[0][1].ToString() != "Value" || table.Rows[0][2].ToString() != "KeyDes")
////// {
////// throw new Exception(et.tableName + "表有问题，KVT表，要求第二个字段名为 Value，第三个字段名为 KeyDes");
////// }
////// }
////// else
////// {
////// et.isKVPairTable = false;
////// }
////// }
////// Console.WriteLine("@@初始化map, key:" + key);
////// et.tableContent[key] = new List<string>();
////// if (et.keys.Contains(key))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + ",有重复的字段:" + key);
////// }
////// else
////// {
////// if (!key.ContainChinese() && key != "KeyDes")//字段非中文才加入,且 KeyDes字段不用加入
////// {
////// et.keys.Add(key);
////// }
////// }
////// }
////// Console.WriteLine("@@初始化map key完毕");
////// }
////// else
////// {
////// for (int k = 0; k < et.columnCount; k++)
////// {
////// string key = table.Rows[0][k].ToString();
////// string property = table.Rows[i][k].ToString();
////// Console.WriteLine("table:" + et.tableName + " , key: " + key + " , property：" + property);
////// //主键的数据要保持唯一性
////// if (k == 0 && i > 4 && et.tableContent[key].Contains(property))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + "的主键" + key + "，property:" + property);
////// }
////// else
////// {
////// if (property == "")
////// {
////// property = "囧";//对于数据表中，用户没有填写的空格，默认赋值为 囧 ，读取的时候需要特殊处理
////// }
////// et.tableContent[key].Add(property);
////// }
////// }
////// }
//////}
#endregion
excelTables.Add(et);
}
return(excelTables);
}
private void btnBrowse_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
grvData.Columns.Clear();
OpenFileDialog ofd = new OpenFileDialog();
if (chkAutoCheck.Checked)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
var result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
btnBrowse.Text = openFileDialog1.FileName;
}
else if (result == DialogResult.Cancel)
{
return;
}
try
{
var stream = new FileStream(btnBrowse.Text, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var sw = new Stopwatch();
sw.Start();
IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);
var openTiming = sw.ElapsedMilliseconds;
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
//toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
var tablenames = GetTablenames(ds.Tables);
cboSheet.DataSource = tablenames;
if (tablenames.Count > 0)
{
cboSheet.SelectedIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
if (ofd.ShowDialog() == DialogResult.OK)
{
btnBrowse.Text = ofd.FileName;
cboSheet.DataSource = null;
cboSheet.DataSource = TextUtils.ListSheetInExcel(ofd.FileName);
}
}
}
//导出XML
public void LoadExcel(Program.Options options)
{
Dictionary <String, DataTable> sheets = new Dictionary <string, DataTable>();
Program.Log("开始加载EXECL文件");
foreach (var file in options.readFilePaths)
{
string excelPath = file;
string excelName = Path.GetFileNameWithoutExtension(excelPath);
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel file is empty: " + excelPath);
}
//// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet is empty: " + excelPath);
}
Program.Log("读取" + Path.GetFileName(excelPath) + "完成!");
sheets.Add(excelName, sheet);
}
}
//生成XML
//创建XmlDocument对象
XmlDocument xmlDoc = new XmlDocument();
//XML的声明<?xml version="1.0" encoding="gb2312"?>
XmlDeclaration xmlSM = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null);
//追加xmldecl位置
xmlDoc.AppendChild(xmlSM);
//添加一个名为Gen的根节点
XmlElement xml = xmlDoc.CreateElement("", "root", "");
//追加Gen的根节点位置
xmlDoc.AppendChild(xml);
Program.Log("生成XML...");
foreach (KeyValuePair <String, DataTable> kvp in sheets)
{
DataTable sheet = kvp.Value;
//先检查此table是否为客户端使用
int clientUseCount = 0;
for (int i = 0; i < sheet.Rows.Count; i++)
{
DataRow row = sheet.Rows[i];
//在这一行数据中查找客户端用字段
bool isClientUse = false;
foreach (DataColumn column in sheet.Columns)
{
string fieldName = column.ToString();
if (fieldName != "客户端用")
{
continue;
}
object value = row[column];
if (value.ToString() != "")
{
try
{
if (int.Parse(value.ToString()) == 1)
{
isClientUse = true;
}
}
catch (Exception ex)
{
Program.Log("当前文件名:" + kvp.Key.ToString());
Program.Log(ex.ToString());
return;
}
}
if (isClientUse)
{
clientUseCount++;
}
}
}
if (clientUseCount <= 1)
{
continue;
}
string key1 = ""; string key2 = "";
XmlElement data = xmlDoc.CreateElement("data");
for (int i = 0; i < sheet.Rows.Count; i++)
{
DataRow row = sheet.Rows[i];
bool isClientUse = false;
String name = ""; String type = ""; String comment = "";
foreach (DataColumn column in sheet.Columns)
{
object value = row[column];
string fieldName = column.ToString();
if (fieldName == "字段名")
{
name = value.ToString();
}
else if (fieldName == "数据类型")
{
if (Regex.Match(value.ToString(), "char").Success)
{
type = "string";
}
else if (Regex.Match(value.ToString(), "UINT8").Success)
{
type = "byte";
}
else if (Regex.Match(value.ToString(), "UINT16").Success)
{
type = "ushort";
}
else if (Regex.Match(value.ToString(), "UINT32").Success)
{
type = "uint";
}
else if (Regex.Match(value.ToString(), "UINT64").Success)
{
type = "ulong";
}
else if (Regex.Match(value.ToString(), "INT8").Success)
{
type = "char";
}
else if (Regex.Match(value.ToString(), "INT16").Success)
{
type = "short";
}
else if (Regex.Match(value.ToString(), "INT32").Success)
{
type = "int";
}
else if (Regex.Match(value.ToString(), "INT64").Success)
{
type = "long";
}
else
{
type = value.ToString();
}
}
else if (fieldName == "客户端用")
{
if (value.ToString() != "")
{
try
{
if (int.Parse(value.ToString()) == 1)
{
isClientUse = true;
}
}
catch (Exception ex)
{
Program.Log("当前文件名:" + kvp.Key.ToString());
Program.Log("当前字段名:" + name);
Program.Log(ex.ToString());
return;
}
}
}
else if (fieldName == "备注")
{
comment = value.ToString();
}
else if (fieldName == "主键")
{
if (value.ToString() != "")
{
if (key1 == "")
{
key1 = name;
}
else if (key2 == "")
{
key2 = name;
}
}
}
}
if (!isClientUse)
{
continue;
}
XmlElement field = xmlDoc.CreateElement("Field");
field.SetAttribute("Name", name);
field.SetAttribute("XmlName", name);
field.SetAttribute("Type", type);
field.SetAttribute("Default", "");
field.SetAttribute("Comment", comment);
data.AppendChild(field);
}
data.SetAttribute("Name", kvp.Key.ToString());
data.SetAttribute("Comment", "");
if (key2 != "")
{
data.SetAttribute("Type", "DictionaryEx");
}
else
{
data.SetAttribute("Type", "Dictionary");
}
data.SetAttribute("ItemName", "root/content");
data.SetAttribute("DataPath", kvp.Key.ToString() + ".xml");
data.SetAttribute("KeyName", key1);
if (key2 != "")
{
data.SetAttribute("Key2Name", key2);
}
data.SetAttribute("CfgType", "DB");
xml.AppendChild(data);
}
string outfile = options.OutFilePath + "\\DataPoolDefine.xml";
xmlDoc.Save(outfile);
Program.Log("输出成功!路径:" + outfile);
}
static void RenameImg()
{
imgNamesDic.Clear();
string excelPath = EditorUtility.OpenFilePanel("打开 excel需求表 ", Application.dataPath, "*");
if (string.IsNullOrEmpty(excelPath))
{
Debug.LogError("excel 路径不存在");
return;
}
Debug.Log(excelPath);
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.ReadWrite))
{
Debug.Log(stream == null);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//exel需要放在Assets目录下
Debug.Log(excelReader.IsValid + " " + excelReader.ExceptionMessage);
DataSet result = excelReader.AsDataSet();
Debug.Log(result == null);
int columns = result.Tables[0].Columns.Count;
int rows = result.Tables[0].Rows.Count;
Debug.LogError(columns + " " + rows);
for (int i = 1; i < rows; i++)
{
string zh_cn = result.Tables[0].Rows[i][5].ToString();
string en = result.Tables[0].Rows[i][6].ToString();
string[] zhStr = zh_cn.Split('-');
zh_cn = zhStr.Length > 1 ? zhStr[1] : zhStr[0];
zh_cn = Regex_ZhCN(zh_cn);
zh_cn = zh_cn.Contains("邮轮") ? zh_cn.Replace("邮轮", "") : zh_cn;
imgNamesDic.Add(zh_cn, en);
Debug.Log($"zh:={zh_cn}, en={en}");
}
}
if (imgNamesDic.Count == 0)
{
Debug.LogError("先从excel表中获取名字");
return;;
}
string dirPath = EditorUtility.OpenFolderPanel("打开缩略图目录 ", Application.dataPath, "缩略图目录");
if (string.IsNullOrEmpty(dirPath))
{
Debug.LogError("excel 路径不存在");
return;
}
Debug.Log(dirPath);
string[] imgPaths = Directory.GetFiles(dirPath, "*.png");
for (int i = 0; i < imgPaths.Length; i++)
{
string item = imgPaths[i];
string oldfileName = Path.GetFileNameWithoutExtension(item);
Debug.Log("oldfileName " + oldfileName);
if (!imgNamesDic.ContainsKey(oldfileName))
{
Debug.Log("没有找到文件");
continue;
}
string newFileName = imgNamesDic[oldfileName];
string newFileFullName = Path.Combine(dirPath, newFileName);
item = item.Replace('\\', '/');
newFileFullName = newFileFullName.Replace('\\', '/') + ".png";
Debug.Log($"旧文件名字：{item}, 新文件名字： {newFileFullName}");
File.Move(item, newFileFullName);
}
}
public async Task <ActionResult> CreateMultiple(HttpPostedFileBase uploadfile)
{
if (ModelState.IsValid)
{
if (uploadfile != null && uploadfile.ContentLength > 0)
{
//ExcelDataReader works on binary excel file
Stream stream = uploadfile.InputStream;
//We need to written the Interface.
IExcelDataReader reader = null;
if (uploadfile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (uploadfile.FileName.EndsWith(".xlsx"))
{
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//Shows error if uploaded file is not Excel file
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
//First row iss treated as the header
UseHeaderRow = true
}
});
var dataTable = result.Tables[0];
List <string> EmailList = new List <string>();
//Read each row one by one
for (var i = 0; i < dataTable.Rows.Count; i++)
{
var FName = dataTable.Rows[i][0].ToString().Trim(); //First Name
var MName = dataTable.Rows[i][1].ToString().Trim(); //Middle Name
var LName = dataTable.Rows[i][2].ToString().Trim(); //Last Name
var StudNum = dataTable.Rows[i][3].ToString().Trim(); //Student Number
var Program = dataTable.Rows[i][4].ToString().Trim(); //Program
var YearLvl = dataTable.Rows[i][5].ToString().Trim(); //Year Level
var Email = dataTable.Rows[i][6].ToString().Trim(); //Email & Username
var isActive = dataTable.Rows[i][7].ToString().Trim(); //Active Bool
var Role = dataTable.Rows[i][8].ToString().Trim(); //Role
//Add each student's email to the email list
EmailList.Add(Email);
//Call the user making method here
AddUser(FName, MName, LName, StudNum, Program, YearLvl, Email, isActive, Role);
}
//Once each student is registered, send them an email
//This launches the SendEmail in a separate thread in the background
var tEmail = new Thread(() => SendEmail(EmailList));
tEmail.Start();
reader.Close();
//Sending result data to View
return(View(result.Tables[0]));
}
}
else
{
ModelState.AddModelError("File", "Please upload your file");
}
return(View());
}
// Parse results
public static List<string> ParseResults(IExcelDataReader excelReader)
{
var list = new List<string>();
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
if (result.Tables.Count > 0)
{
var dt = result.Tables[0];
foreach (DataRow r in dt.Rows)
{
list.Add(r[0].ToString());
}
}
// Return list
return list;
}
private Hash DoCompilerExcelReader(string path, IExcelDataReader excelReader, string compileToFilePath = null)
{
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
//DataSet result = excelReader.AsDataSet();
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
if (result.Tables.Count <= 0)
throw new InvalidExcelException("No Sheet!");
var renderVars = new RenderTemplateVars();
renderVars.FieldsInternal = new List<RenderFieldVars>();
var sheet1 = result.Tables[0];
var strBuilder = new StringBuilder();
var ignoreColumns = new HashSet<int>();
var ignoreRows = new HashSet<int>();
// 寻找注释行，1,或2行
var hasStatementRow = false;
var statementRow = sheet1.Rows[0].ItemArray;
var regExCheckStatement = new Regex(@"\[(.*)\]");
foreach (var cellVal in statementRow)
{
if ((cellVal is string))
{
var matches = regExCheckStatement.Matches(cellVal.ToString());
if (matches.Count > 0)
{
hasStatementRow = true;
}
}
break;
}
// 获取注释行
var commentRow = hasStatementRow ? sheet1.Rows[1].ItemArray : sheet1.Rows[0].ItemArray;
var commentsOfColumns = new List<string>();
foreach (var cellVal in commentRow)
{
commentsOfColumns.Add(cellVal.ToString());
}
// Header
int colIndex = 0;
foreach (DataColumn column in sheet1.Columns)
{
var colNameStr = column.ColumnName.Trim();
if (!string.IsNullOrEmpty(colNameStr))
{
var isCommentColumn = false;
foreach (var commentStartsWith in _config.CommentColumnStartsWith)
{
if (colNameStr.StartsWith(commentStartsWith))
{
isCommentColumn = true;
break;
}
}
if (isCommentColumn)
{
ignoreColumns.Add(colIndex);
}
else
{
if (colIndex > 0)
strBuilder.Append("\t");
strBuilder.Append(colNameStr);
string typeName = "string";
string defaultVal = "";
if (hasStatementRow)
{
var match = regExCheckStatement.Match(statementRow[colIndex].ToString());
var attrs = match.Groups[1].ToString().Split(':');
// Type
if (attrs.Length > 0)
{
typeName = attrs[0];
}
// Default Value
if (attrs.Length > 1)
{
defaultVal = attrs[1];
}
if (attrs.Length > 2)
{
if (attrs[2] == "pk")
{
renderVars.PrimaryKey = colNameStr;
}
}
}
renderVars.FieldsInternal.Add(new RenderFieldVars
{
Index = colIndex,
Type = typeName,
Name = colNameStr,
DefaultValue = defaultVal,
Comment = commentsOfColumns[colIndex],
});
//codeGentor.Columns2DefaultValus.Add(colNameStr, defaultVal);
}
}
colIndex++;
}
strBuilder.Append("\n");
// Rows
var rowIndex = 1;
foreach (DataRow dRow in sheet1.Rows)
{
if (hasStatementRow)
{
// 有声明行，忽略第2行
if (rowIndex == 2)
{
rowIndex++;
continue;
}
}
else
{
// 无声明行，忽略第1行
if (rowIndex == 1)
{
rowIndex++;
continue;
}
}
colIndex = 0;
foreach (var item in dRow.ItemArray)
{
if (ignoreColumns.Contains(colIndex)) // comment column, ignore
continue;
if (colIndex > 0)
strBuilder.Append("\t");
var cloneItem = item;
// 如果单元格是字符串，换行符改成\\n
if (item is string)
{
var sItme = item as string;
cloneItem = sItme.Replace("\n", "\\n");
}
strBuilder.Append(cloneItem);
colIndex++;
}
strBuilder.Append("\n");
rowIndex++;
}
var fileName = Path.GetFileNameWithoutExtension(path);
string exportPath;
if (!string.IsNullOrEmpty(compileToFilePath))
{
exportPath = compileToFilePath;
}
else
{
// use default
exportPath = string.Format("{0}{1}", fileName, _config.ExportTabExt);
}
File.WriteAllText(exportPath, strBuilder.ToString());
renderVars.ClassName = string.Join("", (from name in fileName.Split('_') select System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(name)).ToArray());
renderVars.TabFilePath = exportPath;
return Hash.FromAnonymousObject(renderVars);
}
public uc_autoprocess(
System.Int32 aRows,
System.Int32 aColumns,
TermWindow p1
)
{
this.rRows = aRows;
this.rColumns = aColumns;
this.Parent = p1;
mySB = new System.Text.StringBuilder(this.rRows * this.rColumns);
this.SetSequence();
myMapTxtCaret = new uc_maptxtcaret(this);
stream = File.Open(@"c:\temp\temp.xlsx", FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
output = excelReader.AsDataSet();
ddtt = output.Tables[0];
numCount = ddtt.Rows.Count;
shouldIncExcelPatientCntr = false;
}
private static DataTable GetDataTableFromExcelReader(IExcelDataReader excelReader)
{
var dt = excelReader.AsDataSet().Tables[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns[i].ColumnName = String.IsNullOrEmpty(dt.Rows[0][i].ToString()) ? dt.Columns[i].ColumnName : dt.Rows[0][i].ToString();
}
return dt;
}
/// <summary>
/// Data the formatting and validations.
/// </summary>
/// <param name="uploadFile">The upload file.</param>
/// <param name="excelReader">The excel reader.</param>
/// <param name="visitorPresenter">The visitor presenter.</param>
/// <returns>
/// Import Details for Data provided.
/// </returns>
private static ImportDetails DataFormattingAndValidations(HttpPostedFileBase uploadFile, ref IExcelDataReader excelReader, VisitorPresenter visitorPresenter)
{
var importedFileData = new ImportDetails
{
FileName = uploadFile.FileName,
FileModifiedDate = BaseController.GenerateLocalDateTime(DateTime.UtcNow)
};
try
{
if (uploadFile.FileName.EndsWith(ExcelLatestExtension, StringComparison.OrdinalIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(uploadFile.InputStream);
}
else if (uploadFile.FileName.EndsWith(ExcelNormalExtension, StringComparison.OrdinalIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(uploadFile.InputStream);
}
using (var result = excelReader.AsDataSet())
{
result.Locale = CultureInfo.CurrentCulture;
importedFileData = ConvertToCSV(result, importedFileData);
excelReader.Close();
}
if (string.IsNullOrEmpty(importedFileData.VisitorDetail))
{
visitorPresenter.IsImportFailed = true;
visitorPresenter.ImportException = string.Format(CultureInfo.CurrentCulture, "{0}{1}.", Resource.ShipNameNotCorrect, SessionData.Instance.MasterData.Ship.Name);
}
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(ex.Message))
{
visitorPresenter.IsImportFailed = true;
visitorPresenter.ImportException = Resource.VisitorImportException;
}
}
importedFileData.RejectedReason = visitorPresenter.ImportException;
return importedFileData;
}
static void Excel2Txt()
{
string excelPath = EditorUtility.OpenFilePanel("打开 excel需求表 ", Application.dataPath, "*");
if (string.IsNullOrEmpty(excelPath))
{
Debug.LogError("excel 路径不存在");
return;
}
Debug.Log(excelPath);
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.ReadWrite))
{
Debug.Log(stream == null);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//exel需要放在Assets目录下
Debug.Log(excelReader.IsValid + " " + excelReader.ExceptionMessage);
DataSet result = excelReader.AsDataSet();
Debug.Log(result == null);
int columns = result.Tables[0].Columns.Count;
int rows = result.Tables[0].Rows.Count;
Debug.LogError(columns + " " + rows);
string txt_path = EditorUtility.SaveFolderPanel(" txt保存目录 ", Application.dataPath, " 保存目录");
Debug.Log(txt_path);
if (string.IsNullOrEmpty(txt_path))
{
Debug.LogError("txt 路径不存在");
return;
}
for (int i = 0; i < rows; i++)
{
string str = string.Empty;
for (int j = 0; j < columns; j++)
{
str += result.Tables[0].Rows[i][j] + $"-{j} ";
}
Debug.Log(i + " + " + str);
}
string txtPath = txt_path + "/" + "花艺.txt"; //6，4
txtPath = txtPath.Replace("/", "\\").Replace("\n", ""); //路径替换
using (FileStream _stream = new FileStream(txtPath, FileMode.OpenOrCreate, FileAccess.Write))
{
//return;
for (int i = 1; i < rows; i++)
{
//ModelInfo theme = new ModelInfo();
//theme.name = result.Tables[0].Rows[i][0].ToString();
//theme.path = result.Tables[0].Rows[i][1].ToString();
//theme.type = result.Tables[0].Rows[i][2].ToString();
//theme.thumbnail = result.Tables[0].Rows[i][3].ToString();
//theme.load = result.Tables[0].Rows[i][4].ToString();
//m_allModelsInfo.Add(theme);
//string txtPath = txt_path + "/" + (result.Tables[0].Rows[i][6]).ToString() + ".txt";//6，4
//txtPath = txtPath.Replace("/", "\\").Replace("\n", "");//路径替换
//Debug.Log(txtPath + " txt文件目录");
//string _name = "name:" + result.Tables[0].Rows[i][7].ToString() + "\n";//7
//string _content = "content:" + result.Tables[0].Rows[i][8].ToString();//8,6
//_content = Regex.Replace(_content, @"\s", "");
//string str = (_name + _content).Trim();
string str = result.Tables[0].Rows[i][1].ToString() + "\n";
//str = str.Trim();
//Debug.Log(" StreamWriter " + str);
StreamWriter sw = new StreamWriter(_stream, Encoding.UTF8);
sw.Write(str);
sw.Flush();
}
_stream.Flush();
}
//stream.Flush();
// stream.Close();
}
}
private static List<Schema> ReadSchemas(IExcelDataReader excelReader)
{
var schemas = new List<Schema>();
var excelDs = excelReader.AsDataSet();
var tocDt = excelDs.Tables["TOC"];
var tableNames = tocDt.ValueOf<string>(0);
foreach (var tableName in tableNames)
{
var schemaDt = excelDs.Tables[tableName + "_schema"];
if (schemaDt == null)
{
continue;
}
var schema = new Schema(tableName, schemaDt);
var dt = excelDs.Tables[schema.TableName + "_data"];
schema.DataTable = dt;
schemas.Add(schema);
}
return schemas;
}
public void OpenFile(string fileName)
{
/* Requirement: files with extensions .xls, .xlsx, and .csv must have matching content */
/* Alert the user and return if the fileName is not valid */
if (string.IsNullOrWhiteSpace(fileName) || !File.Exists(fileName))
{
MessageBox.Show(fileName, "File Not Found Or Not Accessible");
return;
}
try
{
_fileStream = File.Open(fileName, FileMode.Open, FileAccess.Read);
/* Open the file with the appropriate method based on the file's extension */
_excelDataReader = default;
switch (Path.GetExtension(fileName).ToLower())
{
case (".xls"):
_excelDataReader = ExcelReaderFactory.CreateBinaryReader(_fileStream);
break;
case (".xlsx"):
_excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(_fileStream);
break;
case (".csv"):
_excelDataReader = ExcelReaderFactory.CreateCsvReader(_fileStream);
break;
default:
_excelDataReader = ExcelReaderFactory.CreateReader(_fileStream);
break;
}
/* Read data from the entire spreadsheet */
_data = _excelDataReader?.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = reader => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
}
catch (UnauthorizedAccessException)
{
MessageBox.Show($"The file could not be accessed, possibly because permissions on the file do not allow it:\n\n{fileName}", "Unable to Open File");
}
catch (DirectoryNotFoundException)
{
MessageBox.Show($"The folder the file is located in cannot be found, possibly because the location is no longer accessible:\n\n{fileName}", "Unable to Open File");
}
catch (FileNotFoundException)
{
MessageBox.Show($"The file could not be found:\n\n{fileName}", "Unable to Open File");
}
catch (IOException)
{
MessageBox.Show($"The file could not be opened, possibly because it is open in another program:\n\n{fileName}", "Unable to Open File");
}
}
private DataSet ConvertExcelToDataSet(IExcelDataReader parsedExcel)
{
DataSet result = parsedExcel.AsDataSet();
return result;
}
private DataSet ConvertStreamToDataSet(Stream inputStream)
{
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(inputStream);
return(reader.AsDataSet());
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(2, excelReader.GetDouble(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetDouble(0));
Assert.AreEqual(2, excelReader.GetDouble(1));
Assert.AreEqual(3, excelReader.GetDouble(2));
Assert.AreEqual(4, excelReader.GetDouble(3));
Assert.AreEqual(5, excelReader.GetDouble(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetDouble(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetDouble(0));
Assert.AreEqual(i + 2, excelReader.GetDouble(1));
Assert.AreEqual(i + 3, excelReader.GetDouble(2));
Assert.AreEqual(i + 4, excelReader.GetDouble(3));
Assert.AreEqual(i + 5, excelReader.GetDouble(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
// test dataset
DataSet result = excelReader.AsDataSet(Configuration.FirstRowColumnNamesConfiguration);
Assert.AreEqual(3, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
// Test default and overridden column name prefix
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
DataSet prefixedResult = excelReader.AsDataSet(Configuration.FirstRowColumnNamesPrefixConfiguration);
Assert.AreEqual("column a", prefixedResult.Tables[0].Columns[0].ColumnName);
Assert.AreEqual("Prefix3", prefixedResult.Tables[0].Columns[3].ColumnName);
}
private List <TeamMember> ExcelParser(string path, string name, Invoice invoice, int garageID)
{
System.Diagnostics.Debug.WriteLine("begin excel parser");
IExcelDataReader reader = null;
//Trace.WriteLine(garageID);
var excelData = new ExcelData(path);
reader = excelData.getExcelReader(name);
//Trace.WriteLine(invoice.InvoiceID);
/*if (reader == null)
* {
* System.Diagnostics.Debug.WriteLine("excel reader null");
* }*/
// Create column names from first row
reader.IsFirstRowAsColumnNames = true;
// The result of each spreadsheet will be created in the result.Tables
DataSet result = reader.AsDataSet();
List <TeamMember> teamMembers = new List <TeamMember>();
System.Diagnostics.Debug.WriteLine("begin for loop");
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Rows)
{
// if this row is the headings, skip this row
System.Diagnostics.Debug.WriteLine("loop");
if (row == null)
{
System.Diagnostics.Debug.WriteLine("null");
continue;
}
if (row[0] is String)
{
System.Diagnostics.Debug.WriteLine(row[0]);
continue;
}
TeamMember teamMember = new TeamMember();
string firstName, lastName;
if (garageID == 2)
{
var first = row[invoiceSplitNameColumns[garageID].first];
var last = row[invoiceSplitNameColumns[garageID].last];
if (first == null)
{
firstName = "";
}
else if (last == null)
{
lastName = "";
}
firstName = (string)first;
lastName = (string)last;
}
else
{
string fullName = (string)row[invoiceNameColumns[garageID]];
string[] names = fullName.Split(','); //split the name
if (names.Length < 2)
{
firstName = names[0];
lastName = "";
}
else
{
firstName = names[1];
lastName = names[0];
}
}
int index = invoiceTokenColumns[garageID];
var cardNumberV = row[index];
Trace.WriteLine(cardNumberV.GetType());
double cardNumberD;
int cardNumber;
if (cardNumberV != DBNull.Value)
{
cardNumberD = (System.Double)cardNumberV;
cardNumber = Convert.ToInt32(cardNumberD);
teamMember.BusinessHoursTokenID = cardNumber;
}
else
{
teamMember.BusinessHoursTokenID = null;
}
teamMember.FirstName = firstName;
teamMember.LastName = lastName;
teamMembers.Add(teamMember);
db.TeamMembers.Add(teamMember);
db.SaveChanges();
/*
* foreach (DataColumn column in table.Columns)
* {
* System.Diagnostics.Debug.WriteLine(row[column]);
* }*/
} // end for columns
} // end for tables
return(teamMembers);
}
/// <summary>
/// 根据命令行参数，执行Excel数据导出工作
/// </summary>
/// <param name="options">命令行参数</param>
private static void Run(Options options)
{
string excelPath = options.ExcelPath;
int header = options.HeaderRows;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
//-- 确定编码
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.EncodingName == options.Encoding)
{
cd = e;
break;
}
}
}
//-- 导出JSON文件
if (options.JsonPath != null && options.JsonPath.Length > 0)
{
JsonExporter exporter = new JsonExporter(sheet, header, options.Lowcase);
exporter.SaveToFile(options.JsonPath, cd, options.ExportArray);
}
//-- 导出SQL文件
if (options.SQLPath != null && options.SQLPath.Length > 0)
{
SQLExporter exporter = new SQLExporter(sheet, header);
exporter.SaveToFile(options.SQLPath, cd);
}
//-- 生成C#定义文件
if (options.CSharpPath != null && options.CSharpPath.Length > 0)
{
string excelName = Path.GetFileName(excelPath);
CSDefineGenerator exporter = new CSDefineGenerator(sheet);
exporter.ClassComment = string.Format("// Generate From {0}", excelName);
exporter.SaveToFile(options.CSharpPath, cd);
}
}
}
public static bool LoadFile(string filePath, ref List <DataType> listItems)
{
if (!File.Exists(filePath))
{
return(false);
}
FileInfo fi = new FileInfo(filePath);
if (null != listItems)
{
listItems.Clear();
}
else
{
listItems = new List <DataType>();
}
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
IExcelDataReader reader = null;
if (".xls" == fi.Extension)
{
reader = ExcelReaderFactory.CreateBinaryReader(fs);
}
else if (".xlsx" == fi.Extension)
{
reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
}
// no valid reader created -> exit
if (reader == null)
{
return(false);
}
reader.IsFirstRowAsColumnNames = true;
DataSet ds = reader.AsDataSet();
foreach (DataTable dtTable in ds.Tables)
{
int iRowStart = 0;
for (int iRow = iRowStart; iRow < dtTable.Rows.Count; ++iRow)
{
DataType dataType = null;
try
{
dataType = BuildDataType(dtTable.TableName, iRow, dtTable.Rows[iRow]);
}
catch (InvalidRowException /*ex*/)
{
break;
}
catch (Exception ex)
{
_log.Error(string.Format("Failed to read {0}({1}) with message : {2}", dtTable.TableName, iRow, ex.Message));
dataType = null;
break;
}
if (null != dataType)
{
listItems.Add(dataType);
}
}
}
}
return(listItems.Count > 0);
}
public ActionResult Crear(HttpPostedFileBase upload)
{
if (Session["StringToken"] == null)
{
return(RedirectToAction("Index", "Home"));
}
tkn = Session["StringToken"].ToString();
listPreCarga = (List <HistoricoVentaModel>)TempData["HistoricoVentas"];
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
List <HistoricoVentaModel> listaHistorico = new List <HistoricoVentaModel>();
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Rows)
{
listaHistorico.Add(new HistoricoVentaModel
{
Mes = Convert.ToInt16(row.ItemArray[0]),
Anio = Convert.ToInt16(row.ItemArray[1]),
MontoVenta = Convert.ToDouble(row.ItemArray[2]),
EsPipa = Convert.ToBoolean(row.ItemArray[3]),
EsCamioneta = Convert.ToBoolean(row.ItemArray[4]),
EsLocal = Convert.ToBoolean(row.ItemArray[5])
});
}
}
reader.Close();
var respuesta = HistoricoServicio.GuardarNuevoHistorico(listaHistorico, tkn);
TempData["RespuestaDTO"] = respuesta;
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
if (listPreCarga != null || listPreCarga.Count > 0)
{
var respuesta = HistoricoServicio.GuardarNuevoHistorico(listPreCarga, tkn);
TempData["RespuestaDTO"] = respuesta;
listPreCarga = null;
}
}
}
return(RedirectToAction("Index"));
//return RedirectToAction("Index");
}
public static List <ImporterResult> ImportEnsayosStep2() //Crea tabla temporal e importar datos
{
List <ImporterResult> errors = new List <ImporterResult>();
int count = 0;
int rowCount = 0;
int tableCount = 0;
int columnCount = 0;
IExcelDataReader excelReader = null;
try
{
string sourceDirectoryPath = Configuration.ImportersPath;
DirectoryInfo di = new DirectoryInfo(sourceDirectoryPath);
FileInfo[] files = di.GetFiles("*.xls*", SearchOption.TopDirectoryOnly);
DataTable sourceTable = new DataTable();
sourceTable.Columns.Add(new DataColumn("Fuente", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Provincia", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Localidad", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Mal", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Establecimiento", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Campana", typeof(string)));
sourceTable.Columns.Add(new DataColumn("FechaSiembra", typeof(DateTime)));
sourceTable.Columns.Add(new DataColumn("FechaCosecha", typeof(DateTime)));
sourceTable.Columns.Add(new DataColumn("Producto", typeof(string)));
sourceTable.Columns.Add(new DataColumn("ColumnaVacia", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Rinde", typeof(float)));
sourceTable.Columns.Add(new DataColumn("Indice", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Quebrado", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Vuelco", typeof(string)));
sourceTable.Columns.Add(new DataColumn("AlturaPlanta", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Humedad", typeof(string)));
sourceTable.Columns.Add(new DataColumn("EsPromedio", typeof(string)));
sourceTable.Columns.Add(new DataColumn("PlantasXHectarea", typeof(string)));
sourceTable.Columns.Add(new DataColumn("DiasFloracion", typeof(string)));
sourceTable.Columns.Add(new DataColumn("EmergenciaFloracion", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Observaciones", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Archivo", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Row", typeof(string)));
foreach (FileInfo info in files)
{
FileStream stream = File.Open(info.FullName, FileMode.Open, FileAccess.Read);
if (info.Extension.Equals(".xls"))
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (info.Extension.Equals(".xlsx"))
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
excelReader.IsFirstRowAsColumnNames = false;
DataSet xlsResults = excelReader.AsDataSet();
for (int t = 0; t < xlsResults.Tables.Count; t++)
{
tableCount = t;
for (int r = 1; r < xlsResults.Tables[t].Rows.Count; r++)
{
rowCount = r + 1;
DataRow row = sourceTable.NewRow();
for (int c = columnCount; c <= 21; c++)
{
if (c == 6 || c == 7) //Si viene con formato fecha en excel, lo cambio
{
string stringDate = (xlsResults.Tables[t].Rows[r][c]).ToString();
if (!string.IsNullOrEmpty(stringDate))
{
double doubleDate = double.Parse(stringDate);
row[c] = DateTime.FromOADate(doubleDate);
}
else
{
row[c] = xlsResults.Tables[t].Rows[r][c];
}
}
else
{
row[c] = xlsResults.Tables[t].Rows[r][c];
}
}
row[22] = rowCount;
sourceTable.Rows.Add(row);
}
}
}
// new method: SQLBulkCopy:
using (SqlConnection conn = new SqlConnection(Configuration.ConnectionString))
{
conn.Open();
using (SqlBulkCopy s = new SqlBulkCopy(conn))
{
s.DestinationTableName = "temp_ensayos";
s.BulkCopyTimeout = 120;
s.WriteToServer(sourceTable);
s.Close();
}
conn.Close();
}
}
catch (Exception ex)
{
count++;
ImporterResult error = new ImporterResult
{
Description = string.Format("Error: {0}. Fila {1}, Columna {2}", ex.Message, rowCount, columnCount),
TipoError = TipoError.FormatoIncorrecto,
Severity = Severity.Error,
};
errors.Add(error);
}
finally
{
//7. Free resources (IExcelDataReader is IDisposable)
if (excelReader != null)
{
excelReader.Close();
}
}
return(errors);
}
public void Open()
{
FileStream fs = new FileStream(m_kartotek_dat, FileMode.Open, FileAccess.Read, FileShare.None);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(fs);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
excelReader.Close();
System.Data.DataTable tbl = result.Tables["MedlemEkstern"];
if (tbl != null)
{
DataRowCollection recs = tbl.Rows;
DataColumnCollection cols = tbl.Columns;
int maxcol = cols.Count;
foreach (DataRow rec in recs)
{
recImportMedlem r = new recImportMedlem();
int NotEmptyCount = 0;
for (int i = 0; i < maxcol; i++)
{
string Name = cols[i].ColumnName;
switch (Name)
{
case "Nr":
try
{
r.Nr = int.Parse(rec.ItemArray[i].ToString());
NotEmptyCount++;
}
catch
{
r.Nr = null;
}
break;
case "Navn":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Navn = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Navn = null;
}
break;
case "Kaldenavn":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Kaldenavn = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Kaldenavn = null;
}
break;
case "Adresse":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Adresse = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Adresse = null;
}
break;
case "Postnr":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Postnr = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Postnr = null;
}
break;
case "By":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Bynavn = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Bynavn = null;
}
break;
case "Email":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Email = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Email = null;
}
break;
case "Telefon":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Telefon = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.Telefon = null;
}
break;
case "Køn":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.Kon = rec.ItemArray[i].ToString().ToUpper();
if ((r.Kon != "M") && (r.Kon != "K"))
{
r.Kon = null;
}
else
{
NotEmptyCount++;
}
}
else
{
r.Kon = null;
}
break;
case "Født":
try
{
r.FodtDato = clsUtil.MSSerial2DateTime(Double.Parse(rec.ItemArray[i].ToString()));
NotEmptyCount++;
}
catch
{
r.FodtDato = null;
}
break;
case "Status":
if (rec.ItemArray[i].ToString().Length > 0)
{
r.stStatus = rec.ItemArray[i].ToString();
NotEmptyCount++;
}
else
{
r.stStatus = null;
}
break;
default:
break;
}
}
if ((NotEmptyCount > 0) && (r.stStatus == null))
{
this.Add(r);
}
}
}
}
public ActionResult Import_Post(HttpPostedFileBase upload, ImportExcel entobj, string AMCCode)
{
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
string tempamccode = Convert.ToString(entobj.AMCID.Split('$')[1]);
string invalidfilemsg = "Invalid file formate for";
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View("Import", entobj));
}
reader.IsFirstRowAsColumnNames = true;
DataSet result = reader.AsDataSet();
reader.Close();
if (tempamccode.ToUpper() == "CAMS") //CAMS
{
if (result.Tables[0].Columns[2].ColumnName.ToLower() != "folio_no" || result.Tables[0].Columns[38].ColumnName.ToLower() != "brkage_amt")
{
ModelState.AddModelError("File", invalidfilemsg + " CAMS!!");
return(View("Import", entobj));
}
}
else if (tempamccode.ToUpper() == "KARVY")//Karvy
{
//if (result.Tables[0].Columns[2].ColumnName.ToLower() != "investor name" || result.Tables[0].Columns[16].ColumnName.ToLower() != "gross brokerage")
if (result.Tables[0].Columns[1].ColumnName.ToLower() != "account number" || result.Tables[0].Columns[16].ColumnName.ToLower() != "gross brokerage")
{
ModelState.AddModelError("File", invalidfilemsg + " KARVY!!");
return(View("Import", entobj));
}
}
else if (tempamccode.ToUpper() == "FRANKLINE")//Frankline
{
if (result.Tables[0].Rows[1][3].ToString().ToLower() != "accountno" || result.Tables[0].Rows[1][18].ToString().ToLower() != "brokerage")
{
ModelState.AddModelError("File", invalidfilemsg + " FRANKLINE!!");
return(View("Import", entobj));
}
}
ImportExcelDAL objdal = new ImportExcelDAL();
objdal.InsertUpdate(result.Tables[0], tempamccode, entobj, 0);
return(RedirectToAction("Create", "ImportExcel", new { calltype = 1 }));
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
return(View("Import", entobj));
}
}
return(View("Create", new { calltype = 0 }));
}
public static void ReadConfig()
{
MG_Config _Dice_Config = Resources.Load <ScriptableObject>("MG_ConfigAssets/MG_Dice_Config") as MG_Config;
_Dice_Config.MG_Dice_CashRange.Clear();
_Dice_Config.MG_Dice_ExtraBonusConfigs.Clear();
_Dice_Config.MG_Dice_SpecialPropsConfigs.Clear();
_Dice_Config.MG_Dice_JackpotConfigs.Clear();
_Dice_Config.MG_Dice_BricksConfigs.Clear();
_Dice_Config.MG_Scratch_Configs.Clear();
_Dice_Config.MG_Slots_Configs.Clear();
_Dice_Config.MG_Wheel_Configs.Clear();
XlsxPath = Application.dataPath + "/MiddleGround/MG_Config.xlsx";
if (!File.Exists(XlsxPath))
{
Debug.LogError("Read MG_ConfigXlsx File Error : file is not exist.");
return;
}
FileStream fs = new FileStream(XlsxPath, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet dataSet = reader.AsDataSet();
reader.Dispose();
if (dataSet is null)
{
Debug.LogError("Read MG_ConfigXlsx File Error : file is empty.");
return;
}
#region zeroTable
DataTable zeroTable = dataSet.Tables[0];
int rowsCount0 = zeroTable.Rows.Count;
for (int i = 1; i < rowsCount0; i++)
{
var tempRow = zeroTable.Rows[i];
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
continue;
}
MG_Dice_BrickConfig _Dice_BrickConfig = new MG_Dice_BrickConfig()
{
brickType = (MG_Dice_BrickType)System.Enum.Parse(typeof(MG_Dice_BrickType), tempRow[0].ToString()),
minRandomNum = int.Parse(tempRow[1].ToString()),
maxRandomNum = int.Parse(tempRow[2].ToString()),
weight = int.Parse(tempRow[3].ToString()),
maxCanGetValue = int.Parse(tempRow[4].ToString())
};
_Dice_Config.MG_Dice_BricksConfigs.Add(_Dice_BrickConfig);
}
#endregion
#region firstTable
DataTable firstTable = dataSet.Tables[1];
int rowsCount1 = firstTable.Rows.Count;
for (int i = 2; i < rowsCount1; i++)
{
var tempRow = firstTable.Rows[i];
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
continue;
}
//范围
_Dice_Config.MG_Dice_CashRange.Add(int.Parse(tempRow[1].ToString()) * 100);
int configIndex = i - 2;
MG_Dice_ExtraBonusConfig extraBonusConfig = new MG_Dice_ExtraBonusConfig
{
rangeIndex = configIndex,
needTargetStep = int.Parse(tempRow[2].ToString()),
cashBonusRate = float.Parse(tempRow[3].ToString()),
minCashBonus = int.Parse(tempRow[4].ToString()),
maxCashBonus = int.Parse(tempRow[5].ToString()),
goldBonusRate = float.Parse(tempRow[6].ToString()),
minGoldBonus = int.Parse(tempRow[7].ToString()),
maxGoldBonus = int.Parse(tempRow[8].ToString()),
};
_Dice_Config.MG_Dice_ExtraBonusConfigs.Add(extraBonusConfig);
MG_Dice_SpecialPropsConfig specialPropsConfig = new MG_Dice_SpecialPropsConfig
{
rangeIndex = configIndex,
minCashReward = int.Parse(tempRow[9].ToString()),
maxCashReward = int.Parse(tempRow[10].ToString()),
minGoldReward = int.Parse(tempRow[12].ToString()),
maxGoldReward = int.Parse(tempRow[13].ToString())
};
specialPropsConfig.cashMutiple = new List <float>();
string[] cashmutiples = tempRow[11].ToString().Split(';');
for (int j = 0; j < cashmutiples.Length; j++)
{
specialPropsConfig.cashMutiple.Add(float.Parse(cashmutiples[j]));
}
specialPropsConfig.goldMutiple = new List <float>();
string[] goldmutiples = tempRow[14].ToString().Split(';');
for (int j = 0; j < goldmutiples.Length; j++)
{
specialPropsConfig.goldMutiple.Add(float.Parse(goldmutiples[j]));
}
_Dice_Config.MG_Dice_SpecialPropsConfigs.Add(specialPropsConfig);
MG_Dice_JackpotConfig jackpotConfig = new MG_Dice_JackpotConfig()
{
noRewardRate = float.Parse(tempRow[15].ToString()),
cashRewardRate = float.Parse(tempRow[16].ToString()),
goldRewardRate = float.Parse(tempRow[18].ToString())
};
jackpotConfig.cashPool = new List <int>();
string[] cashNums = tempRow[17].ToString().Split(';');
for (int j = 0; j < cashNums.Length; j++)
{
jackpotConfig.cashPool.Add(int.Parse(cashNums[j]));
}
jackpotConfig.goldPool = new List <int>();
string[] goldNums = tempRow[19].ToString().Split(';');
for (int j = 0; j < goldNums.Length; j++)
{
jackpotConfig.goldPool.Add(int.Parse(goldNums[j]));
}
jackpotConfig.mutiplePool = new List <float>();
string[] mutipleNums = tempRow[20].ToString().Split(';');
for (int j = 0; j < mutipleNums.Length; j++)
{
jackpotConfig.mutiplePool.Add(float.Parse(mutipleNums[j]));
}
_Dice_Config.MG_Dice_JackpotConfigs.Add(jackpotConfig);
}
#endregion
#region secondTable
DataTable secondTable = dataSet.Tables[2];
int rowCount2 = secondTable.Rows.Count;
for (int i = 1; i < rowCount2; i++)
{
var tempRow = secondTable.Rows[i];
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
continue;
}
MG_Scratch_Config _Scratch_Config = new MG_Scratch_Config()
{
RnageCashStart = int.Parse(tempRow[1].ToString()) * 100,
minScratchTimes = int.Parse(tempRow[2].ToString()),
maxScratchTimes = int.Parse(tempRow[3].ToString()),
minCash = int.Parse(tempRow[4].ToString()),
maxCash = int.Parse(tempRow[5].ToString()),
RnageSssStart = int.Parse(tempRow[6].ToString()),
sssWeight = int.Parse(tempRow[7].ToString()),
goldWeight = int.Parse(tempRow[8].ToString()),
minGold = int.Parse(tempRow[9].ToString()),
maxGold = int.Parse(tempRow[10].ToString())
};
_Dice_Config.MG_Scratch_Configs.Add(_Scratch_Config);
}
#endregion
#region thirdTabl
DataTable thirdTable = dataSet.Tables[3];
int rowCount3 = thirdTable.Rows.Count;
for (int i = 1; i < rowCount3; i++)
{
var tempRow = thirdTable.Rows[i];
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
continue;
}
MG_Slots_Config _Slots_Config = new MG_Slots_Config()
{
rewardType = (MG_Slots_RewardType)System.Enum.Parse(typeof(MG_Slots_RewardType), tempRow[0].ToString()),
weight = int.Parse(tempRow[1].ToString()),
rewardPercentRangeMin = float.Parse(tempRow[3].ToString()),
rewardPercentRangeMax = float.Parse(tempRow[4].ToString()),
maxCanGetValue = int.Parse(tempRow[5].ToString()),
};
if (_Slots_Config.rewardType == MG_Slots_RewardType.Cash ||
_Slots_Config.rewardType == MG_Slots_RewardType.Gift)
{
_Slots_Config.maxCanGetValue *= 100;
}
_Slots_Config.rewardThisWhereIndex = new List <int>();
string[] whereIndexs = tempRow[2].ToString().Split(',');
for (int j = 0; j < whereIndexs.Length; j++)
{
_Slots_Config.rewardThisWhereIndex.Add(int.Parse(whereIndexs[j]));
}
_Dice_Config.MG_Slots_Configs.Add(_Slots_Config);
}
#endregion
#region forthTable
DataTable forthTable = dataSet.Tables[4];
int rowCount4 = forthTable.Rows.Count;
for (int i = 1; i < rowCount4; i++)
{
var tempRow = forthTable.Rows[i];
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
continue;
}
MG_Wheel_Config _Wheel_Config = new MG_Wheel_Config()
{
rewardType = (MG_Wheel_RewardType)System.Enum.Parse(typeof(MG_Wheel_RewardType), tempRow[1].ToString()),
rewardNum = int.Parse(tempRow[2].ToString()),
weight = int.Parse(tempRow[3].ToString()),
rewardThisWhereIndex = int.Parse(tempRow[4].ToString()),
maxCanGetValue = int.Parse(tempRow[5].ToString())
};
_Dice_Config.MG_Wheel_Configs.Add(_Wheel_Config);
}
#endregion
EditorUtility.SetDirty(_Dice_Config);
AssetDatabase.SaveAssets();
}
// Add some programmatically-generated objects to the data store
// Can write one method, or many methods - your decision
// The important idea is that you check for existing data first
// Call this method from a controller action/method
public bool LoadData()
{
// User name
var user = HttpContext.Current.User.Identity.Name;
// Monitor the progress
bool done = false;
// ############################################################
// RoleClaim
if (ds.RoleClaims.Count() == 0)
{
// Add role claims
//ds.SaveChanges();
//done = true;
}
// ############################################################
// Player
// Attention - 4 - Method to load data from the XLSX file, with ExcelDataReader add-on
if (ds.Players.Count() == 0)
{
// Add players
// Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/NFLPlayoffTeams.xlsx");
// Load the workbook into a System.Data.DataSet "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
// At this point in time, sourceData holds ALL the data from the XLSX in memory
// Worksheet name
string worksheetName;
// Load the first worksheet...
// ===========================
// Get worksheet by its name
worksheetName = "DEN";
var worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
List <PlayerAdd> items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
// Fill in the team name
item.Team = worksheetName;
ds.Players.Add(Mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "CAR";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(Mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "NE";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(Mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "ARI";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(Mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
done = true;
}
return(done);
}
public void Read()
{
using (FileStream stream = File.Open(_excelFile, FileMode.Open, FileAccess.Read)) {
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
DataTable dt = result.Tables[0];
excelReader.Close();
stream.Close();
if (result.Tables.Count > 0 && dt.Rows.Count > 0)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlTransaction trx = conn.BeginTransaction();
try
{
foreach (DataRow dr in dt.Rows)
{
object year = dr["财务年"];
if (year == null || string.IsNullOrEmpty(year.ToString()))
{
continue;
}
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trx;
cmd.CommandText = string.Format(@"INSERT INTO [{7}].[STXCALENDAR]
([WHSEID]
,[FISCALYEAR]
,[QUARTER]
,[WEEK]
,[FROM_DATE]
,[TO_DATE]
,[ADDWHO]
)
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
)"
, _warehouse
, dr["财务年"]
, dr["季度"]
, dr["第几周"]
, dr["开始日期"]
, dr["结束日期"]
, _warehouse
, _warehouse
);
//Site Prime Part Alternate Part Vendor Number Allocation Percentage Start Date Active End Date Active Planner Code Non ASIC Indicator
cmd.ExecuteNonQuery();
}
trx.Commit();
conn.Close();
MailClient.SendCalendarNotificationMail(dt, _excelFile, string.Empty);
}
catch (Exception e)
{
Console.WriteLine(e);
trx.Rollback();
conn.Close();
MailClient.SendCalendarNotificationMail(dt, _excelFile, e.Message);
}
}
}
}
}
public async Task <List <ItemFilaArchivoViewModel> > RegistrarAsync(HttpPostedFileBase upload)
{
List <ItemFilaArchivoViewModel> Lista = new List <ItemFilaArchivoViewModel>();
if (upload != null && upload.ContentLength > 0)
{
try
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//ModelState.AddModelError("File", "This file format is not supported");
return(Lista);;
}
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
if (result != null && result.Tables[0].Rows.Count > 0)
{
using (DbContextTransaction transaction = _context.Database.BeginTransaction())
{
try
{
ArchivoTracking cabecera = new ArchivoTracking();
cabecera.FechaCarga = DateTime.Now;
cabecera.UsuarioCarga = "SYSTEM";
cabecera.Activo = true;
cabecera.FechaCreacion = DateTime.Now;
cabecera.UsuarioCreacion = "SYSTEM";
_context.ArchivoTrackings.Add(cabecera);
_context.SaveChanges();
int NumeroFila = 2;
foreach (var item in result.Tables[0].Rows)
{
if ((((System.Data.DataRow)item).ItemArray[0] != null) && (((System.Data.DataRow)item).ItemArray[0].ToString() != ""))
{
FilasTracking fila = new FilasTracking();
ItemFilaArchivoViewModel itemFila = new ItemFilaArchivoViewModel();
itemFila.NumFila = NumeroFila;
//int cajasP = 0;
//int cjasOrg = 0;
//int NUM_CTNRSPLAN = 0;
//int.TryParse(((System.Data.DataRow)item).ItemArray[2].ToString(), out cajasP);
//int.TryParse(((System.Data.DataRow)item).ItemArray[3].ToString(), out cjasOrg);
//int.TryParse(((System.Data.DataRow)item).ItemArray[4].ToString(), out NUM_CTNRSPLAN);
fila.IdArchivo = cabecera.IdArchivoTracking;
fila.YEAR = int.Parse(((System.Data.DataRow)item).ItemArray[0].ToString());
fila.WEEK = int.Parse(((System.Data.DataRow)item).ItemArray[1].ToString());
fila.EXPORTED_BY = ((System.Data.DataRow)item).ItemArray[2].ToString();
fila.CONTRACT_TERMS = ((System.Data.DataRow)item).ItemArray[3].ToString();
fila.INCOTERM = ((System.Data.DataRow)item).ItemArray[4].ToString();
fila.CLIENTE_FACTURA_SRI = ((System.Data.DataRow)item).ItemArray[5].ToString();
fila.CLIENTE_GRUPO = ((System.Data.DataRow)item).ItemArray[6].ToString();
fila.CONSIGNEE = ((System.Data.DataRow)item).ItemArray[7].ToString();
fila.NOTIFY = ((System.Data.DataRow)item).ItemArray[8].ToString();
fila.PAIS_DESCARGA = ((System.Data.DataRow)item).ItemArray[9].ToString();
fila.PUERTO_DESCARGA = ((System.Data.DataRow)item).ItemArray[10].ToString();
fila.DESTINO_FINAL = ((System.Data.DataRow)item).ItemArray[11].ToString();
fila.VAPOR = ((System.Data.DataRow)item).ItemArray[12].ToString();
fila.LINEA_NAVIERA = ((System.Data.DataRow)item).ItemArray[14].ToString();
fila.DAE = ((System.Data.DataRow)item).ItemArray[15].ToString();
fila.NO_BL = ((System.Data.DataRow)item).ItemArray[16].ToString();
fila.TIPO_CAJA = ((System.Data.DataRow)item).ItemArray[17].ToString();
fila.TIPO_CARGA = ((System.Data.DataRow)item).ItemArray[18].ToString();
fila.TIPO_CONTENEDOR = ((System.Data.DataRow)item).ItemArray[19].ToString();
fila.MARCA = ((System.Data.DataRow)item).ItemArray[20].ToString();
fila.CNTRS = int.Parse(((System.Data.DataRow)item).ItemArray[21].ToString());
fila.BOXES = int.Parse(((System.Data.DataRow)item).ItemArray[22].ToString());
fila.TOTAL_PESO_BRUTO = decimal.Parse(((System.Data.DataRow)item).ItemArray[23].ToString());
fila.TOTAL_PESO_NETO = decimal.Parse(((System.Data.DataRow)item).ItemArray[24].ToString());
//if (((System.Data.DataRow)item).ItemArray[26] != null && (((System.Data.DataRow)item).ItemArray[26].ToString().Trim() != ""))
//{
// DateTime FECHAHORACUTOFF;
// DateTime.TryParse(((System.Data.DataRow)item).ItemArray[26].ToString(), out FECHAHORACUTOFF);
// fila.FECHAHORACUTOFF = FECHAHORACUTOFF;
//}
_context.FilasTrackings.Add(fila);
_context.SaveChanges();
var op = false;
op = await RegistrarFila(fila);
if (op == true)
{
itemFila.Detalle = "Registrada Correctamente";
Lista.Add(itemFila);
NumeroFila = NumeroFila + 1;
}
else
{
itemFila.Detalle = "Error al registrar Fila";
Lista.Add(itemFila);
NumeroFila = NumeroFila + 1;
}
}
}
transaction.Commit();
return(Lista);
}
catch (Exception e)
{
transaction.Rollback();
throw;
// return false;
}
}
}
}
catch (Exception e)
{
throw;
}
}
return(Lista);
}
private static DataSet ConvertStreamToDataSet(Stream filestream)
{
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(filestream);
return(reader.AsDataSet());
}
public ActionResult UpdateMultiple(HttpPostedFileBase uploadfile)
{
if (ModelState.IsValid)
{
if (uploadfile != null && uploadfile.ContentLength > 0)
{
//ExcelDataReader works on binary excel file
Stream stream = uploadfile.InputStream;
//We need to written the Interface.
IExcelDataReader reader = null;
if (uploadfile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (uploadfile.FileName.EndsWith(".xlsx"))
{
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//Shows error if uploaded file is not Excel file
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
//First row is treated as the header
UseHeaderRow = true
}
});
var dataTable = result.Tables[0];
List <Student> StudentList = new List <Student>();
//Read each row one by one
for (var i = 0; i < dataTable.Rows.Count; i++)
{
var studNum = dataTable.Rows[i][0].ToString().Trim(); //Student Number
var program = dataTable.Rows[i][1].ToString().Trim(); //Program
var yearLvl = dataTable.Rows[i][2].ToString().Trim(); //Year Level
var updateStudent = new Student
{
StudentID = studNum,
Program = program,
YearLevel = Int32.Parse(yearLvl)
};
//Check if the student exists
var studentInDb = db.Students.FirstOrDefault(s => s.StudentID == updateStudent.StudentID);
if (studentInDb != null)
{
//update student data here
studentInDb.Program = updateStudent.Program;
studentInDb.YearLevel = updateStudent.YearLevel;
studentInDb.IsActive = true;
db.SaveChanges();
//add this student to the list of currently updated students
StudentList.Add(updateStudent);
}
else
{
Response.Redirect("/Home/Error?Error=Error updating student. Student # " + updateStudent.StudentID + " was not found in the database.");
}
}
//Get all the students who are active except for the currently updated students
List <string> StudentIdList = StudentList.Select(q => q.StudentID).ToList();
var activeStudents = db.Students.Where(s => s.IsActive == true && !StudentIdList.Contains(s.StudentID)).ToList();
//set isActive for all these students to false
foreach (var student in activeStudents)
{
student.IsActive = false;
db.SaveChanges();
}
reader.Close();
//Sending result data to View
return(View(result.Tables[0]));
}
}
else
{
ModelState.AddModelError("File", "Please upload your file");
}
return(View());
}
/// <summary>
/// Handles the OnClick event of the lbtnSaveAndImport control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void lbtnSaveAndImport_OnClick(object sender, EventArgs e)
{
Server.ScriptTimeout = 1200;
Save();
if (uploadedFile.UploadedFiles.Count > 0)
{
if (ddlType.SelectedValue.ToEnum <ImportType>() == ImportType.Excel) // Excel
{
var sheet = ViewState["SheetName"].ToString();
IExcelDataReader excelReader = null;
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (excelReader != null)
{
var result = excelReader.AsDataSet();
ddlSheet.Items.Clear();
foreach (DataTable table in result.Tables)
{
ddlSheet.Items.Add(new ListItem(table.TableName, table.TableName));
}
if (!string.IsNullOrEmpty(sheet) && ddlSheet.Items.FindByValue(sheet) != null)
{
ddlSheet.Items.FindByValue(sheet).Selected = true;
}
else
{
sheet = ddlSheet.Items[0].Value;
ViewState["SheetName"] = sheet;
}
if (result.Tables[sheet].Rows.Count > 0)
{
var tmpImportTableName = string.Format("TmpImport_{0}", _importId.ToString().Replace("-", ""));
var sqlTableCreator = new SqlTableCreator();
sqlTableCreator.Connection = new SqlConnection(ConfigurationManager.AppSettings["ADONETConnectionString"]);
sqlTableCreator.Connection.Open();
sqlTableCreator.DestinationTableName = tmpImportTableName;
sqlTableCreator.CreateFromDataTable(result.Tables[sheet]);
sqlTableCreator.Connection.Close();
if (txtFirstRow.Value > 1) // удаление лишних строк
{
for (int i = 1; i < txtFirstColumn.Value; i++)
{
result.Tables[sheet].Rows.RemoveAt(i - 1);
}
}
if (cbIsFirstRowAsColumnNames.Checked)
{
result.Tables[sheet].Rows.RemoveAt(0);
}
BulkInsertDataTable(ConfigurationManager.AppSettings["ADONETConnectionString"], tmpImportTableName, result.Tables[sheet]);
DataManager.Import.DoImport(_importId);
}
}
}
else // CSV
{
var sr = new StreamReader(uploadedFile.UploadedFiles[0].InputStream, Encoding.GetEncoding("windows-1251"));
var csvDataTable = new DataTable();
string strTextFromFile = "";
string[] strArrSplitText = null;
while ((strTextFromFile = sr.ReadLine()) != null)
{
var options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline | RegexOptions.IgnoreCase));
var r = new Regex(string.Format(@"\{0}(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))", CsvSeparator), options);
strArrSplitText = r.Split(strTextFromFile);
for (int i = 0; i < strArrSplitText.Length; i++)
{
strArrSplitText[i] = strArrSplitText[i].Trim('\"');
}
if (csvDataTable.Columns.Count == 0)
{
for (int i = 0; i < strArrSplitText.Length; i++)
{
csvDataTable.Columns.Add("Column" + (i + 1).ToString(), typeof(string));
}
}
var row = csvDataTable.NewRow();
for (int i = 0; i < strArrSplitText.Length; i++)
{
row[i] = strArrSplitText[i];
}
csvDataTable.Rows.Add(row);
}
var tmpImportTableName = string.Format("TmpImport_{0}", _importId.ToString().Replace("-", ""));
var sqlTableCreator = new SqlTableCreator();
sqlTableCreator.Connection = new SqlConnection(ConfigurationManager.AppSettings["ADONETConnectionString"]);
sqlTableCreator.Connection.Open();
sqlTableCreator.DestinationTableName = tmpImportTableName;
sqlTableCreator.CreateFromDataTable(csvDataTable);
sqlTableCreator.Connection.Close();
if (txtFirstRow.Value > 1) // удаление лишних строк
{
for (int i = 1; i < txtFirstColumn.Value; i++)
{
csvDataTable.Rows.RemoveAt(i - 1);
}
}
if (cbIsFirstRowAsColumnNames.Checked)
{
csvDataTable.Rows.RemoveAt(0);
}
BulkInsertDataTable(ConfigurationManager.AppSettings["ADONETConnectionString"], tmpImportTableName, csvDataTable);
DataManager.Import.DoImport(_importId);
}
}
Response.Redirect(UrlsData.AP_Imports());
}
static void RenameMP3()
{
mp3NamesDic.Clear();
string excelPath = EditorUtility.OpenFilePanel("打开 excel需求表 ", Application.dataPath, "*");
if (string.IsNullOrEmpty(excelPath))
{
Debug.LogError("excel 路径不存在");
return;
}
Debug.Log(excelPath);
using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.ReadWrite))
{
Debug.Log(stream == null);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);//exel需要放在Assets目录下
Debug.Log(excelReader.IsValid + " " + excelReader.ExceptionMessage);
DataSet result = excelReader.AsDataSet();
Debug.Log(result == null);
int columns = result.Tables[0].Columns.Count;
int rows = result.Tables[0].Rows.Count;
Debug.LogError(columns + " " + rows);
for (int i = 1; i < rows; i++)
{
string excelValue = result.Tables[0].Rows[i][6].ToString();
string excelKey = result.Tables[0].Rows[i][6].ToString();
if (excelKey.Split('_').Length < 3)
{
continue;
}
string _key = excelKey.Split('_')[3];
mp3NamesDic.Add(_key, excelValue);
Debug.Log($"key:={_key}, value={excelValue}");
}
}
if (mp3NamesDic.Count == 0)
{
Debug.LogError("先从excel表中获取名字");
return;;
}
string dirPath = EditorUtility.OpenFolderPanel("打开语音目录 ", Application.dataPath, "语音目录");
if (string.IsNullOrEmpty(dirPath))
{
Debug.LogError("语音 路径不存在");
return;
}
Debug.Log(dirPath);
string[] mp3Path = Directory.GetFiles(dirPath, "*.mp3");
for (int i = 0; i < mp3Path.Length; i++)
{
string item = mp3Path[i];
string oldfileName = Path.GetFileNameWithoutExtension(item);
Debug.Log("oldfileName " + oldfileName);
string _oldFileName = oldfileName.Split('_')[3];
if (!mp3NamesDic.ContainsKey(_oldFileName))
{
Debug.Log("没有找到文件");
continue;
}
string newFileName = mp3NamesDic[_oldFileName];
string newFileFullName = Path.Combine(dirPath, newFileName);
item = item.Replace('\\', '/');
newFileFullName = newFileFullName.Replace('\\', '/') + ".mp3";
Debug.Log($"旧文件名字：{item}, 新文件名字： {newFileFullName}");
File.Move(item, newFileFullName);
}
}
/// <summary>
/// Previews the specified rebuild columns.
/// </summary>
/// <param name="rebuildColumns">if set to <c>true</c> [rebuild columns].</param>
protected void Preview(bool rebuildColumns = true)
{
pnlWarning.Visible = false;
rgPreview.Visible = true;
pnlExcelSettings.Visible = ddlType.SelectedValue.ToEnum <ImportType>() == ImportType.Excel;
pnlCsvSettings.Visible = ddlType.SelectedValue.ToEnum <ImportType>() == ImportType.Csv;
var sheet = ViewState["SheetName"].ToString();
if (ddlType.SelectedValue.ToEnum <ImportType>() == ImportType.Excel) // Excel
{
IExcelDataReader excelReader = null;
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (uploadedFile.UploadedFiles[0].GetExtension() == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(uploadedFile.UploadedFiles[0].InputStream);
}
if (excelReader != null && excelReader.IsValid)
{
var result = excelReader.AsDataSet();
ddlSheet.Items.Clear();
foreach (DataTable table in result.Tables)
{
ddlSheet.Items.Add(new ListItem(table.TableName, table.TableName));
}
if (!string.IsNullOrEmpty(sheet) && ddlSheet.Items.FindByValue(sheet) != null)
{
ddlSheet.Items.FindByValue(sheet).Selected = true;
}
else
{
sheet = ddlSheet.Items[0].Value;
ViewState["SheetName"] = sheet;
}
var previewTable = result.Tables[sheet].Rows.Cast <DataRow>().Take(countPreviewRows).CopyToDataTable();
var firstRow = int.Parse(txtFirstRow.Text) - 1;
var firstColumn = int.Parse(txtFirstColumn.Text) - 1;
for (var i = firstColumn; i < previewTable.Columns.Count; i++)
{
columns.Add(previewTable.Rows[firstRow][i].ToString());
}
// Change column names to numeric
for (var i = 0; i < previewTable.Columns.Count; i++)
{
previewTable.Columns[i].ColumnName = (i + 1).ToString();
}
// Add numeric
var dc = previewTable.Columns.Add(" ");
dc.SetOrdinal(0);
for (var i = 0; i < previewTable.Rows.Count; i++)
{
previewTable.Rows[i][0] = i + 1;
}
rgPreview.DataSource = previewTable.DefaultView;
rgPreview.DataBind();
excelReader.Close();
upPreview.Update();
upImportArea.Update();
isPreviewed = true;
if (rebuildColumns)
{
for (var i = 0; i < columns.Count; i++)
{
_importColumns.Add(new tbl_ImportColumn
{
ID = Guid.NewGuid(),
Name = columns[i],
Source = string.Format("{0}:{1}", firstRow + 1, firstColumn + i + 1),
SystemName = string.Format("Column{0}", i + 1)
});
}
rgImportColumns.DataSource = _importColumns;
rgImportColumns.DataBind();
BindImportColumnRules();
}
}
else
{
pnlWarning.Visible = true;
}
}
else // CSV
{
//if (uploadedFile.UploadedFiles.Count > 0 && uploadedFile.UploadedFiles[0].GetExtension() == ".csv")
if (uploadedFile.UploadedFiles.Count > 0)
{
var sr = new StreamReader(uploadedFile.UploadedFiles[0].InputStream, Encoding.GetEncoding("windows-1251"));
var csvDataTable = new DataTable();
string strTextFromFile = "";
string[] strArrSplitText = null;
while ((strTextFromFile = sr.ReadLine()) != null)
{
var options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline | RegexOptions.IgnoreCase));
var r = new Regex(string.Format(@"\{0}(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))", CsvSeparator), options);
strArrSplitText = r.Split(strTextFromFile);
for (int i = 0; i < strArrSplitText.Length; i++)
{
strArrSplitText[i] = strArrSplitText[i].Trim('\"');
}
if (csvDataTable.Columns.Count == 0)
{
for (int i = 0; i < strArrSplitText.Length; i++)
{
csvDataTable.Columns.Add((i + 1).ToString(), typeof(string));
}
}
var row = csvDataTable.NewRow();
for (int i = 0; i < strArrSplitText.Length; i++)
{
row[i] = strArrSplitText[i];
}
csvDataTable.Rows.Add(row);
}
for (var i = 0; i < csvDataTable.Columns.Count; i++)
{
columns.Add(csvDataTable.Rows[0][i].ToString());
}
// Add numeric
var dc = csvDataTable.Columns.Add(" ");
dc.SetOrdinal(0);
for (var i = 0; i < csvDataTable.Rows.Count; i++)
{
csvDataTable.Rows[i][0] = i + 1;
}
rgPreview.DataSource = csvDataTable.DefaultView;
rgPreview.DataBind();
upPreview.Update();
upImportArea.Update();
isPreviewed = true;
if (rebuildColumns)
{
for (var i = 0; i < columns.Count; i++)
{
_importColumns.Add(new tbl_ImportColumn
{
ID = Guid.NewGuid(),
Name = columns[i],
Source = string.Format("{0}:{1}", 1, i + 1),
SystemName = string.Format("Column{0}", i + 1)
});
}
rgImportColumns.DataSource = _importColumns;
rgImportColumns.DataBind();
BindImportColumnRules();
}
}
}
}
private void ReadSpreadsheet(IExcelDataReader excelReader)
{
string role = serverRole.Value;
int roleColumn;
switch (role)
{
case "cd":
{
roleColumn = (int)serverType.cd;
break;
}
case "cm":
{
roleColumn = (int)serverType.cm;
break;
}
case "proc":
{
roleColumn = (int)serverType.processing;
break;
}
case "cmproc":
{
roleColumn = (int)serverType.cmProcessing;
break;
}
case "rep":
{
roleColumn = (int)serverType.reporting;
break;
}
default:
{
roleColumn = 0;
break;
}
}
string currentAppPath = manualSwitcher.Checked ? manualPathContainer.Value : Server.MapPath("~");
if (!Directory.Exists(currentAppPath + "\\App_Config"))
{
string errorText = String.Format("The provided directory: {0} , does not contain the 'App_Config' folder. Please ensure that you have set the 'Manual' path correctly or the current page has been installed in Sitecore", currentAppPath);
ShowErrors(errorText);
return;
}
string searchProv = searchProvider.Value;
List <string> processedConfigs = new List <string>();
DataSet dtSet = new DataSet();
dtSet = excelReader.AsDataSet();
System.Data.DataTable table = dtSet.Tables[0];
string configPath;
bool configShouldBeEnabled;
bool isConfigExists;
for (int i = 10; i < table.Rows.Count; i++)
{
if (String.IsNullOrEmpty(table.Rows[i][1].ToString()))
{
continue;
}
if (table.Rows[i][3].ToString() == "Web.config" || table.Rows[i][3].ToString() == "Web.config.Oracle")
{
continue;
}
if (roleColumn != 0)
{
configPath = table.Rows[i][2].ToString().Replace(@"\website", currentAppPath) + @"\" + table.Rows[i][3].ToString().Substring(0, table.Rows[i][3].ToString().IndexOf(".config") + 7).Replace(" ", String.Empty);
configShouldBeEnabled = table.Rows[i][roleColumn].ToString() == "Enable";
isConfigExists = File.Exists(configPath);
if (processedConfigs.Contains(table.Rows[i][2].ToString() + table.Rows[i][3].ToString().Substring(0, table.Rows[i][3].ToString().IndexOf(".config") + 7).Replace(" ", String.Empty)))
{
skippedConfigs.Add(table.Rows[i][2].ToString() + @"\" + table.Rows[i][3].ToString());
continue;
}
processedConfigs.Add(table.Rows[i][2].ToString() + table.Rows[i][3].ToString().Substring(0, table.Rows[i][3].ToString().IndexOf(".config") + 7).Replace(" ", String.Empty));
bool isSelectedProvider = table.Rows[i][5].ToString() == searchProv || searchProv == "Lucene is used" && table.Rows[i][5].ToString() == "Lucene" || searchProv == "Solr is used" && table.Rows[i][5].ToString() == "Solr" || table.Rows[i][5].ToString() == "Base" || String.IsNullOrEmpty(table.Rows[i][5].ToString()) ? true : false;
try
{
if (isSelectedProvider && isConfigExists && configShouldBeEnabled == false || !isSelectedProvider && isConfigExists && configShouldBeEnabled == true)
{
missconsistens.Add(configPath, "Should be disabled");
}
if (isSelectedProvider && !isConfigExists && configShouldBeEnabled)
{
missconsistens.Add(configPath, "Should be Enabled");
}
}
catch (ArgumentException)
{
skippedConfigs.Add(table.Rows[i][2].ToString().Replace(@"\website", currentAppPath) + @"\" + table.Rows[i][3].ToString());
}
catch (Exception ex)
{
string errorText = String.Format("The '{1}' error occurred during defining the misconsitensies. Check the StackTrace for detais: \n {0}", ex.StackTrace, ex.Message);
ShowErrors(errorText);
return;
}
}
}
excelReader.Close();
}
public bool LoadXlsx()
{
List <TableDataPath> tablePathList;
TableDataPath mainDataPath;
bool needTxt;
OnSetConnectionInfo(out tablePathList, out mainDataPath, out needTxt);
// 合并表格
if (!CombineExcel(mainDataPath, tablePathList))
{
Debug.LogError("合成表格错误");
return(false);
}
// 特殊情况：合并 modifier_node 表格
if (Path.GetFileNameWithoutExtension(mainDataPath.dataPath) == "aura")
{
string pathPrefix = Application.dataPath + "/";
TableDataPath data = new TableDataPath();
data.dataPath = pathPrefix + "../../server/bin/data/modifier_node.xlsx";
data.dataPath = data.dataPath.Replace("\\", "/");
data.tableName = "Sheet1";
TableDataPath data1 = new TableDataPath();
data1.dataPath = pathPrefix + "../../server/bin/data/modifier_node_old.xlsx";
data1.dataPath = data1.dataPath.Replace("\\", "/");
data1.tableName = "Sheet1";
TableDataPath data2 = new TableDataPath();
data2.dataPath = pathPrefix + "../../server/bin/data/modifier_node_new.xlsx";
data2.dataPath = data2.dataPath.Replace("\\", "/");
data2.tableName = "Sheet1";
TableDataPath data3 = new TableDataPath();
data3.dataPath = pathPrefix + "../../server/bin/data/modifier_node_x.xlsx";
data3.dataPath = data3.dataPath.Replace("\\", "/");
data3.tableName = "Sheet1";
TableDataPath data4 = new TableDataPath();
data4.dataPath = pathPrefix + "../../server/bin/data/modifier_node_wxw.xlsx";
data4.dataPath = data4.dataPath.Replace("\\", "/");
data4.tableName = "Sheet1";
TableDataPath data5 = new TableDataPath();
data5.dataPath = pathPrefix + "../../server/bin/data/modifier_node_gqc.xlsx";
data5.dataPath = data5.dataPath.Replace("\\", "/");
data5.tableName = "Sheet1";
TableDataPath data6 = new TableDataPath();
data6.dataPath = pathPrefix + "../../server/bin/data/modifier_node_zikai.xlsx";
data6.dataPath = data6.dataPath.Replace("\\", "/");
data6.tableName = "Sheet1";
TableDataPath data7 = new TableDataPath();
data7.dataPath = pathPrefix + "../../server/bin/data/modifier_node_thy.xlsx";
data7.dataPath = data7.dataPath.Replace("\\", "/");
data7.tableName = "Sheet1";
List <TableDataPath> pathList = new List <TableDataPath>()
{
data1, data2, data3, data4, data5, data6, data7
};
if (!(CombineExcel(data, pathList)))
{
Debug.LogError("合成 modifier_node 表错误");
return(false);
}
// 合并的表导出txt数据
if (needTxt && !SaveAs_Txt(data.dataPath, data.tableName))
{
Debug.LogError("modifier_node 导出txt错误");
return(false);
}
}
// 读取合并的表
FileStream stream = File.Open(mainDataPath.dataPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//DataTable dataTable = result.Tables[0];
DataTable dataTable = result.Tables[mainDataPath.tableName];
if (dataTable == null)
{
Debug.LogError("找不到子表, tableName: " + mainDataPath.tableName);
return(false);
}
if (!OnFetchData(dataTable))
{
Debug.LogError("读取表格有错");
return(false);
}
// 合并的表导出txt数据
if (needTxt && !SaveAs_Txt(mainDataPath.dataPath, mainDataPath.tableName))
{
Debug.LogError("表格导出txt错误");
return(false);
}
Debug.Log("done");
return(true);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
// Parse results
public static void ParseResults(IExcelDataReader excelReader, ref List<Fileline> list, ref List<int> index)
{
// Return list
var list_ = new List<Fileline>();
var index_ = new List<int>();
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
if (result.Tables.Count > 0)
{
var dt = result.Tables[0];
int j = 0;
int i = 0;
foreach (DataRow r in dt.Rows)
{
var line = new Fileline();
if (r[0] == DBNull.Value || r[1] == DBNull.Value)
{
index_.Add(j + 1);
}
else
{
ParseProperties(line, r, index_, i, j, ref index_);
////################################################################//
//XmlSerializer SerializerObj = new XmlSerializer(typeof(Fileline));
//TextWriter WriteFileStream = new StreamWriter(@"C:\Test\test.xml");
//SerializerObj.Serialize(WriteFileStream, line);
////################################################################//
list_.Add(line);
}
j++;
}
i = 0;
}
// Return list
list = list_;
index = index_;
}