private void ReadCurrencie_OnClick(object sender, RoutedEventArgs e)
{
Task.Factory.StartNew(() =>
{
var excelEdit = new ExcelEdit();
excelEdit.Open(AppDomain.CurrentDomain.BaseDirectory + "Currencie.xlsx");
var sheet = excelEdit.GetSheet("default");
var rowCount = sheet.UsedRange.Rows.Count;
for (int i = 1; i <= rowCount; i++) //
{
if (sheet.Rows[i] == null)
{
continue;
}
var curNameList = sheet.Cells[i, "A"].Value.ToString().Split('_');
foreach (var s in curNameList)
{
if (!bibiList.Contains(s))
{
bibiList.Add(s);
}
}
}
_mainModel.CurrencieNum = bibiList.Count;
bibiList = bibiList.OrderBy(x => x.ToLower()).ToList();
_mainModel.Imported = true;
_mainModel.UpdateState(_mainModel.State);
});
}
public static int getDataInRow <T>(KeyValuePair <string, object> param)
{
Dictionary <string, string> fileinfo = getFileInfo <T>();
ExcelEdit excel = new ExcelEdit();
Dictionary <string, int> location = getColumn <T>();
string fileName = fileinfo["ExcelName"];
string sheetName = fileinfo["SheetName"];
string str = System.Windows.Forms.Application.StartupPath;
if (!File.Exists(str + @"\" + fileName))
{
return(0);
}
excel.Open(str + @"\" + fileName);
excel.ws = excel.GetSheet(sheetName);
int rows = excel.ws.UsedRange.CurrentRegion.Rows.Count;
int crow = 0;
for (int i = 2; i < rows + 1; i++)
{
Range titleRange = excel.ws.Range[excel.ws.Cells[i, location[param.Key]], excel.ws.Cells[i, location[param.Key]]];//选中标题
if (titleRange.Value2 != null)
{
var gpt = typeof(T).GetProperties();
var mt = gpt.FirstOrDefault(c => c.Name == param.Key);
if (mt != null)
{
object obj = new object();
if (titleRange.Value != null)
{
Type tp = mt.PropertyType;
dynamic value = titleRange.Value;
switch (param.Key)
{
case "CreateDate":
tp = typeof(DateTime);
value = DateTime.Now;
break;
}
obj = Convert.ChangeType(value, tp);
}
else
{
obj = titleRange.Value;
}
if (obj.Equals(param.Value))
{
crow = i;
break;
}
}
}
}
excel.Close();
return(crow);
}
/// <summary>
/// 回水导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button7_Click(object sender, EventArgs e)
{
string FileName = "";
SaveFileDialog sfd = new SaveFileDialog();
// sfd.InitialDirectory = "E:\\";
sfd.FileName = "回水统计";
sfd.Filter = "xlsx文件(*.xlsx)|*.xlsx|xls文件(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
FileName = sfd.FileName;
}
else
{
return;
}
ExcelEdit Edit = new ExcelEdit();
Edit.InsertTable(LVtoTB(listView4), "Sheet1", 2, 1, true);
Edit.SaveAs(FileName);
Edit.Close();
}
private void executeToExcel(Record record)
{
string path = Application.StartupPath + ConfigurationManager.AppSettings["excelPath"].ToString();
File.Copy(path, Application.StartupPath + @"\test.xlsx", true);
ExcelEdit excelOperator = new ExcelEdit();
excelOperator.Open(Application.StartupPath + @"\test.xlsx");
Microsoft.Office.Interop.Excel.Worksheet sheet1 = excelOperator.GetSheet("Sheet1");
sheet1.Cells[4, 5].Value = record.Operator;
sheet1.Cells[4, 8].Value = record.CreateTime.ToString("yyyy-MM-dd");
sheet1.Cells[6, 2].Value = record.SeriesNum;
sheet1.Cells[6, 5].Value = record.ProName;
sheet1.Cells[6, 8].Value = record.Result == 1 ? "合格" : "不合格";
for (int i = 0; i < record.RecordDetailList.Count; i++)
{
sheet1.Cells[i + 11, 2].Value = record.RecordDetailList[i].Index;
sheet1.Cells[i + 11, 3].Value = record.RecordDetailList[i].TestValue + " N·m";
sheet1.Cells[i + 11, 4].Value = record.RecordDetailList[i].Standard + " N·m";
sheet1.Cells[i + 11, 5].Value = record.RecordDetailList[i].Difference;
sheet1.Cells[i + 11, 6].Value = record.RecordDetailList[i].Percent + "%";
sheet1.Cells[i + 11, 7].Value = record.Result == 1 ? "合格" : "不合格";
sheet1.Cells[i + 11, 8].Value = record.RecordDetailList[i].Upper + " N·m";
sheet1.Cells[i + 11, 9].Value = record.RecordDetailList[i].Lower + " N·m";
}
if (ConfigurationManager.AppSettings["printMode"].ToString().Equals("preview"))
{
sheet1.PrintPreview();
}
else if (ConfigurationManager.AppSettings["printMode"].ToString().Equals("print"))
{
sheet1.PrintOutEx();
}
excelOperator.Save();
excelOperator.Close();
GC.Collect();
}
private void ProcessExcel()
{
Log("导出Excel文件...");
ExcelEdit ed = new ExcelEdit();
ed.Create();
int index = 1;
ed.SetCellValue("Sheet1", index, 1, "图序号范围");
ed.SetCellValue("Sheet1", index, 2, "面料编号");
ed.SetCellValue("Sheet1", index, 3, "面料名称");
ed.SetCellValue("Sheet1", index, 4, "总米数");
ed.SetCellValue("Sheet1", index, 5, "实际米数");
index++;
// 面料名称、面料编号、规则名列表、文件路径列表、米数列表、图号列表
foreach (var item in FacePathTuples)
{
if (item.Item3.Count == 0)
{
continue;
}
string strnumrange = item.Item6.Count >= 2 ? item.Item6.First() + "-" + item.Item6.Last() : item.Item6[0];
ed.SetCellValue("Sheet1", index, 1, strnumrange);
double totalmeter = 0.0;
foreach (var fi in item.Item5)
{
totalmeter += fi;
}
ed.SetCellValue("Sheet1", index, 2, item.Item2);
ed.SetCellValue("Sheet1", index, 3, item.Item1);
ed.SetCellValue("Sheet1", index, 4, totalmeter);
ed.SetCellValue("Sheet1", index, 5, item.Item5.Count * Ratio + totalmeter);
index++;
}
string excelname = Url.Substring(Url.LastIndexOf('\\') + 1);
ExcelUrl = Url + "\\" + excelname + ".xlsx";
ed.SaveAs(ExcelUrl);
ed.Close();
Log("导出Excel文件结束");
if (MessageBox.Show("是否更新库存表?", "更新", MessageBoxButton.OKCancel) == MessageBoxResult.OK)
{
bool b0 = System.IO.File.Exists(ExcelUrl);
if (!b0)
{
Log("找不到样表文件" + ExcelUrl + "更新失败");
return;
}
string stockexcel = Url + "\\库存.xlsx";
bool b2 = System.IO.File.Exists(stockexcel);
if (!b2)
{
Log("找不到库存表文件" + stockexcel + "更新失败");
return;
}
try
{
if (!CreateStock.Create(stockexcel, ExcelUrl, ""))
{
Log("更新库存表失败！请检查文件内容有效性！");
}
else
{
Log("更新库存表成功！");
}
}
catch (Exception)
{
Log("更新库存表失败！请检查文件内容有效性！");
}
}
}
public void Process(int startindex, float ratio)
{
StartIndex = startindex;
Ratio = ratio;
Log("查找文件...");
List <string> files = new List <string>();
GetAllFiles(Url, ref files);
if (files.Count == 0)
{
Log("没有找到文件，结束");
return;
}
Log("查找小样文件...");
List <FileInfo> fileinfos = new List <FileInfo>();
int totalindex = StartIndex;
foreach (var f in files)
{
FileInfo fi;
if (ParseFileName(Path.GetFileNameWithoutExtension(f), out fi))
{
fi.Url = f;
fi.Number = totalindex.ToString() + "号";
fileinfos.Add(fi);
totalindex++;
}
}
if (fileinfos.Count == 0)
{
Log("没有找到米样文件，结束");
return;
}
Log("找到" + fileinfos.Count.ToString() + "个小样文件，开始重命名...");
foreach (var item in fileinfos)
{
string newname = item.Number + "-" + item.ReguName + Path.GetExtension(item.Url);
Rename(item.Url, newname);
}
Log("重命名结束...");
Log("导出Excel文件...");
{
ExcelEdit ed = new ExcelEdit();
ed.Create();
int index = 1;
ed.SetCellValue("Sheet1", index, 1, "图号");
ed.SetCellValue("Sheet1", index, 2, "描述");
index++;
foreach (var item in fileinfos)
{
ed.SetCellValue("Sheet1", index, 1, item.Number);
string disc = item.Format + item.Disc + "-";
foreach (var fn in item.FaceNameCount)
{
disc += fn.Item1.Item1 + "(" + fn.Item2.ToString() + ")个、";
}
disc.TrimEnd('、');
ed.SetCellValue("Sheet1", index, 2, disc);
index++;
}
string excelname = Url.Substring(Url.LastIndexOf('\\') + 1);
ExcelUrl1 = Url + "\\小样" + excelname + "1.xlsx";
ed.SaveAs(ExcelUrl1);
ed.Close();
}
{
int nCount = Config.Settings.lstFaceInfo.Count;
List <Tuple <string, string, List <int> > > sortfacenamecount = new List <Tuple <string, string, List <int> > >();
for (int i = 0; i < nCount; i++)
{
sortfacenamecount.Add(new Tuple <string, string, List <int> >(Config.Settings.lstFaceInfo[i].Index.ToString() + "#", Config.Settings.lstFaceInfo[i].FaceName, new List <int>()));
}
foreach (var item in fileinfos)
{
foreach (var fn in item.FaceNameCount)
{
var res = sortfacenamecount.Where(s => s.Item1 == fn.Item1.Item2);
if (res.Count() != 0)
{
res.ElementAt(0).Item3.Add(fn.Item2);
}
else
{
Log("文件" + item.Url + "的面料类型" + fn.Item1 + "没有定义");
}
}
}
ExcelEdit ed = new ExcelEdit();
ed.Create();
int index = 1;
ed.SetCellValue("Sheet1", index, 1, "面料编号");
ed.SetCellValue("Sheet1", index, 2, "面料名称");
ed.SetCellValue("Sheet1", index, 3, "个数");
ed.SetCellValue("Sheet1", index, 4, "实际米数");
index++;
foreach (var item in sortfacenamecount)
{
if (item.Item3.Count == 0)
{
continue;
}
ed.SetCellValue("Sheet1", index, 1, item.Item1);
ed.SetCellValue("Sheet1", index, 2, item.Item2);
int totalcount = 0;
foreach (var fn in item.Item3)
{
totalcount += fn;
}
ed.SetCellValue("Sheet1", index, 3, totalcount);
ed.SetCellValue("Sheet1", index, 4, totalcount * Ratio);
index++;
}
string excelname = Url.Substring(Url.LastIndexOf('\\') + 1);
ExcelUrl2 = Url + "\\小样" + excelname + "2.xlsx";
ed.SaveAs(ExcelUrl2);
ed.Close();
}
Log("导出Excel文件结束");
{
if (MessageBox.Show("是否更新库存表?", "更新", MessageBoxButton.OKCancel) == MessageBoxResult.OK)
{
bool b0 = System.IO.File.Exists(ExcelUrl2);
if (!b0)
{
Log("找不到小样表文件" + ExcelUrl2 + "，更新失败");
return;
}
string stockexcel = Url + "\\库存.xlsx";
bool b2 = System.IO.File.Exists(stockexcel);
if (!b2)
{
Log("找不到库存表文件" + stockexcel + "更新失败");
return;
}
try
{
if (!CreateStock.Create(stockexcel, "", ExcelUrl2))
{
Log("更新库存表失败！请检查文件内容有效性！");
}
else
{
Log("更新库存表成功！");
}
}
catch (Exception)
{
Log("更新库存表失败！请检查文件内容有效性！");
}
}
}
}
public Form1()
{
excelEdit = new ExcelEdit();
InitializeComponent();
// GetConnect();//
}
/// <summary>
/// 从文件获取数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static void getListFromFile <T>()
{
string className = typeof(T).Name;
Dictionary <string, string> fileinfo = getFileInfo <T>();
string fileName = fileinfo["ExcelName"];
string sheetName = fileinfo["SheetName"];
List <T> Sourcelist = new List <T>();
if (!PubulicData.sourceData.Keys.Contains(className))
{
PubulicData.sourceData.Add(className, Sourcelist);
}
else
{
PubulicData.sourceData[className] = Sourcelist;
}
ExcelEdit excel = new ExcelEdit();
Dictionary <string, int> location = getColumn <T>();
string str = System.Windows.Forms.Application.StartupPath;
if (!File.Exists(str + @"\" + fileName))
{
return;
}
excel.Open(str + @"\" + fileName);
excel.ws = excel.GetSheet(sheetName);
int rows = excel.ws.UsedRange.CurrentRegion.Rows.Count;
for (int i = 2; i < rows + 1; i++)
{
T bd = Activator.CreateInstance <T>();;
foreach (string j in location.Keys)
{
Range titleRange = excel.ws.Range[excel.ws.Cells[i, location[j]], excel.ws.Cells[i, location[j]]];//选中标题
if (titleRange.Value2 != null)
{
var gpt = typeof(T).GetProperties();
var mt = gpt.FirstOrDefault(c => c.Name == j);
if (mt != null)
{
object obj = new object();
if (titleRange.Value != null)
{
Type tp = mt.PropertyType;
dynamic value = titleRange.Value;
switch (j)
{
case "CreateDate":
tp = typeof(DateTime);
value = DateTime.Now;
break;
}
obj = Convert.ChangeType(value, tp);
}
else
{
obj = titleRange.Value;
}
mt.SetValue(bd, obj);
}
}
}
Sourcelist.Add(bd);
}
excel.Close();
}
/// <summary>
/// 保存数据到文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sourceData"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
public static void saveDataToFile <T>(List <T> sourceData)
{
Dictionary <string, string> fileinfo = getFileInfo <T>();
string fileName = fileinfo["ExcelName"];
string sheetName = fileinfo["SheetName"];
ExcelEdit excel = new ExcelEdit();
string str = System.Windows.Forms.Application.StartupPath;
Worksheet ws = null;
if (!Directory.Exists(str))
{
Directory.CreateDirectory(str);
}
if (!File.Exists(str + @"\" + fileName))
{
excel.mFilename = str + @"\" + fileName;
excel.Create();
excel.AddSheet(sheetName);
}
else
{
excel.Open(str + @"\" + fileName);
}
if (excel.GetSheet(sheetName) == null)
{
ws = excel.AddSheet(sheetName);
}
else
{
ws = excel.GetSheet(sheetName);
}
int y = 2;
foreach (T item in sourceData)
{
int x = 1;
foreach (PropertyInfo obj in item.GetType().GetProperties())
{
object value = null;
if (y == 2)
{
value = obj.Name.ToString();
excel.SetCellValue(ws, 1, x, value);
}
value = item.GetType().GetProperty(obj.Name.ToString()).GetValue(item);
Range titleRange = ws.Range[ws.Cells[y, x],
ws.Cells[y, x]]; //选中标题
titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
if (titleRange.Value != null && value != null && value.Equals(titleRange.Value))
{
x++;
continue;
}
else
{
excel.SetCellValue(ws, y, x, value);
x++;
}
}
y++;
}
excel.wb.RefreshAll();
if (File.Exists(str + @"\" + fileName))
{
excel.Save();
}
else
{
excel.SaveAs(str + @"\" + fileName);
}
excel.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
var table = ExcelEdit.ImportExcelToDataTable("sheet", "filepath");
}
