public void OpenTest_Create()
{
using (var excel = new OperateExcel())
{
excel.Open();
excel.Write(Cell.R1C1(1, 1), "1", "2", "3");
excel.Write(Cell.R1C1(101, 101), "あいうえおかきくけこ");
excel.Save(CalcPath("test.xlsx"));
}
Assert.IsTrue(File.Exists(CalcPath("test.xlsx")));
using (var excel = new OperateExcel())
{
excel.Open(CalcPath("test.xlsx"));
Assert.AreEqual(1, excel.Read <int>(Cell.R1C1(1, 1)));
Assert.AreEqual("2", excel.Read <string>(Cell.R1C1(1, 2)));
Assert.AreEqual(3.0, excel.Read <double>(Cell.R1C1(1, 3)));
Assert.AreEqual("あいうえおかきくけこ", excel.Read <string>(Cell.R1C1(101, 101)));
excel.Write(Cell.R1C1(4, 4), "さしすせそ");
excel.Save();
}
using (var excel = new OperateExcel())
{
excel.Open(CalcPath("test.xlsx"));
Assert.AreEqual("さしすせそ", excel.Read <string>(Cell.R1C1(4, 4)));
}
}
private void generateEmaCsv(List <FurtherIssueModel> fList)
{
string emaFileDir = getEmaFileDir(fList);
//need to confirm
string csvFileName = string.Format("{0}\\WRT_QUA_{1}_Korea.csv", emaFileDir, DateTime.Today.ToString("ddMMMyyyy", new CultureInfo("en-US")));
List <List <string> > csvResList = new List <List <string> >();
List <string> head = new List <string>();
int line = 0;
head.Add("Logical_Key");
head.Add("Secondary_ID");
head.Add("Secondary_ID_Type");
head.Add("EH_Issue_Quantity");
head.Add("Issue_Quantity");
csvResList.Add(head);
if (File.Exists(csvFileName))
{
line = ReadLogic(csvFileName);
}
csvResList.AddRange(fList.Select((t, i) => new List <string>
{
(i + 1 + line).ToString(), t.Old_Isin, "ISIN", "N", t.New_Quanity
}));
if (File.Exists(csvFileName))
{
csvResList.RemoveAt(0);//remove head
OperateExcel.WriteToCSV(csvFileName, csvResList, FileMode.Append);
}
else
{
OperateExcel.WriteToCSV(csvFileName, csvResList, FileMode.Create);
}
TaskResultList.Add(new TaskResultEntry(Path.GetFileNameWithoutExtension(csvFileName), "", csvFileName, FileProcessType.Other));
}
public ActionResult DownLoad()
{
object con = new SystemCache().GetCache("stockout_query");
if (con == null)
{
return(new EmptyResult());
}
var dataList = _sto_StockOutBusiness.GetStockOutList(
$"{con.GetPropertyValue("StoreId")}",
$"{con.GetPropertyValue("OutNo")}",
$"{con.GetPropertyValue("B_OutDate")}",
$"{con.GetPropertyValue("E_OutDate")}",
new Pagination()
{
PageRows = Int32.MaxValue
});
string outputFileName = $"出库-{DateTime.Now.ToString("yyyyMMddHHmmss")}";
DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("仓库"));
dataTable.Columns.Add(new DataColumn("出库单号"));
dataTable.Columns.Add(new DataColumn("出库时间"));
dataTable.Columns.Add(new DataColumn("出库操作员"));
dataTable.Columns.Add(new DataColumn("材料编码"));
dataTable.Columns.Add(new DataColumn("材料名称"));
dataTable.Columns.Add(new DataColumn("规格"));
dataTable.Columns.Add(new DataColumn("单位"));
dataTable.Columns.Add(new DataColumn("数量"));
dataTable.Columns.Add(new DataColumn("出库类型"));
dataTable.Columns.Add(new DataColumn("申请编号"));
dataTable.Columns.Add(new DataColumn("备注"));
foreach (StockOutListItem item in dataList)
{
DataRow newRow = dataTable.NewRow();
newRow[0] = item.StoreName;
newRow[1] = item.OutNo;
newRow[2] = item.OutDate;
newRow[3] = item.OutOperID;
newRow[4] = item.MatNo;
newRow[5] = item.MatName;
newRow[6] = item.GuiGe;
newRow[7] = item.UnitName;
newRow[8] = item.Quantity;
newRow[9] = item.OutType == 0? "工程领料" : "普通领料";
newRow[10] = item.ApplyNo;
newRow[11] = item.Context;
dataTable.Rows.Add(newRow);
}
OperateExcel.ExportToExcel(System.Web.HttpContext.Current, dataTable, outputFileName);
return(new EmptyResult());
}
public void OpenTest_Open()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
excel.SelectSheet("テスト");
Assert.AreEqual("ほげほげ", excel.Read <string>(Cell.A1("B2")));
}
}
//导出Excel
public ExportExcelModel ExportExcel(UserParams userInfo)
{
ExportExcelModel exportExcelModel = new ExportExcelModel();
DataTable dtUerInfo = userInfoDAL.SearchInfo(userInfo);
exportExcelModel.filename = OperateExcel.ExportToExcel(dtUerInfo, DateTime.Now.ToString("yyyyMMddHHmmss"));
return(exportExcelModel);
}
public void ReadRowTestOne()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
excel.SelectSheet("Sheet2");
var row = excel.ReadRow(4, ColumnsRange.A1("B:B"));
Assert.AreEqual("あ", row[0]);
Assert.AreEqual(1, row.Count);
}
}
public void FindTest_Table()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
int row, column;
excel.SelectSheet("Sheet2");
Assert.IsTrue(excel.Find(CellsRange.A1("B2:E5"), "え", out row, out column));
Assert.AreEqual(4, row);
Assert.AreEqual(5, column);
}
}
public void CopySheetTestReal()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
excel.CopySheet("コピーした奴", "検索", OperateExcel.SheetPosition.First);
excel.CreateSheet("新規作成奴", OperateExcel.SheetPosition.AfterCurrentSheet);
excel.SelectSheet("新規作成奴");
excel.Write(Cell.A1("A1"), "おおおお");
excel.Save("testCopy.xlsx");
}
}
public void GetSheetNamesTest()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
var names = excel.GetSheetNames();
Assert.AreEqual(4, names.Count);
Assert.AreEqual("Sheet1", names[0]);
Assert.AreEqual("テスト", names[1]);
Assert.AreEqual("Sheet2", names[2]);
Assert.AreEqual("検索", names[3]);
}
}
public void ReadRowTest()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
excel.SelectSheet("Sheet2");
var row = excel.ReadRow(4, ColumnsRange.A1("B:F"));
Assert.AreEqual("あ", row[0]);
Assert.AreEqual("い", row[1]);
Assert.AreEqual("う", row[2]);
Assert.AreEqual("え", row[3]);
Assert.AreEqual("", row[4]);
Assert.AreEqual(5, row.Count);
}
}
public void FindTest()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
int row, column;
excel.SelectSheet("検索");
Assert.IsTrue(excel.Find(CellsRange.ColumnRange("C"), "中川義男", out row, out column));
Assert.AreEqual(9, row);
Assert.AreEqual(3, column);
Assert.IsFalse(excel.Find(CellsRange.ColumnRange("C"), "山本", out row, out column));
Assert.IsTrue(excel.Find(CellsRange.ColumnRange("C"), "山本", out row, out column, true));
Assert.AreEqual(12, row);
Assert.AreEqual(3, column);
}
}
public void CopyAndInsertRowTest()
{
File.Copy("template.xlsx", CalcPath("test.xlsx"));
using (var excel = new OperateExcel())
{
excel.Open(CalcPath("test.xlsx"));
excel.CopyAndInsertRow(1, 2, 2, "Sheet2");
excel.CopyAndInsertRow(2, 2, 1);
Assert.AreEqual("1", excel.Read <string>(Cell.R1C1(2, 2)));
Assert.AreEqual("4", excel.Read <string>(Cell.R1C1(2, 5)));
Assert.AreEqual("あ", excel.Read <string>(Cell.R1C1(3, 2)));
Assert.AreEqual("あ", excel.Read <string>(Cell.R1C1(4, 2)));
Assert.AreEqual("え", excel.Read <string>(Cell.R1C1(3, 5)));
Assert.AreEqual("え", excel.Read <string>(Cell.R1C1(4, 5)));
}
}
public void IsStrikethroughTest()
{
using (var excel = new OperateExcel())
{
excel.Open("template.xlsx");
//全て取り消し線なし
Assert.AreEqual(false, excel.IsStrikethrough(Cell.A1("B9")));
//セルごと取り消し線
Assert.AreEqual(true, excel.IsStrikethrough(Cell.A1("B10")));
//セル内の一部を取り消し線
Assert.AreEqual(false, excel.IsStrikethrough(Cell.A1("B11")));
//セル内のテキストを全て取り消し線
Assert.AreEqual(true, excel.IsStrikethrough(Cell.A1("B12")));
//改行以外を全て取り消し線
Assert.AreEqual(false, excel.IsStrikethrough(Cell.A1("B13")));
Assert.AreEqual(true, excel.IsStrikethrough(CellsRange.A1("B14:C14")));
Assert.AreEqual(false, excel.IsStrikethrough(CellsRange.A1("B15:C15")));
}
}
public void CopySheetTest()
{
using (var excel = new OperateExcel())
{
excel.Open();
excel.Write(Cell.A1("B2"), "あいうえお");
excel.CopySheet("first", "Sheet1", OperateExcel.SheetPosition.First);
var names = excel.GetSheetNames();
Assert.AreEqual(2, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
excel.CopySheet("last", "Sheet1", OperateExcel.SheetPosition.Last);
names = excel.GetSheetNames();
Assert.AreEqual(3, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
Assert.AreEqual("last", names[2]);
excel.CopySheet("after", "Sheet1", OperateExcel.SheetPosition.AfterCurrentSheet);
names = excel.GetSheetNames();
Assert.AreEqual(4, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
Assert.AreEqual("after", names[2]);
Assert.AreEqual("last", names[3]);
excel.CopySheet("before", "Sheet1", OperateExcel.SheetPosition.BeforeCurrentSheet);
names = excel.GetSheetNames();
Assert.AreEqual(5, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("before", names[1]);
Assert.AreEqual("Sheet1", names[2]);
Assert.AreEqual("after", names[3]);
Assert.AreEqual("last", names[4]);
foreach (var name in names)
{
excel.SelectSheet(name);
Assert.AreEqual("あいうえお", excel.Read <string>(Cell.A1("B2")));
}
}
}
private void generateEmaCsv(List <FurtherIssueModel> fList)
{
string emaFileDir = getEmaFileDir(fList);
//need to confirm
string csvFileName = string.Format("{0}\\WRT_QUA_{1}_Korea.csv", emaFileDir, DateTime.Today.ToString("ddMMMyyyy", new CultureInfo("en-US")));
List <List <string> > csvResList = new List <List <string> >();
List <string> head = new List <string>();
int line = 0;
head.Add("Logical_Key");
head.Add("Secondary_ID");
head.Add("Secondary_ID_Type");
head.Add("EH_Issue_Quantity");
head.Add("Issue_Quantity");
csvResList.Add(head);
if (File.Exists(csvFileName))
{
line = readLogic(csvFileName);
}
for (int i = 0; i < fList.Count; i++)
{
List <string> term = new List <string>();
term.Add((i + 1 + line).ToString());
term.Add(fList[i].Old_Isin);
term.Add("ISIN");
term.Add("N");
term.Add(fList[i].New_Quanity);
csvResList.Add(term);
}
if (File.Exists(csvFileName))
{
csvResList.RemoveAt(0);//remove head
OperateExcel.WriteToCSV(csvFileName, csvResList, FileMode.Append);
}
else
{
OperateExcel.WriteToCSV(csvFileName, csvResList, FileMode.Create);
}
AddResult(Path.GetFileNameWithoutExtension(csvFileName), csvFileName, "");
}
/// <summary>
/// 下载当前查询的库存列表
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public ActionResult DownLoad(string key)
{
string param = $"{new SystemCache().GetCache("stock_query")}";
var dataList = _sto_StockBusiness.GetDataList(param, new Pagination()
{
PageRows = int.MaxValue
});
string outputFileName = $"库存-{DateTime.Now.ToString("yyyyMMddHHmmss")}";
DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("仓库"));
dataTable.Columns.Add(new DataColumn("编码"));
dataTable.Columns.Add(new DataColumn("名称"));
dataTable.Columns.Add(new DataColumn("分类"));
dataTable.Columns.Add(new DataColumn("规格"));
dataTable.Columns.Add(new DataColumn("单位"));
dataTable.Columns.Add(new DataColumn("数量"));
dataTable.Columns.Add(new DataColumn("单价"));
dataTable.Columns.Add(new DataColumn("上限"));
dataTable.Columns.Add(new DataColumn("下限"));
foreach (Sto_StockBusiness.Stock stock in dataList)
{
DataRow newRow = dataTable.NewRow();
newRow[0] = stock.StoreName;
newRow[1] = stock.MatNo;
newRow[2] = stock.MatName;
newRow[3] = stock.BigClassName;
newRow[4] = stock.GuiGe;
newRow[5] = stock.UnitName;
newRow[6] = stock.Quantity;
newRow[7] = stock.Price;
newRow[8] = stock.MaxStoreQuantity;
newRow[9] = stock.WarnStoreQuantity;
dataTable.Rows.Add(newRow);
}
OperateExcel.ExportToExcel(System.Web.HttpContext.Current, dataTable, outputFileName);
return(new EmptyResult());
}
public void CreateSheetTest()
{
using (var excel = new OperateExcel())
{
excel.Open();
excel.CreateSheet("first", OperateExcel.SheetPosition.First);
var names = excel.GetSheetNames();
Assert.AreEqual(2, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
excel.CreateSheet("last", OperateExcel.SheetPosition.Last);
names = excel.GetSheetNames();
Assert.AreEqual(3, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
Assert.AreEqual("last", names[2]);
excel.CreateSheet("after", OperateExcel.SheetPosition.AfterCurrentSheet);
names = excel.GetSheetNames();
Assert.AreEqual(4, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("Sheet1", names[1]);
Assert.AreEqual("after", names[2]);
Assert.AreEqual("last", names[3]);
excel.CreateSheet("before", OperateExcel.SheetPosition.BeforeCurrentSheet);
names = excel.GetSheetNames();
Assert.AreEqual(5, names.Count);
Assert.AreEqual("first", names[0]);
Assert.AreEqual("before", names[1]);
Assert.AreEqual("Sheet1", names[2]);
Assert.AreEqual("after", names[3]);
Assert.AreEqual("last", names[4]);
}
}
