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]); } }