private void ReadSegmentDetails(int companyId, string sheet)
{
var columnNames = excelQueryFactory.GetColumnNames(sheet);
var tempResult = from data in excelQueryFactory.Worksheet(sheet) select data;
var resultSet = new List <CompanySegment>();
foreach (var row in tempResult)
{
var data = new CompanySegment();
for (var index = 0; index < row.Count; index++)
{
switch (index)
{
case 0:
data.RowHeader = row[index];
break;
case 1:
data.ValueCell1 = row[index];
break;
case 2:
data.ValueCell2 = row[index];
break;
case 3:
data.ValueCell3 = row[index];
break;
case 4:
data.ValueCell4 = row[index];
break;
case 5:
data.ValueCell5 = row[index];
break;
}
}
if (string.IsNullOrEmpty(data.RowHeader))
{
data.IsHeaderRow = 1;
}
data.SheetName = sheet;
data.CompanyId = companyId;
resultSet.Add(data);
}
foreach (var item in resultSet)
{
dbMapper.SaveCompanySegment(item);
}
}
protected static List <Row> CreateRows(ExcelQueryFactory excel, bool hasHeader, int breakAfter)
{
var currentWorkSheet = excel.GetWorksheetNames().First();
var sheet = hasHeader
? excel.Worksheet(currentWorkSheet).Select(t => t as List <Cell>)
: excel.WorksheetNoHeader(currentWorkSheet).Select(t => t as List <Cell>);
List <string> columns = null;
var xls = new ExcelQueryFactory(excel.FileName);
columns = hasHeader
? xls.GetColumnNames(currentWorkSheet).ToList()
: xls.GetColumnNames(currentWorkSheet).Select((c, i) => "Column" + 1).ToList();
var list = new List <Row>(sheet.Count());
var emptyCount = 0;
foreach (var row in sheet)
{
var importRow = new Row();
for (var i = 0; i < columns.Count(); i++)
{
importRow.Insert(i, row[i]);
}
var empty = row.All(v => string.IsNullOrEmpty(v.Value.ToString().Trim()));
if (empty)
{
emptyCount++;
}
else
{
emptyCount = 0;
}
if (emptyCount > breakAfter)
{
break;
}
if (!empty)
{
list.Add(importRow);
}
}
return(list);
}
public override IEnumerable <string> Read()
{
var data = new ExcelQueryFactory(FilePath);
var firstWorksheetName = data.GetWorksheetNames().ToList()[0];
return(data.GetColumnNames(firstWorksheetName));
}
/// <summary>
/// Lấy về danh sách các cột ở trong file Excel
/// </summary>
/// <param name="ExcelFileName">Đường dẫn trở tới file Excel</param>
/// <param name="SheetName">Tên sheet cần lấy ra, mặc định là Sheet1, Sheet2, Sheet3...</param>
/// <returns></returns>
public IEnumerable <string> GetColumnName(string ExcelFileName, string SheetName)
{
var excel = new ExcelQueryFactory(ExcelFileName);
var columnName = excel.GetColumnNames(SheetName);
return(columnName);
}
public void GetColumnNames_throws_exception_when_filename_not_set()
{
var factory = new ExcelQueryFactory(new LogManagerFactory());
Assert.That(() => factory.GetColumnNames(""),
Throws.TypeOf <NullReferenceException>(), "FileName property is not set");
}
//[Test]
public void LinqMethod1(string WorksheetName)
{
string filePath = "G:\\data.xlsx";
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(filePath);
var artistAlbums = from a in excelFile.Worksheet(sheetName) select a;
var columnNames = excelFile.GetColumnNames("Sheet1");
foreach (var column in columnNames)
{
//Console.WriteLine(column);
if (column.Equals("Name"))
{
foreach (var a in artistAlbums)
{
var name = a["Name"].ToString();
if (name.Equals("senthil"))
{
string artistInfo = "Artist Name: {0}; Album: {1}";
Console.WriteLine(string.Format(artistInfo, a["Name"], a["Age"]));
//Console.WriteLine(a["Name"] + " " + a["Age"]);
//Console.WriteLine(a["Age"]);
}
}
}
}
}
static void Main(string[] args)
{
// Получение информации из Excel документа с именем SampleData.xls
string pathFile = "SampleData.xls";
var excelFile = new ExcelQueryFactory(pathFile);
// Вывод списка листов (Worksheet) в Excel документе
foreach( var item in excelFile.GetWorksheetNames())
{
Console.Write(item+" ");
}
Console.Write("\n");
foreach (var item in excelFile.GetWorksheetNames())
{
Console.Write(item + ":");
foreach (var itemRow in excelFile.GetColumnNames(item))
{
Console.Write(itemRow + " ");
}
Console.Write("\n");
}
// установка документа "только для чтения"
excelFile.ReadOnly = true;
Console.ReadKey();
}
public IEnumerable<Product> GetProductsFromFile(string filename)
{
var excel = new ExcelQueryFactory(filename);
var worksheetNames = excel.GetWorksheetNames().ToArray();
//
//Mapping
//
for (int i = 0; i < worksheetNames.Length; i++)
{
var columnNames = excel.GetColumnNames(worksheetNames[i]);
var titleRow = FindTitleRow(excel.WorksheetNoHeader(worksheetNames[i]));
var codeIndex = 0;
var priceIndex = GetColumnIndex(titleRow, "Price");
bool skip = true;
foreach (var product in excel.WorksheetNoHeader(worksheetNames[i])
.Where(x => x[codeIndex] != ""))
{
if (skip)
{
skip = product[codeIndex].ToString().Trim() != "No.:";
continue;
}
yield return
new Product
{
Code = product[codeIndex].ToString().Trim(),
Price = product[priceIndex].Cast<decimal>()
};
}
}
}
private void LoadColumns()
{
var excel = new ExcelQueryFactory(FileName);
var columns = excel.GetColumnNames(CurrentWorkSheet).OrderBy(col => col);
BindColumns(cbCodigoCliente, columns, true, "CodigoCliente");
BindColumns(cbDescripcionCliente, columns, true, "DescripcionCliente");
BindColumns(cbDireccionCliente, columns, true, "DireccionCliente");
BindColumns(cbLocalidadCliente, columns, true, "LocalidadCliente");
BindColumns(cbProvinciaCliente, columns, true, "ProvinciaCliente");
BindColumns(cbTelefonoCliente, columns, true, "TelefonoCliente");
BindColumns(cbCodigoPuntoEntrega, columns, true, "CodigoPtoDeEntrega");
BindColumns(cbDescripcionPuntoEntrega, columns, true, "DescripcionPtoDeEntrega");
BindColumns(cbDireccionPuntoEntrega, columns, true, "DireccionPtoDeEntrega");
BindColumns(cbLocalidadPuntoEntrega, columns, true, "LocalidadPtoDeEntrega");
BindColumns(cbCodigoTicket, columns, true, "CodigoTicket");
BindColumns(cbFecha, columns, true, "Fecha");
BindColumns(cbCantidadPedido, columns, true, "CantPedido");
BindColumns(cbCantidadAcumulada, columns, true, "CantAcumulada");
BindColumns(cbUnidad, columns, true, "Unidad");
BindColumns(cbCodigoProducto, columns, true, "CodigoProducto");
BindColumns(cbComentario1, columns, true, "Comentario1");
BindColumns(cbComentario2, columns, true, "Comentario2");
BindColumns(cbComentario3, columns, true, "Comentario3");
lblDirs.Text = string.Format(Reporte, 0, excel.Worksheet(CurrentWorkSheet).Count(), 0, "0.00", 0, "0.00", 0, "0.00", 0);
SetProgressBar(0);
}
protected virtual void BindFields(DropDownList combo, int index)
{
combo.Items.Clear();
if (string.IsNullOrEmpty(FileName))
{
return;
}
IEnumerable <string> columns = null;
switch (FileType)
{
case FileTypes.Excel97:
var excel = new ExcelQueryFactory(FileName);
columns = excel.GetColumnNames(CurrentWorkSheet).ToList();
break;
case FileTypes.Csv:
var csv = File.ReadAllLines(FileName);
columns = csv[0].Split(';');
break;
}
combo.Items.Add(string.Empty);
foreach (var fieldValue in columns)
{
combo.Items.Add(fieldValue);
}
if (combo.Items.Count > index + 1)
{
combo.SelectedIndex = index + 1;
}
}
public IEnumerable <Product> GetProductsFromFile(string filename)
{
var excel = new ExcelQueryFactory(filename);
var worksheetNames = excel.GetWorksheetNames().ToArray();
//
//Mapping
//
for (int i = 0; i < worksheetNames.Length; i++)
{
var columnNames = excel.GetColumnNames(worksheetNames[i]);
var titleRow = FindTitleRow(excel.WorksheetNoHeader(worksheetNames[i]));
var codeIndex = 0;
var priceIndex = GetColumnIndex(titleRow, "Price");
bool skip = true;
foreach (var product in excel.WorksheetNoHeader(worksheetNames[i])
.Where(x => x[codeIndex] != ""))
{
if (skip)
{
skip = product[codeIndex].ToString().Trim() != "No.:";
continue;
}
yield return
(new Product
{
Code = product[codeIndex].ToString().Trim(),
Price = product[priceIndex].Cast <decimal>()
});
}
}
}
public void GetColumnNames_throws_exception_when_filename_not_set()
{
Assert.Throws <NullReferenceException>(() =>
{
var factory = new ExcelQueryFactory();
factory.GetColumnNames("");
}, "FileName property is not set");
}
public void GetColumnNames_returns_column_names()
{
var excel = new ExcelQueryFactory(_excelFileName);
var columnNames = excel.GetColumnNames("Sheet1");
Assert.AreEqual(
"Name, CEO, EmployeeCount, StartDate",
string.Join(", ", columnNames.ToArray()));
}
private List <string> GetColumnNames(string nameFile)
{
FileInfo fileInfO = new FileInfo(nameFile);
var excel = new ExcelQueryFactory(fileInfO.FullName);
List <string> worksheetList = excel.GetWorksheetNames().ToList();
return(excel.GetColumnNames(worksheetList[0]).ToList());
}
public void GetColumnNames_returns_column_names()
{
var excel = new ExcelQueryFactory(_excelFileName, new LogManagerFactory());
var columnNames = excel.GetColumnNames("Sheet1");
Assert.AreEqual(
"Name, CEO, EmployeeCount, StartDate",
string.Join(", ", columnNames.ToArray()));
}
public IEnumerable <string> getColumnsName(string sheetName, ExcelQueryFactory excelFile)
{
try {
var colNames = excelFile
.GetColumnNames(sheetName, "A2:I2")
.ToList();
return(colNames);
}
catch (Exception)
{ throw; }
}
public void ExcelFielder_ReadExcelFileWithFieldsAtTopRow_ReturnTopRow()
{
var mockFieldManager = new Mock<IFieldManager>();
var fielder = new ExcelFielder(mockFieldManager.Object);
fielder.FileSource = @"C:\Users\jpinkard\Documents\GitHub\SolutionsForWork\AcumiChart\TestFiles\Excel_FieldInTopRowSample.xlsx";
fielder.WorksheetSource = "NoDupFields";
var excel = new ExcelQueryFactory(@"C:\Users\jpinkard\Documents\GitHub\SolutionsForWork\AcumiChart\TestFiles\Excel_FieldInTopRowSample.xlsx");
var fields = excel.GetColumnNames("NoDupFields");
fielder.Read();
Assert.That(mockFieldManager.AvailableFields, Is.EqualTo(fields));
}
private static string[,] BomToArray(ExcelQueryFactory bom)
{
var worksheetNames = bom.GetWorksheetNames();
var columnNames = bom.GetColumnNames(worksheetNames.ElementAt(0));
List <string> itemNums = new List <string>();
List <string> colNames = new List <string>();
foreach (var c in columnNames)
{
colNames.Add(c.ToString());
}
foreach (var c in bom.Worksheet())
{
if (c[0].ToString() == "")
{
break;
}
itemNums.Add(c[0].ToString());
}
String[,] bomArray = new string[itemNums.Count() + 1, colNames.Count()];
for (int i = 0; i < colNames.Count; i++)
{
bomArray[0, i] = colNames.ElementAt(i);
}
int bomRows = bomArray.GetLength(0);
int bomCols = bomArray.GetLength(1);
int row = 1;
foreach (var c in bom.Worksheet())
{
for (int col = 0; col < bomCols; col++)
{
bomArray[row, col] = c[col];
}
row++;
if (row == bomRows)
{
break;
}
}
return(bomArray);
}
private static IQueryable <Coach> LeesCoaches(ExcelQueryFactory excel)
{
excel.AddMapping <Coach>(x => x.Naam, "NAAM VOORNAAM");
excel.AddMapping <Coach>(x => x.Ploeg, "coach - begeleider");
excel.AddMapping <Coach>(x => x.Email, "E-mail");
excel.AddMapping <Coach>(x => x.Telefoonnummers, "Telefoonnr#", s => { return(s); });
excel.AddMapping <Coach>(x => x.Licentie, "licentie");
foreach (var field in excel.GetColumnNames("Blad4"))
{
Debug.WriteLine(field);
}
var coaches = from c in excel.Worksheet <Coach>("Blad4") select c;
return(coaches);
}
private void LoadLanguages(SqlConnection connection, string path)
{
ExcelQueryFactory factory = new ExcelQueryFactory(path);
ExcelFileRow[] languages = (from row in factory.Worksheet <ExcelFileRow>(0)
select row).ToList().DeleteNulls();
names = factory.GetColumnNames(Categories).ToArray();
//table Languages
for (int i = 0; i < languages.Length; i++)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = $"insert into Languages (name) values (@name)";
command.Parameters.Add("@name", SqlDbType.VarChar).Value = names[i + 1];
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
//table LanguageTranslations
for (int i = 0; i < languages.Length; i++)
{
int language_id = Helper.GetId(Languages, names[i + 1], connection);
for (int j = 1; j < languages.Length + 1; j++)
{
int native_lang_id = Helper.GetId(Languages, names[j], connection);
if (i >= languages.Length)
{
break;
}
using (SqlCommand command = new SqlCommand())
{
string[] properties = Helper.GetPropertyValues(languages[i]);
command.Connection = connection;
command.CommandText = "insert into LanguageTranslations (translation, lang_id, native_lang_id) " +
$"values (@translation, @lang_id, @native_lang_id)";
command.Parameters.Add("@translation", SqlDbType.NVarChar).Value = properties[j];
command.Parameters.Add("@lang_id", SqlDbType.VarChar).Value = language_id;
command.Parameters.Add("@native_lang_id", SqlDbType.VarChar).Value = native_lang_id;
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
}
}
public static string GetFiletype(string filepath)
{
string extension = GetFileExtension(filepath).ToLower();
string result = "Invalid";
var excel = new ExcelQueryFactory(filepath);
try
{
string[] headers;
if (extension == "csv")
{
headers = System.IO.File.ReadLines(filepath).First().Split(',');
}
else
{
headers = excel.GetColumnNames(excel.GetWorksheetNames().First()).ToArray();
}
//This is the see if its a sentral file
if (headers.Contains("First Name") && headers.Contains("Surname"))
{
result = "Sentral";
if (headers.Contains("Award"))
{
result += " Awards";
}
else if (headers.Contains("Activity Name"))
{
result += " Activities";
}
}
//or a vivo file
else if (headers.Contains("fname") && headers.Contains("sname") && headers.Contains("miles_total"))
{
result = "VIVO points";
}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
return(result);
}
void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
//var excel = new ExcelQueryFactory(filenamesopen);
try
{
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
List <string> columnNames = excel.GetColumnNames(cboSheet.Text.ToString().Trim()).ToList();
setComboList(cboCredit, columnNames);
setComboList(cboDate, columnNames);
setComboList(cboDebit, columnNames); setComboList(CboBalance, columnNames);
}
catch (Exception)
{
throw;
}
finally
{ SplashScreenManager.CloseForm(false); }
}
private void LoadColumns()
{
var excel = new ExcelQueryFactory(FileName);
var columns = excel.GetColumnNames(CurrentWorkSheet).OrderBy(col => col);
BindColumns(cbDescripcion, columns, true, "Descripcion");
BindColumns(cbCodigo, columns, true, "Codigo");
BindColumns(cbCalle, columns, true, "Calle");
BindColumns(cbAltura, columns, true, "Altura");
BindColumns(cbEsquina, columns, true, "Esquina");
BindColumns(cbPartido, columns, true, "Partido");
BindColumns(cbProvincia, columns, true, "Provincia");
BindColumns(cbLatitud, columns, true, "Latitud");
BindColumns(cbLongitud, columns, true, "Longitud");
BindColumns(cbDesde, columns, true, "VigenciaDesde");
BindColumns(cbHasta, columns, true, "VigenciaHasta");
lblDirs.Text = string.Format(Reporte, 0, 0, "0.00", 0, "0.00", 0, "0.00", 0, excel.Worksheet(CurrentWorkSheet).Count(), 0, "0.00");
SetProgressBar(0);
}
/// <summary> 设置xls 映射关系
/// </summary>
/// <param name="userXLS"></param>
/// <returns> 错误列 集合
/// </returns>
private static List <string> SetXlsMapping <VMdl>(ExcelQueryFactory userXLS)
{
List <string> errColumns = new List <string>();
//xls现有数据字段
var firstSheet = userXLS.GetWorksheetNames().First <string>();
List <string> cnlist = userXLS.GetColumnNames(firstSheet).ToList <string>();
Type type = typeof(VMdl);
var ProArr = type.GetProperties();
foreach (var pro in ProArr)
{
var attributeArr = pro.GetCustomAttributes(true);
var title = pro.Name;
var name = pro.Name;
var mapAttribute = attributeArr.OfType <MapFromAttribute>().FirstOrDefault();
if (mapAttribute != null)
{
title = mapAttribute.ColumnName;
}
var requiredAttri = attributeArr.OfType <NecessaryColumnAttribute>().FirstOrDefault();
if (requiredAttri != null)
{
if (!cnlist.Exists(cn => cn == title)) //不存在
{
errColumns.Add(title);
continue;
}
}
if (mapAttribute != null)
{
userXLS.AddMapping(name, title);
}
}
return(errColumns);
}
public void WriteDataFromExcelToXML()
{
XDocument doc = new XDocument();
doc.Add(new XElement("Store"));
string pathFile = "../../PriceProductBase.xlsx";
var excelFile = new ExcelQueryFactory(pathFile);
foreach (var item in excelFile.GetWorksheetNames())
{
var category = new XElement(item);
List<Row> elements;
if (item == "Prices")
{
elements = (from a in excelFile.Worksheet(item) where a["КодТовара"] != "" select a).Take(5).ToList();
}
else
{
elements = (from a in excelFile.Worksheet(item) select a).Take(5).ToList();
}
foreach (var el in elements)
{
XElement element = new XElement("Element");
foreach(var itemRow in excelFile.GetColumnNames(item))
{
XElement data = new XElement(itemRow);
data.Value = el[itemRow];
element.Add(data);
}
category.Add(element);
}
doc.Root.Add(category);
}
excelFile.ReadOnly = true;
doc.Save("PriceProductBase.xml");
}
static void Main(string[] args)
{
// Получение информации из Excel документа с именем SampleData.xls
string pathFile = "PriceProductBase.xlsx";
var excelFile = new ExcelQueryFactory(pathFile);
string sheetName = "Orders";
// Вывод списка листов (Worksheet) в Excel документе
var infoOrders = from a in excelFile.Worksheet(sheetName)
select a;
foreach (var item in excelFile.GetWorksheetNames())
{
foreach (var itemRow in excelFile.GetColumnNames(item))
{
Console.Write(itemRow + " ");
}
Console.Write("\n");
}
foreach (var item in infoOrders)
{
string pattern = "{0};{1};{2};{3};{4};{5};{6};";
// Вывод информации из столбцов OrderDate и Total
Console.WriteLine(string.Format(pattern, item["Номер заказа"],
item["Номер продукта"], item["F3"],
item["F4"], item["F5"],
item["F6"], item["F7"]));
}
// установка документа "только для чтения"
excelFile.ReadOnly = true;
Console.ReadKey();
}
public void Generate()
{
var inputFile = GeneratorFileHelper.GetInputExcelFile();
var excel = new ExcelQueryFactory(inputFile);
var columns = excel.GetColumnNames("Payouts").Skip(1);
var outputFile = GeneratorFileHelper.InitializeOutputJsFile("Cities.js");
var builder = new StringBuilder("Boxcars.Data.cities = [\n\t");
var counter = 0;
foreach (var column in columns)
{
builder.AppendFormat("'{0}', ", column);
counter = (counter + 1)%5;
if (counter == 0)
builder.Append("\n\t");
}
//Remove the last comma and space
builder.Length -= 2;
builder.Append("\n];");
File.AppendAllText(outputFile, builder.ToString());
}
private static IQueryable <Lid> LeesLeden(ExcelQueryFactory excel)
{
excel.AddMapping <Lid>(x => x.Naam, "Naam");
excel.AddMapping <Lid>(x => x.RugNummer, "Nr", s =>
{
int result;
if (int.TryParse(s, out result))
{
return(result);
}
return(null);
}
);
excel.AddMapping <Lid>(x => x.Adres, "Adres");
excel.AddMapping <Lid>(x => x.Gemeente, "Postn + Gem#");
excel.AddMapping <Lid>(x => x.Ploeg, "Ploeg");
excel.AddMapping <Lid>(x => x.Geb, "Geb", s =>
{
DateTime result;
if (DateTime.TryParse(s, CultureInfo.GetCultureInfo("nl-be"), DateTimeStyles.None, out result))
{
return(result);
}
return(null);
});
excel.AddMapping <Lid>(x => x.Nat, "Nat");
excel.AddMapping <Lid>(x => x.Gesl, "Gesl");
excel.AddMapping <Lid>(x => x.Email, "E-mail");
excel.AddMapping <Lid>(x => x.Telefoonnummers, "Telefoonnr#", s => { return(s); });
excel.AddMapping <Lid>(x => x.Licentie, "licentie");
foreach (var field in excel.GetColumnNames("Blad1"))
{
Debug.WriteLine(field);
}
var leden = from c in excel.Worksheet <Lid>("Blad1") select c;
return(leden);
}
private void btn_Import_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.ShowDialog();
string pathfile = of.FileName;
if (pathfile != "")
{
string ext = Path.GetExtension(pathfile);
if (ext.ToLower() == ".xls" || ext.ToLower().Equals(".xlsx"))
{
var exceltmp = new ExcelQueryFactory(pathfile);
var tmp = from s in exceltmp.Worksheet("Sheet1") select s;
var columnNames = exceltmp.GetColumnNames("Sheet1");
DataTable dtExcelRecords = new DataTable();
foreach (var columnName in columnNames)
{
dtExcelRecords.Columns.Add(columnName);
}
foreach (var row in tmp)
{
DataRow dr = dtExcelRecords.NewRow();
foreach (var columnName in columnNames)
{
dr[columnName] = row[columnName];
}
dtExcelRecords.Rows.Add(dr);
}
LuuDuLieuTuImportExcel(dtExcelRecords);
}
// testimportexcel = 1;
}
else
{
MessageBox.Show("Vui lòng chọn file excel muốn import!");
}
}
private void btn_Import_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (txt_DuongDan.EditValue.ToString() != "")
{
string file = txt_DuongDan.EditValue.ToString();
string ext = Path.GetExtension(file);
if (ext.ToLower() == ".xls" || ext.ToLower().Equals(".xlsx"))
{
var exceltmp = new ExcelQueryFactory(file);
var tmp = from s in exceltmp.Worksheet("Sheet1") select s;
var columnNames = exceltmp.GetColumnNames("Sheet1");
DataTable dtExcelRecords = new DataTable();
foreach (var columnName in columnNames)
{
dtExcelRecords.Columns.Add(columnName);
}
foreach (var row in tmp)
{
DataRow dr = dtExcelRecords.NewRow();
foreach (var columnName in columnNames)
{
dr[columnName] = row[columnName];
}
dtExcelRecords.Rows.Add(dr);
}
gridControl1.DataSource = dtExcelRecords;
}
testimportexcel = 1;
}
else
{
MessageBox.Show("Vui lòng chọn file excel muốn import!");
}
}
public static DataTable getFromExcel(string fileName, string workbookName)
{
DataTable dt = new DataTable();
var excel = new ExcelQueryFactory(fileName);
var query = from c in excel.Worksheet(workbookName)
select c;
var names = excel.GetColumnNames(workbookName);
string line = string.Empty;
string[] columns = new string[names.Count()];
string[] orig_columns = new string[names.Count()];
int i = 0;
foreach (string s in names)
{
orig_columns[i] = s;
columns[i] = s.Replace(' ', '_');
i++;
}
foreach (string s in columns)
{
dt.Columns.Add(s, typeof(string));
}
foreach (Row r in query)
{
DataRow row = dt.NewRow();
i = 0;
for (i = 0; i < names.Count(); i++)
{
row[columns[i]] = r[orig_columns[i]].ToString();
}
dt.Rows.Add(row);
}
return(dt);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ReturnOrderDNum, "退货单号");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.HeadId, "头表ID");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ReturnQty, "退货数量");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Remark, "备注");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.PrintStaus, "打印状态");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.PrintDate, "打印日期");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.PrintMan, "打印人");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ConfirmStatus, "确认状态");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ConfirmMan, "确认人");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ConfirmDate, "确认时间");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Attr1, "");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Attr2, "");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Attr3, "");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Attr4, "");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.Attr5, "");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.CreatePerson, "创建人");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.CreateTime, "创建时间");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ModifyPerson, "修改人");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.ModifyTime, "修改时间");
excelFile.AddMapping <WMS_ReturnOrder_DModel>(x => x.BatchId, "");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_ReturnOrder_DModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_ReturnOrder_DModel();
model.Id = row.Id;
model.ReturnOrderDNum = row.ReturnOrderDNum;
model.HeadId = row.HeadId;
model.ReturnQty = row.ReturnQty;
model.Remark = row.Remark;
model.PrintStaus = row.PrintStaus;
model.PrintDate = row.PrintDate;
model.PrintMan = row.PrintMan;
model.ConfirmStatus = row.ConfirmStatus;
model.ConfirmMan = row.ConfirmMan;
model.ConfirmDate = row.ConfirmDate;
model.Attr1 = row.Attr1;
model.Attr2 = row.Attr2;
model.Attr3 = row.Attr3;
model.Attr4 = row.Attr4;
model.Attr5 = row.Attr5;
model.CreatePerson = row.CreatePerson;
model.CreateTime = row.CreateTime;
model.ModifyPerson = row.ModifyPerson;
model.ModifyTime = row.ModifyTime;
model.BatchId = row.BatchId;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_ReturnOrder_D entity = new WMS_ReturnOrder_D();
entity.Id = model.Id;
entity.ReturnOrderDNum = model.ReturnOrderDNum;
entity.HeadId = model.HeadId;
entity.ReturnQty = model.ReturnQty;
entity.Remark = model.Remark;
entity.PrintStaus = model.PrintStaus;
entity.PrintDate = model.PrintDate;
entity.PrintMan = model.PrintMan;
entity.ConfirmStatus = model.ConfirmStatus;
entity.ConfirmMan = model.ConfirmMan;
entity.ConfirmDate = model.ConfirmDate;
entity.Attr1 = model.Attr1;
entity.Attr2 = model.Attr2;
entity.Attr3 = model.Attr3;
entity.Attr4 = model.Attr4;
entity.Attr5 = model.Attr5;
entity.CreatePerson = model.CreatePerson;
entity.CreateTime = model.CreateTime;
entity.ModifyPerson = model.ModifyPerson;
entity.ModifyTime = model.ModifyTime;
entity.BatchId = model.BatchId;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_ReturnOrder_D.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.ProductBillNum, "入库单号（业务）(必输)");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.EntryBillNum, "入库单号（系统）");
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.Department, "本货部门");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Partid, "物料");
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.PartCode, "物料编码(必输)");
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.ProductQty, "数量(必输)");
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.Lot, "批次(格式：YYYY-MM-DD)");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.InvId, "库存");
excelFile.AddMapping <WMS_Product_EntryModel>(x => x.InvName, "库房");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.SubInvId, "子库存");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Remark, "备注");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_Product_EntryModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_Product_EntryModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
string productBillNum = "Z" + DateTime.Now.ToString("yyyyMMddHHmmssff");
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_Product_EntryModel();
model.Id = row.Id;
model.ProductBillNum = productBillNum;
//productBillNum = row.ProductBillNum;
model.EntryBillNum = row.EntryBillNum;
model.Department = row.Department;
//model.Partid = row.Partid;
model.PartCode = row.PartCode;
model.ProductQty = row.ProductQty;
model.Lot = row.Lot;
//model.InvId = row.InvId;
model.InvName = row.InvName;
//model.SubInvId = row.SubInvId;
model.Remark = row.Remark;
model.Attr1 = row.Attr1;
model.Attr2 = row.Attr2;
model.Attr3 = row.Attr3;
model.Attr4 = row.Attr4;
model.Attr5 = row.Attr5;
model.CreatePerson = row.CreatePerson;
model.CreateTime = row.CreateTime;
model.ModifyPerson = row.ModifyPerson;
model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Product_Entry entity = new WMS_Product_Entry();
entity.Id = model.Id;
entity.ProductBillNum = model.ProductBillNum;
//entity.ProductBillNum = "ZZJ" + DateTime.Now.ToString("yyyyMMddHHmmssff");
entity.EntryBillNum = model.EntryBillNum;
//entity.EntryBillNum = "ZZJ" + DateTime.Now.ToString("yyyyMMddHHmmssff");
entity.Department = "总装车间";
entity.Partid = model.Partid;
entity.ProductQty = model.ProductQty;
entity.InvId = model.InvId;
entity.Lot = model.Lot;
//entity.SubInvId = model.SubInvId;
entity.Remark = model.Remark;
//entity.Attr1 = model.Attr1;
//entity.Attr2 = model.Attr2;
//entity.Attr3 = model.Attr3;
//entity.Attr4 = model.Attr4;
//entity.Attr5 = model.Attr5;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Product_Entry.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
//全部保存成功后，调用存储过程“P_WMS_ProcessProductEntry”入库。
ObjectParameter returnValue = new ObjectParameter("ReturnValue", typeof(string));
db.P_WMS_ProcessProductEntry(oper, productBillNum, returnValue);
if (returnValue.Value == DBNull.Value)
{
tran.Commit();
}
else
{
tran.Rollback();
errors.Add((string)returnValue.Value);
}
//tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_POModel>(x => x.PO, "采购订单(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PODate, "采购日期(必输格式:YYYY-MM-DD)");
excelFile.AddMapping <WMS_POModel>(x => x.SupplierShortName, "供应商简称(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PartCode, "物料编码(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.QTY, "数量(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PlanDate, "计划到货日期");
excelFile.AddMapping <WMS_POModel>(x => x.POType, "采购订单类型");
//excelFile.AddMapping<WMS_POModel>(x => x.Status, "状态");
excelFile.AddMapping <WMS_POModel>(x => x.Remark, "说明");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_POModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_POModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_POModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_POModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_POModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_POModel();
model.Id = row.Id;
model.PO = row.PO;
model.PODate = row.PODate;
model.SupplierShortName = row.SupplierShortName;
model.PartCode = row.PartCode;
model.QTY = row.QTY;
model.PlanDate = row.PlanDate;
model.POType = row.POType;
model.Status = "有效";
model.Remark = row.Remark;
//model.Attr1 = row.Attr1;
//model.Attr2 = row.Attr2;
//model.Attr3 = row.Attr3;
//model.Attr4 = row.Attr4;
//model.Attr5 = row.Attr5;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_PO entity = new WMS_PO();
entity.Id = model.Id;
entity.PO = model.PO;
entity.PODate = model.PODate;
entity.SupplierId = model.SupplierId;
entity.PartId = model.PartId;
entity.QTY = model.QTY;
entity.PlanDate = model.PlanDate;
entity.POType = model.POType;
entity.Status = "有效";
entity.Remark = model.Remark;
entity.Attr1 = model.Attr1;
entity.Attr2 = model.Attr2;
entity.Attr3 = model.Attr3;
entity.Attr4 = model.Attr4;
entity.Attr5 = model.Attr5;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_PO.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_CustomerModel>(x => x.CustomerCode, "客户编码");
excelFile.AddMapping <WMS_CustomerModel>(x => x.CustomerShortName, "客户简称");
excelFile.AddMapping <WMS_CustomerModel>(x => x.CustomerName, "客户名称");
excelFile.AddMapping <WMS_CustomerModel>(x => x.CustomerType, "客户类型");
excelFile.AddMapping <WMS_CustomerModel>(x => x.LinkMan, "联系人");
excelFile.AddMapping <WMS_CustomerModel>(x => x.LinkManTel, "联系电话");
excelFile.AddMapping <WMS_CustomerModel>(x => x.LinkManAddress, "联系地址");
//excelFile.AddMapping<WMS_CustomerModel>(x => x.Status, "状态");
excelFile.AddMapping <WMS_CustomerModel>(x => x.Remark, "说明");
//excelFile.AddMapping<WMS_CustomerModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_CustomerModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_CustomerModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_CustomerModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_CustomerModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_CustomerModel();
model.Id = row.Id;
model.CustomerCode = row.CustomerCode;
model.CustomerShortName = row.CustomerShortName;
model.CustomerName = row.CustomerName;
model.CustomerType = row.CustomerType;
model.LinkMan = row.LinkMan;
model.LinkManTel = row.LinkManTel;
model.LinkManAddress = row.LinkManAddress;
//model.Status = row.Status;
model.Remark = row.Remark;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
//rtn = false;
//errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
//wws.Cell(rowIndex + 1, 15).Value = errorMessage;
//continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
//wws.Cell(rowIndex + 1, 15).Value = errorMessage;
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Customer entity = new WMS_Customer();
entity.Id = model.Id;
entity.CustomerCode = model.CustomerCode;
entity.CustomerShortName = model.CustomerShortName;
entity.CustomerName = model.CustomerName;
entity.CustomerType = model.CustomerType;
entity.LinkMan = model.LinkMan;
entity.LinkManTel = model.LinkManTel;
entity.LinkManAddress = model.LinkManAddress;
entity.Status = "有效";
entity.Remark = model.Remark;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Customer.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
//wws.Cell(rowIndex + 1, 15).Value = errorMessage;
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
//[ExpectedException(typeof(NullReferenceException), "FileName property is not set")]
public void GetColumnNames_throws_exception_when_filename_not_set()
{
var factory = new ExcelQueryFactory();
factory.GetColumnNames("");
}
private static void TestCase10_LinqToExcel()
{
//https://github.com/paulyoder/LinqToExcel
var table = ExcelQueryFactory.Worksheet("Info",
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx"));
var excelQueryFactory =
new ExcelQueryFactory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
"Test.xlsx"))
{
UsePersistentConnection = true,
ReadOnly = true,
DatabaseEngine = DatabaseEngine.Ace,
TrimSpaces = TrimSpacesType.Both,
StrictMapping = StrictMappingType.Both
};
var columnNames = excelQueryFactory.GetColumnNames("Info");
try
{
var infos = excelQueryFactory.Worksheet<Information>("Info").Where(p => p.Status == 1);
var Num = excelQueryFactory.WorksheetRange("A3", "B103", "Sheet1");
}
finally
{
excelQueryFactory.Dispose();
}
}
/// <summary>
/// Check if necessary columns ir available
/// </summary>
/// <param name="input">ExcelQueryFactory instance</param>
private void ValidateExcelColumns(ExcelQueryFactory input)
{
string notFound = "Not found";
var columnNames = input.GetColumnNames("Sheet1");
if (columnNames.Count() == 0)
{
throw new ArgumentException("Sheet1 not found");
}
if (columnNames.Where(o => o == Feature).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", Feature, notFound));
}
if (columnNames.Where(o => o == FinnishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", FinnishText, notFound));
}
if (columnNames.Where(o => o == SwedishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", SwedishText, notFound));
}
if (columnNames.Where(o => o == EnglishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", EnglishText, notFound));
}
}
private void ValidateExcelColumns(ExcelQueryFactory excel)
{
List<string> OriginalColumnNames = new List<string>()
{
"ModelName", "PropertyName", "English","Finnish","Swedish","English1", "Finnish1","Swedish1","English2",
"Finnish2", "Swedish2", "English3" , "Finnish3", "Swedish3"
};
var excelcolumns = excel.GetColumnNames("Main").ToList();
for (int i = 0; i < OriginalColumnNames.Count(); i++)
{
if (excelcolumns[i] != OriginalColumnNames[i])
{
throw new ArgumentException("Column names have been changed or changed order!!!");
}
}
}
public void GetColumnNames_throws_exception_when_filename_not_set()
{
var factory = new ExcelQueryFactory();
factory.GetColumnNames("");
}
private void MapColumns(ExcelQueryFactory excel)
{
var columns = excel.GetColumnNames("Main").ToList();
#region In caseof Linq to Excel update
//excel.AddMapping<WebElementExcelModel>(m => m.ModelName, columns[0]);
//excel.AddMapping<WebElementExcelModel>(m => m.PropertyName, columns[1]);
//// Prompt
//excel.AddMapping<WebElementExcelModel>(m => m.Label.EngText, columns[2]);
//excel.AddMapping<WebElementExcelModel>(m => m.Label.FinText, columns[3]);
//excel.AddMapping<WebElementExcelModel>(m => m.Label.SweText, columns[4]);
//// ControlText
//excel.AddMapping<WebElementExcelModel>(m => m.ControlText.EngText, columns[5]);
//excel.AddMapping<WebElementExcelModel>(m => m.ControlText.FinText, columns[6]);
//excel.AddMapping<WebElementExcelModel>(m => m.ControlText.SweText, columns[7]);
//// Help
//excel.AddMapping<WebElementExcelModel>(m => m.Help.EngText, columns[8]);
//excel.AddMapping<WebElementExcelModel>(m => m.Help.FinText, columns[9]);
//excel.AddMapping<WebElementExcelModel>(m => m.Help.SweText, columns[10]);
//// EnumText
//excel.AddMapping<WebElementExcelModel>(m => m.EnumText.EngText, columns[11]);
//excel.AddMapping<WebElementExcelModel>(m => m.EnumText.FinText, columns[12]);
//excel.AddMapping<WebElementExcelModel>(m => m.EnumText.SweText, columns[13]);
#endregion
excel.AddMapping<WebElementExcelModel>(m => m.ModelName, columns[0]);
excel.AddMapping<WebElementExcelModel>(m => m.PropertyName, columns[1]);
// Label
excel.AddMapping<WebElementExcelModel>(m => m.LabelEngText, columns[2]);
excel.AddMapping<WebElementExcelModel>(m => m.LabelFinText, columns[3]);
excel.AddMapping<WebElementExcelModel>(m => m.LabelSweText, columns[4]);
// ControlText
excel.AddMapping<WebElementExcelModel>(m => m.ControlEngText, columns[5]);
excel.AddMapping<WebElementExcelModel>(m => m.ControlFinText, columns[6]);
excel.AddMapping<WebElementExcelModel>(m => m.ControlSweText, columns[7]);
// Help
excel.AddMapping<WebElementExcelModel>(m => m.HelpEngText, columns[8]);
excel.AddMapping<WebElementExcelModel>(m => m.HelpFinText, columns[9]);
excel.AddMapping<WebElementExcelModel>(m => m.HelpSweText, columns[10]);
// EnumText
excel.AddMapping<WebElementExcelModel>(m => m.EnumEngText, columns[11]);
excel.AddMapping<WebElementExcelModel>(m => m.EnumFinText, columns[12]);
excel.AddMapping<WebElementExcelModel>(m => m.EnumSweText, columns[13]);
}
/// <summary>
/// Check if necessary columns ir available
/// </summary>
/// <param name="input">ExcelQueryFactory instance</param>
private void ValidateExcelColumns(ExcelQueryFactory input)
{
string notFound = "Not found";
var excelcolumns = input.GetColumnNames("Main").ToList();
List<string> OriginalColumnNames = new List<string> { "Feature", "Original", "English text", "Finish text", "Swedish text" };
for (int i = 0; i < OriginalColumnNames.Count(); i++)
{
if (excelcolumns[i] != OriginalColumnNames[i])
{
throw new ArgumentException("Column names have been changed or changed order!!!");
}
}
if (excelcolumns.Count() == 0)
{
throw new ArgumentException("Main not found");
}
if (excelcolumns.Where(o => o == Feature).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", Feature, notFound));
}
if (excelcolumns.Where(o => o == FinnishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", FinnishText, notFound));
}
if (excelcolumns.Where(o => o == SwedishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", SwedishText, notFound));
}
if (excelcolumns.Where(o => o == EnglishText).Count() == 0)
{
throw new ArgumentException(string.Format("{0}: {1}", EnglishText, notFound));
}
}
static void Main(string[] args)
{
//mongoimport --db DataStore --collection users --file File_2015-04-21-142804.json
var path = ConfigurationManager.AppSettings["Path"].ToString(); ;
var sheet = ConfigurationManager.AppSettings["Sheet"].ToString(); ;
var fileName = ConfigurationManager.AppSettings["FileName"].ToString();
var excel = new ExcelQueryFactory(path);
var columns = excel.GetColumnNames(sheet);
var list = excel.Worksheet(sheet).ToList();
var headers = new List<string>();
// get header
foreach (var c in columns)
{
if (!string.IsNullOrEmpty(c.ToString()))
{
var header = c.ToString().Trim().ToLower()
.Replace(" ", "")
.Replace("/", "")
.Replace("(", "")
.Replace(")", "");
headers.Add(header);
}
}
Console.WriteLine();
Console.WriteLine("[Convert Excel to JSON]");
Console.WriteLine("=======================");
Console.WriteLine("Path : {0}", path);
Console.WriteLine("Sheet : {0}", sheet);
Console.WriteLine("File Name : {0}", fileName);
Console.WriteLine();
Console.WriteLine("Generating...");
Console.WriteLine();
var outputFileName = string.Format("{0}_{1}.json", fileName, DateTime.Now.ToString("yyyy-MM-dd-HHmmss"));
var outFile = System.IO.File.CreateText(outputFileName);
foreach (var item in list)
{
// generate json
outFile.WriteLine("{");
for (int i = 0; i < headers.Count; i++)
{
if (i != (headers.Count - 1))
{
outFile.WriteLine(" \"{0}\": \"{1}\",", headers[i], item[i].ToString()
.Replace("\n", " ")
.Replace("\r", " ")
.Replace("\"", " ")
.Trim());
}
else
{
outFile.WriteLine(" \"{0}\": \"{1}\"", headers[i], item[i].ToString()
.Replace("\n", " ")
.Replace("\r", " ")
.Replace("\"", " ")
.Trim());
}
}
outFile.WriteLine("}");
}
outFile.Close();
Console.WriteLine(string.Format("{0} created", outputFileName));
Console.WriteLine("Press any key to continue...");
Console.Read();
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.FeedBillNum, "投料单号（业务）");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ReleaseBillNum, "投料单号（系统）");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Department, "投料部门");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.AssemblyPartCode, "总成物料");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.SubAssemblyPartCode, "投料物料");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Lot, "批次号");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.FeedQty, "投料数量");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.BoxQty, "箱数");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Capacity, "体积");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.InvName, "库房");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.SubInvId, "子库存");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Remark, "备注");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintStaus, "打印状态");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintDate, "打印时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintMan, "打印人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmStatus, "确认状态");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmMan, "确认人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmDate, "确认时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_Feed_ListModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_Feed_ListModel();
model.Id = row.Id;
model.FeedBillNum = row.FeedBillNum;
//model.ReleaseBillNum = row.ReleaseBillNum;
model.Department = row.Department;
model.AssemblyPartCode = row.AssemblyPartCode;
model.SubAssemblyPartCode = row.SubAssemblyPartCode;
model.FeedQty = row.FeedQty;
model.BoxQty = row.BoxQty;
model.Capacity = row.Capacity;
model.InvName = row.InvName;
model.Lot = row.Lot;
//model.SubInvId = row.SubInvId;
model.Remark = row.Remark;
//model.PrintStaus = row.PrintStaus;
//model.PrintDate = row.PrintDate;
//model.PrintMan = row.PrintMan;
//model.ConfirmStatus = row.ConfirmStatus;
//model.ConfirmMan = row.ConfirmMan;
//model.ConfirmDate = row.ConfirmDate;
//model.Attr1 = row.Attr1;
//model.Attr2 = row.Attr2;
//model.Attr3 = row.Attr3;
//model.Attr4 = row.Attr4;
//model.Attr5 = row.Attr5;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Feed_List entity = new WMS_Feed_List();
entity.Id = model.Id;
entity.FeedBillNum = model.FeedBillNum;
//entity.ReleaseBillNum = model.ReleaseBillNum;
//entity.ReleaseBillNum = "TL" + DateTime.Now.ToString("yyyyMMddHHmmssff");打印时生成
entity.Department = model.Department;
entity.AssemblyPartId = model.AssemblyPartId;
entity.SubAssemblyPartId = model.SubAssemblyPartId;
entity.FeedQty = model.FeedQty;
entity.BoxQty = model.BoxQty;
entity.Capacity = model.Capacity;
entity.InvId = model.InvId;
entity.Lot = model.Lot;
//entity.SubInvId = model.SubInvId;
entity.Remark = model.Remark;
entity.PrintStaus = "未打印";
//entity.PrintDate = model.PrintDate;
//entity.PrintMan = model.PrintMan;
entity.ConfirmStatus = "未确认";
//entity.ConfirmMan = model.ConfirmMan;
//entity.ConfirmDate = model.ConfirmDate;
//entity.Attr1 = model.Attr1;
//entity.Attr2 = model.Attr2;
//entity.Attr3 = model.Attr3;
//entity.Attr4 = model.Attr4;
//entity.Attr5 = model.Attr5;
//entity.CreatePerson = model.CreatePerson;
//entity.CreateTime = model.CreateTime;
//entity.ModifyPerson = model.ModifyPerson;
//entity.ModifyTime = model.ModifyTime;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Feed_List.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
/// <summary>
/// Get the list of column names from the excel file
/// </summary>
/// <returns></returns>
public List<string> GetImportFileColumnNames(string FileName, string SheetName)
{
try
{
var excelSheet = new ExcelQueryFactory(FileName);
return excelSheet.GetColumnNames(SheetName).ToList();
}
catch
{
return new List<string>();
}
}