public void Test_Write_MultiTheadWrite_List() { Parallel.For(0, 10, (i) => { var data = new List <Product>(); for (int j = 0; j < 100; j++) { data.Add(new Product { SysNo = j, ProductId = "编号:" + j, Price = Convert.ToDecimal(j) / 3, CreateTime = DateTime.Now }); } try { NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("山丘"); excel.Write(data, 0, 0, 0); Console.WriteLine(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + "_" + i.ToString() + ".xls")); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + "_" + i.ToString() + ".xls")); } catch (Exception ex) { Console.WriteLine(ex.Message); throw; } }); }
public void Test_Write_Dynamic_List() { NpoiExcelHelper excel = new NpoiExcelHelper(true); excel.CreateSheet(); excel.Write(new[] { "第一列", "第二列", "三", "肆" }, 0, 0, 0); var names = RandomHelper.GenString(RandomHelper.CharSchemeEnum.Chinese, 500, 1000); var data = new List <dynamic>(); for (int i = 1; i < 60000; i++) { dynamic o = new ExpandoObject(); o.A = "Hello World"; o.aasd = DateTime.Now.ToString("yyyy-MM-dd"); o.c223C = 1.1m; o.asd21D = 2.2d; data.Add(o); } excel.Write(data, 0, 1, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xlsx")); }
public void MyTestMethod() { var file = @"C:\Users\Administrator\Downloads\Documents\Mios开发\线下支付宝还款导入2007.xlsx"; var data = NpoiExcelHelper.Read(file, 0, 1, 0); Console.WriteLine(JsonConvert.SerializeObject(data)); }
public void Test_writeAnonymousList() { var res = new List <Product>(); for (int i = 0; i < 10000; i++) { res.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } var data = res.Select(d => new { B = d.ProductId, A = d.SysNo, c = d.Price, }).ToList(); NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeAnonymousList.xls", ResultFile)); }
public void Test_wirteDynamicList() { NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(new[] { "第一列", "第二列", "三", "肆" }, 0, 0, 0); var data = new List <dynamic>(); for (int i = 0; i < 10000; i++) { dynamic o = new ExpandoObject(); o.A = "Hello World"; o.B = DateTime.Now.ToString("yyyy-MM-dd"); o.C = 1.1m; o.D = 2.2d; data.Add(o); } excel.Write(data, 0, 1, 0); excel.Export(string.Format("{0}Test_wirteDynamicList.xls", ResultFile)); }
public void Test_writeCustomerTypeListObject() { var data = new List <Product>(); for (int i = 0; i < 10000; i++) { data.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } NpoiExcelHelper excel = new NpoiExcelHelper(); /* * 注意 : * 使用 用户自定义实体类型写入Excel时,会自动根据 属性名称字母排序数据列 */ excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeCustomerTypeListObject.xls", ResultFile)); }
public void Test_Write_Customer_Object_List() { var data = new List <Product>(); for (int i = 0; i < 10000; i++) { data.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet(); /* * 注意 : * 使用 用户自定义实体类型写入Excel时,会自动根据 属性名称字母排序数据列 */ excel.Write(data, 0, 0, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_Write_By_SheetName_Anonymous_List() { var res = new List <Product>(); for (int i = 0; i < 10000; i++) { res.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } var data = res.Select(d => new { B = d.ProductId, A = d.SysNo, c = d.Price, }).ToList(); NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("金蛇狂舞"); excel.Write(data, "金蛇狂舞", 1, 1); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_Write_col_30_row_300000() { NpoiExcelHelper excel = new NpoiExcelHelper(true); excel.CreateSheet(); for (int i = 0; i < 30; i++) { excel.Write("第 " + i + " 列", 0, 0, i); } var data = new List <dynamic>(); for (int i = 1; i < 100000; i++) { dynamic o = new ExpandoObject(); o.A1 = RandomHelper.GenNumber(0, 10000); o.A2 = RandomHelper.GenNumber(0, 10000); o.A3 = RandomHelper.GenNumber(0, 10000); o.A4 = RandomHelper.GenNumber(0, 10000); o.A5 = RandomHelper.GenNumber(0, 10000); o.A6 = RandomHelper.GenNumber(0, 10000); o.A7 = RandomHelper.GenNumber(0, 10000); o.A8 = RandomHelper.GenNumber(0, 10000); o.A9 = RandomHelper.GenNumber(0, 10000); o.A10 = RandomHelper.GenNumber(0, 10000); o.A11 = RandomHelper.GenNumber(0, 10000); o.A12 = RandomHelper.GenNumber(0, 10000); o.A13 = RandomHelper.GenNumber(0, 10000); o.A14 = RandomHelper.GenNumber(0, 10000); o.A15 = RandomHelper.GenNumber(0, 10000); o.A16 = RandomHelper.GenNumber(0, 10000); o.A17 = RandomHelper.GenNumber(0, 10000); o.A18 = RandomHelper.GenNumber(0, 10000); o.A19 = RandomHelper.GenNumber(0, 10000); o.A20 = RandomHelper.GenNumber(0, 10000); o.A21 = RandomHelper.GenNumber(0, 10000); o.A22 = RandomHelper.GenNumber(0, 10000); o.A23 = RandomHelper.GenNumber(0, 10000); o.A24 = RandomHelper.GenNumber(0, 10000); o.A25 = RandomHelper.GenNumber(0, 10000); o.A26 = RandomHelper.GenNumber(0, 10000); o.A27 = RandomHelper.GenNumber(0, 10000); o.A28 = RandomHelper.GenNumber(0, 10000); o.A29 = RandomHelper.GenNumber(0, 10000); o.A30 = RandomHelper.GenNumber(0, 10000); data.Add(o); } excel.Write(data, 0, 1, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xlsx")); }
public ActionResult <object> ImportProduct() { var filePath = Utils.FileSave(HttpContext, "xlsx"); if (filePath.IsNullOrWhiteSpace()) { return(false.ResponseDataError("上传文件失败,格式不对或超过限制文件大小")); } var errorMsgList = new List <string>(); DataTable table; try { table = NpoiExcelHelper.ExcelToTable(filePath); } catch (Exception ex) { return(false.ResponseDataError($"数据转换失败,错误信息:{ex.Message}")); } var importData = new List <CrmProductScrewEntity>(); if (table.Rows.Count > 0) { var importDataTemp = NpoiExcelHelper.GetEntityFromDataTable <CrmProductScrewView>(table); var dictList = _cache.GetOrCreate("dictcontroller_getcrmdiclist", (entry) => _repositoryDict.GetDicList()); //类别id,缓存key和字典那边一样 var index = 1; foreach (var item in importDataTemp) { index++; if (dictList.FirstOrDefault(t => t.DictKey == item.ProductType && t.DictType == (int)CrmDictTypeEnum.产品类型) == null || dictList.FirstOrDefault(t => t.PDictKey == item.ProductType && t.DictKey == item.ProductNameType && t.DictType == (int)CrmDictTypeEnum.产品名称) == null || dictList.FirstOrDefault(t => t.DictKey == item.Material && t.DictType == (int)CrmDictTypeEnum.产品材质) == null || dictList.FirstOrDefault(t => t.DictKey == item.Exterior && t.DictType == (int)CrmDictTypeEnum.产品外观) == null || item.Specification.IsNullOrWhiteSpace() || item.PackageWeight == 0 || item.ProposedPrice == 0 || item.RetailPrice == 0 || item.PurchasePrice == 0 || item.CostPrice == 0 ) { errorMsgList.Add($"{index}行格式错误"); continue; //跳出循环 } var model = new CrmProductScrewEntity().CopyFrom(item); model.ModifyTime = DateTime.Now; model.Status = 1; importData.Add(model); } } var isok = _repository.AddBatchProductScrew(importData); var msg = !isok ? "存入数据时失败" : errorMsgList.Count == 0 ? "全部导入成功" : "【部分失败】" + string.Join("|", errorMsgList); return(isok.ResponseSuccessFailure(msg)); }
public void RFP_Hotel() { var file = @"E:\WorkDocument\Document\2016-08-31 hilton weddingconsult re\RFP Email List - GCM Hotels New 20160829需要放的酒店.xls"; var data = NpoiExcelHelper.Read(file, rowIndex: 6).Select(d => new { HotelCode = d.D, Email = string.Intern(d.G) + ";" + d.H }); Console.WriteLine(JsonConvert.SerializeObject(data.Where(d => !string.IsNullOrWhiteSpace(d.Email)))); }
public void Test_CreateSheet_byName() { NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet(); excel.CreateSheet(); excel.CreateSheet("金蛇狂舞"); excel.CreateSheet(); excel.CreateSheet("大鱼海棠"); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
static void Main(string[] args) { try { DataTable table = NpoiExcelHelper.Excel2DataTable("Sample.xls"); CsvHelper.DataTable2Csv(table); } catch (Exception ex) { Console.WriteLine(ex); } }
public void Test_writeStringArray() { string[,] res = new string[,] { {"A","B","C","D","E","F"}, {"A1","B2","C3","D4","E5","F6"}, {"A11","B22","C33","D44","E55","F66"}, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 0, 0); excel.Export(string.Format("{0}Test_writeStringArray.xls", ResultFile)); }
public void Test_writeNumberArray() { double[,] res = new double[,] { {1,2,3,4,5,6,7,8}, {1.1,2.2,3.3,4.4,5.5,6.6,7.7,8.8}, {1.11,2.22,3.33,4.44,5.55,6.66,7.77,8.88}, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 3, 3); excel.Export(string.Format("{0}Test_writeNumberArray.xls", ResultFile)); }
public void Test_writeNumberArray() { double[,] res = new double[, ] { { 1, 2, 3, 4, 5, 6, 7, 8 }, { 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8 }, { 1.11, 2.22, 3.33, 4.44, 5.55, 6.66, 7.77, 8.88 }, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 3, 3); excel.Export(string.Format("{0}Test_writeNumberArray.xls", ResultFile)); }
public void Test_writeStringArray() { string[,] res = new string[, ] { { "A", "B", "C", "D", "E", "F" }, { "A1", "B2", "C3", "D4", "E5", "F6" }, { "A11", "B22", "C33", "D44", "E55", "F66" }, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 0, 0); excel.Export(string.Format("{0}Test_writeStringArray.xls", ResultFile)); }
public void 读取_返回List_Dynamic() { var file = @"F:\WorkDocument\Code.zen\Code.Zen.Test\assert\GCM Summer Sale_hotellist_Zeropartner.xlsx"; var data = NpoiExcelHelper.Read(file, 0, 0, 0); var a = data.Select(d => new { hotelcode = d.C, hot = string.IsNullOrWhiteSpace(d.H) ? 0 : d.H, hotelbookingurl = d.Z, }); var rs = JsonConvert.SerializeObject(a); Console.WriteLine(rs); }
public void Test_Write_Number_Array() { double[,] res = new double[, ] { { 1, 2, 3, 4, 5, 6, 7, 8 }, { 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8 }, { 1.11, 2.22, 3.33, 4.44, 5.55, 6.66, 7.77, 8.88 }, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 3, 3); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_Wirte_String_Array() { string[,] res = new string[, ] { { "A", "B", "C", "D", "E", "F" }, { "A1", "B2", "C3", "D4", "E5", "F6" }, { "A11", "B22", "C33", "D44", "E55", "F66" }, }; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet("1-1"); excel.Write(res, 0, 0, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_Read_Dynamic_List() { var file = @"C:\Users\Administrator\Desktop\npoiTestOutput\Test_Write_Customer_Object_List.xls"; var data = NpoiExcelHelper.Read(file, 0, 0, 0); var a = data.Select(d => new { A = d.A, B = d.B, C = d.C, D = d.D, }); var rs = JsonConvert.SerializeObject(a); Console.WriteLine(rs); }
public void Hilton_Summer_Olympics_Source_Test() { var file = @"E:\WorkDocument\Document\2016-05-13 夏季活动\包含酒店中文名,参加夏季活动酒店信息表格-0512.xlsx"; var data = NpoiExcelHelper.Read(file, rowIndex: 1); var a = data.Select(d => new { hoteltype = d.AD, hotelname_ch = d.C, hotelcode = d.D, hotelcity = d.AE, hot = string.IsNullOrWhiteSpace(d.I) ? 0 : d.I, hotelbookingurl = d.AA, }); var rs = JsonConvert.SerializeObject(a); Console.WriteLine(rs); }
public void Test_writeListValueType() { List <string> data = new List <string>() { "A", "B", "C" }; //List<List<string>> data = new List<List<string>> //{ // new List<string> {"A"}, // new List<string> {"B","C"}, // new List<string> {"D","E","F"} //}; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeListValueType.xls", ResultFile)); }
public void BookLink_Mod() { var file = @"E:\WorkDocument\Document\2016-06-01 booklink 修改\6月1日需更新酒店信息表格-0523.xlsx"; var data = NpoiExcelHelper.Read(file, rowIndex: 1).Select(d => new { HotelCode = d.D, BookLink = d.AB }); StringBuilder sb = new StringBuilder(); foreach (var item in data) { sb.AppendFormat("UNION SELECT '{0}','{1}'" + "\r\n", item.HotelCode, item.BookLink); } Console.WriteLine(sb.ToString()); //Console.WriteLine(JsonConvert.SerializeObject(data)); }
public void Test_JSONConvert_Desc_Dynamic() { //string json = @"{ ""Name"":""陈宁"",""SEXx"":""男"" }"; //var data = JsonConvert.DeserializeAnonymousType(json, new { Name = "", Sex = "", Age = 0 }); //dynamic obj = new ExpandoObject(); //obj.Name = "千里之外"; //obj.Author = "JAY"; //obj.Album = "2007世界巡回演唱会"; //obj.FavoiCount = 167; //Console.WriteLine(JsonConvert.SerializeObject(obj)); var json = @"[{ ""HotelCode"": ""SYXCICI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SYXDTDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""JHGXIDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSHITW"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HGHJIDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SYXHIHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""XMNCICI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""WUXTJDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CKGJBDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CSXZHHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HUZHADI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""MFMCSCI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CZXWDHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSWFHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""FOCLDDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""TAOLXDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""WUHRSHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CANSCDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""XMNWBDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HAKHCDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SYXHQDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""DLUGTHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HKGHCCI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SHAWAWA"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SHAHITW"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""DDGZDGI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CANSRDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CTUCCHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SHASPDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HAKWEHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""NKGWUDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSCICI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HAKMEHI"", ""Email"": ""[email protected] "" }, { ""HotelCode"": ""NGBNCDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""URCHHHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CANGUHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SHASHHI"", ""Email"": ""[email protected] "" }, { ""HotelCode"": ""NKGJFHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SZXSBGI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HGHHEDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SZVTVDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""TAOGBHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SZXSFHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HGHLRHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""LJGGIGI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CKGCWDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""TNAJHHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSWAWA"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""XIYHIHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HSNZHHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CGOZHHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""TSNECHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""FUOCDHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSDTDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""GOQQG"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""NKGNRHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""NKGYFHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SHEDTDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CANGTHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""WUHOVHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""WUXXDDI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SJWZSHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""SZXSSHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""BJSCAHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""ZGNZDHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HFEHIHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""HAKHAHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""AVAHSDT"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""CKGHIHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""TNAJCHI"", ""Email"": ""*****@*****.**"" }, { ""HotelCode"": ""YNTDIDI"", ""Email"": ""*****@*****.**"" }]"; var a = JsonConvert.DeserializeObject <List <C1> >(json); var b = NpoiExcelHelper.Read(@"E:\WorkDocument\Document\2016-06-14 希尔顿婚宴网站- 酒店增加email\RFP Email List - GCM Hotels New 20160613.xls", rowIndex: 6).Select(d => new C1 { HotelCode = d.D, Email = d.H }); foreach (var item in a) { var c = b.FirstOrDefault(d => d.HotelCode.Trim() == item.HotelCode.Trim() && !string.IsNullOrWhiteSpace(d.Email)); if (c == null) { continue; } item.Email += ";" + b.FirstOrDefault(d => d.HotelCode.Trim() == item.HotelCode.Trim()).Email; } Console.WriteLine(JsonConvert.SerializeObject(a)); }
public void Test_Write_ListValueType() { List <string> data = new List <string>() { "A", "B", "C" }; //List<List<string>> data = new List<List<string>> //{ // new List<string> {"A"}, // new List<string> {"B","C"}, // new List<string> {"D","E","F"} //}; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet(); excel.Write(data, 0, 0, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_Write_DataTable() { DataTable dt = new DataTable(); dt.Columns.Add("string", typeof(string)); dt.Columns.Add("int", typeof(int)); dt.Columns.Add("decimal", typeof(decimal)); dt.Columns.Add("double", typeof(double)); dt.Columns.Add("datetime", typeof(DateTime)); string[] colTitles = new List <string>() { "标题", "int 数值", "decimal 数值", "double 数值", "datetime 数值" }.ToArray(); for (int i = 0; i < 30000; i++) { DataRow dr = dt.NewRow(); dr["string"] = "NPOI向Excel文件中插入数值时,可能会出现数字当作文本的情况(即左上角有个绿色三角),这样单元格的值就无法参与运算。这是因为在SetCellValue设置单元格值的时候使用了字符串进行赋值,默认被转换成了字符型。如果需要纯数字型的,请向SetCellValue中设置数字型变量。sheet.GetRow(2).CreateCell(2).SetCellValue(123);"; dr["int"] = i; dr["decimal"] = Convert.ToDecimal(i) / 2; dr["double"] = Convert.ToDouble(i) / 2; dr["datetime"] = DateTime.Now; dt.Rows.Add(dr); } NpoiExcelHelper excel = new NpoiExcelHelper(); excel.CreateSheet(); excel.Write(colTitles, 0, 0, 0); excel.Write(dt, 0, 1, 0); excel.Export(Path.Combine(_output_directory, MethodBase.GetCurrentMethod().Name + ".xls")); }
public void Test_writeAnonymousList() { var res = new List<Product>(); for (int i = 0; i < 10000; i++) { res.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } var data = res.Select(d => new { B = d.ProductId, A = d.SysNo, c = d.Price, }).ToList(); NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeAnonymousList.xls", ResultFile)); }
public void Test_wirteDynamicList() { NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(new[] { "第一列", "第二列", "三", "肆" }, 0, 0, 0); var data = new List<dynamic>(); for (int i = 0; i < 10000; i++) { dynamic o = new ExpandoObject(); o.A = "Hello World"; o.B = DateTime.Now.ToString("yyyy-MM-dd"); o.C = 1.1m; o.D = 2.2d; data.Add(o); } excel.Write(data, 0, 1, 0); excel.Export(string.Format("{0}Test_wirteDynamicList.xls", ResultFile)); }
public void Test_writeCustomerTypeListObject() { var data = new List<Product>(); for (int i = 0; i < 10000; i++) { data.Add(new Product { SysNo = i, ProductId = "编号:" + i, Price = Convert.ToDecimal(i) / 3, CreateTime = DateTime.Now }); } NpoiExcelHelper excel = new NpoiExcelHelper(); /* * 注意 : * 使用 用户自定义实体类型写入Excel时,会自动根据 属性名称字母排序数据列 */ excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeCustomerTypeListObject.xls", ResultFile)); }
private void buttonX1_Click(object sender, EventArgs e) { string fileName = "";//保存的excel文件名 SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "导出Excel(*.xls)|*.xls"; sfd.FileName = "质量分析日报表"; string[] faltsArray = { "加工斑点", "针孔", "斑点", "阴裂", "凹凸", "泥渣", "缺泥", "粗糙", "开裂", "裂耳", "变形", "其他", "小计", "色不均", "色脏", "缺釉", "釉彩斑点", "缩釉", "釉缕", "毛孔", "落渣", "粘疤", "粘渣脚", "伤裂", "薄釉", "其他","小计", "欠火", "过火", "釉泡", "出损", "其他", "小计" }; setting = settingMethod.Find(); isNeedRate = setting.IsNeedRate == 0 ? false : true; DataSet ds = (DataSet)rptGrid.PrimaryGrid.DataSource; if (ds != null) { if (sfd.ShowDialog() == DialogResult.OK) { fileName = sfd.FileName; int columnNums = rptGrid.PrimaryGrid.Columns.Count; //列数; int rowNums = rptGrid.PrimaryGrid.Rows.Count; //行数 HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet("sheet1"); IRow row = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index; style.FillPattern = FillPattern.SolidForeground; NpoiExcelHelper.setMergedRegion(sheet, row, 0, "产品编号", 0, 2, 0, 0); NpoiExcelHelper.setMergedRegion(sheet, row, 1, "器型名称", 0, 2, 1, 1); NpoiExcelHelper.setMergedRegion(sheet, row, 2, "花色名称", 0, 2, 2, 2); NpoiExcelHelper.setMergedRegion(sheet, row, 3, "窑炉编号", 0, 2, 3, 3); NpoiExcelHelper.setMergedRegion(sheet, row, 4, "瓷质", 0, 2, 4, 4); NpoiExcelHelper.setMergedRegion(sheet, row, 5, "班次", 0, 2, 5, 5); NpoiExcelHelper.setMergedRegion(sheet, row, 6, "成型工艺", 0, 2, 6, 6); NpoiExcelHelper.setMergedRegion(sheet, row, 7, "瓷重", 0, 2, 7, 7); NpoiExcelHelper.setMergedRegion(sheet, row, 8, "总数", 0, 2, 8, 8); //NpoiExcelHelper.setMergedRegion(sheet, row, 8, "合计", 0, 2, 8, 8); NpoiExcelHelper.setMergedRegion(sheet, row, 9, "A等品", 0, 0, 9, 12); NpoiExcelHelper.setMergedRegion(sheet, row, 13, "B等品", 0, 0, 13, 16); NpoiExcelHelper.setMergedRegion(sheet, row, 17, "C等品", 0, 1, 17, 18); NpoiExcelHelper.setMergedRegion(sheet, row, 19, "补釉", 0, 1, 19, 20); NpoiExcelHelper.setMergedRegion(sheet, row, 21, "废品", 0, 1, 21, 22); NpoiExcelHelper.setMergedRegion(sheet, row, 23, "小计", 0, 1, 23, 24); NpoiExcelHelper.setMergedRegion(sheet, row, 25, "成型缺陷", 0, 0, 25, 50); NpoiExcelHelper.setMergedRegion(sheet, row, 51, "釉彩缺陷", 0, 0, 51, 78); NpoiExcelHelper.setMergedRegion(sheet, row, 79, "看火缺陷", 0, 0, 79, 88); IRow row2 = sheet.CreateRow(1); NpoiExcelHelper.setMergedRegion(sheet, row2, 9, "外销", 1, 1, 9, 10); NpoiExcelHelper.setMergedRegion(sheet, row2, 11, "内销", 1, 1, 11, 12); NpoiExcelHelper.setMergedRegion(sheet, row2, 13, "B甲", 1, 1, 13, 14); NpoiExcelHelper.setMergedRegion(sheet, row2, 15, "B等", 1, 1, 15, 16); for (int i = 0; i < faltsArray.Length; i++) { int j = i * 2 + 25; NpoiExcelHelper.setMergedRegion(sheet, row2, j, faltsArray[i], 1, 1, j, j + 1); } IRow row3 = sheet.CreateRow(2); NpoiExcelHelper.setQtyAndRate(row3, 9, 88, isNeedRate); for (int i = 3; i < rowNums + 3; i++)// { IRow r = sheet.CreateRow(i); if (isNeedRate) { for (int j = 0; j < columnNums; j++) { ICell c = r.CreateCell(j); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 3]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } string columnname = gr[j].GridColumn.Name; if (columnname.ToUpper().Contains("RATE")) { if (!Convert.IsDBNull(gr[j].Value) && Convert.ToDouble(gr[j].Value) >= 5) { c.CellStyle = style; } } } } else { for (int j = 0; j < 9; j++) { ICell c = r.CreateCell(j); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 3]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } } for (int j = 9; j < columnNums; j = j + 2) { ICell c = r.CreateCell(j); CellRangeAddress region = new CellRangeAddress(i, i, j, j + 1); sheet.AddMergedRegion(region); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 3]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } } } } FileStream file = new FileStream(fileName, FileMode.OpenOrCreate); book.Write(file); file.Flush(); file.Close(); MessageUtil.ShowTips("导出成功!"); } } }
private void buttonX1_Click(object sender, System.EventArgs e) { string fileName = "";//保存的excel文件名 SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "导出Excel(*.xls)|*.xls"; sfd.FileName = "选瓷汇总表"; DataSet ds = (DataSet)rptGrid.PrimaryGrid.DataSource; if (ds != null) { if (sfd.ShowDialog() == DialogResult.OK) { fileName = sfd.FileName; int columnNums = rptGrid.PrimaryGrid.Columns.Count; //列数; int rowNums = rptGrid.PrimaryGrid.Rows.Count; //行数 HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet("sheet1"); IRow row = sheet.CreateRow(0); NpoiExcelHelper.setMergedRegion(sheet, row, 0, "产品编号", 0, 1, 0, 0); NpoiExcelHelper.setMergedRegion(sheet, row, 1, "器型名称", 0, 1, 1, 1); NpoiExcelHelper.setMergedRegion(sheet, row, 2, "花色名称", 0, 1, 2, 2); NpoiExcelHelper.setMergedRegion(sheet, row, 3, "窑炉编号", 0, 1, 3, 3); NpoiExcelHelper.setMergedRegion(sheet, row, 4, "瓷质", 0, 1, 4, 4); NpoiExcelHelper.setMergedRegion(sheet, row, 5, "班次", 0, 1, 5, 5); NpoiExcelHelper.setMergedRegion(sheet, row, 6, "A+", 0, 0, 6, 7); NpoiExcelHelper.setMergedRegion(sheet, row, 8, "A等", 0, 0, 8, 9); NpoiExcelHelper.setMergedRegion(sheet, row, 10, "CA等", 0, 0, 10, 11); NpoiExcelHelper.setMergedRegion(sheet, row, 12, "B甲", 0, 0, 12, 13); NpoiExcelHelper.setMergedRegion(sheet, row, 14, "B等", 0, 0, 14, 15); NpoiExcelHelper.setMergedRegion(sheet, row, 16, "C等", 0, 0, 16, 17); NpoiExcelHelper.setMergedRegion(sheet, row, 18, "补釉", 0, 0, 18, 19); NpoiExcelHelper.setMergedRegion(sheet, row, 20, "废品", 0, 0, 20, 21); NpoiExcelHelper.setMergedRegion(sheet, row, 22, "总数", 0, 1, 22, 23); IRow row2 = sheet.CreateRow(1); NpoiExcelHelper.setQtyAndRate(row2, 4, 18, isNeedRate); for (int i = 2; i < rowNums + 2; i++)// { IRow r = sheet.CreateRow(i); if (isNeedRate) { for (int j = 0; j < columnNums; j++) { ICell c = r.CreateCell(j); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } } } else { for (int j = 0; j < 6; j++) { ICell c = r.CreateCell(j); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } } for (int j = 6; j < columnNums; j = j + 2) { ICell c = r.CreateCell(j); CellRangeAddress region = new CellRangeAddress(i, i, j, j + 1); sheet.AddMergedRegion(region); GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2]; //瓷质 if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial") { c.SetCellValue(gr[j].FormattedValue.ToString()); } else { c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString()); } } } } //for (int i = 0; i <= rowNums + 2; i++) //{ // sheet.AutoSizeColumn(i); //} FileStream file = new FileStream(fileName, FileMode.OpenOrCreate); book.Write(file); file.Flush(); file.Close(); MessageUtil.ShowTips("导出成功!"); } } }
public void Test_writeListValueType() { List<string> data = new List<string>() { "A", "B", "C" }; //List<List<string>> data = new List<List<string>> //{ // new List<string> {"A"}, // new List<string> {"B","C"}, // new List<string> {"D","E","F"} //}; NpoiExcelHelper excel = new NpoiExcelHelper(); excel.Write(data, 0, 0, 0); excel.Export(string.Format("{0}Test_writeListValueType.xls", ResultFile)); }
/// <summary> /// 开始进行转化 /// </summary> /// <param name="excelName">生成Excel的名称</param> /// <param name="sheetName">生成ExcelSheet的名称</param> /// <returns></returns> public string StartConvert(string excelName = "temp.xls", string sheetName = "pic") { logger.Log("图片转化开始:" + picPath); // 从图片文件创建image对象 Image img = Image.FromFile(picPath); Bitmap bmp = new Bitmap(img); img.Dispose(); int width = bmp.Width; int height = bmp.Height; if (width >= 250) { throw new Exception("图片宽度像素须控制在250以内."); } string path = excelPath + "\\" + excelName; List <int> hasWriteColor = new List <int>(); List <int> hasSetWidthCol = new List <int>(); bool hasNoWriteColor = false; while (true) { hasNoWriteColor = false; List <int> CurrentColor = new List <int>(); #region 一次写入颜色 //创建Excel对象,进行转化 using (NpoiExcelHelper excel = new NpoiExcelHelper(path)) { excel.SetWorkSheet(sheetName); excel.SetColotSpool(colorSpool); for (int h = 0; h < height; h++) { for (int w = 0; w < width; w++) { Color color = bmp.GetPixel(w, h); int tempRgb = color.ToArgb(); //判断是否是之前循环已经写入的颜色 if (hasWriteColor.Contains(tempRgb)) { continue; } if (!hasSetWidthCol.Contains(w)) { excel.SetColumeWidth(w); hasSetWidthCol.Add(w); } //写入Excel颜色 if (excel.SetCellColor(h, w, color)) { //记录本次循环颜色中 if (!CurrentColor.Contains(tempRgb)) { CurrentColor.Add(tempRgb); } } else { //如果还是有无法写入的颜色,则标记还存在未写入颜色 if (hasNoWriteColor) { continue; } hasNoWriteColor = true; } } } excel.SaveToFile(path); } #endregion logger.Log("本次写入颜色:" + CurrentColor.Count + "种"); //将本次写入的颜色记录到全部写入颜色中 hasWriteColor.AddRange(CurrentColor); //如果不存在未写入未,则本次结束 if (!hasNoWriteColor) { break; } logger.Log("存在未写入颜色,继续进行写入"); } logger.Log("图片转化完成:" + picPath); return(path); }
public RDataBom LoadExcel() { string formatNo = Request.Query["formatNo"].ToString(); if (Request.Form.Files == null || Request.Form.Files.Count == 0) { throw new Exception("Excel不存在"); } var excelFormat = Service.FetchExcelFormat(formatNo); if (excelFormat == null) { throw new Exception("导入格式 不存在!"); } var sheet = NpoiExcelHelper.GetSheet(Request.Form.Files[0], "0"); if (sheet == null) { throw new Exception("Sheet 找不到!"); } RDataBom rInfo = new RDataBom(); rInfo.prd_no = NpoiExcelHelper.GetCellStringValue(excelFormat.PrdNoPos, sheet); rInfo.prd_name = NpoiExcelHelper.GetCellStringValue(excelFormat.PrdNamePos, sheet); rInfo.dep_no = NpoiExcelHelper.GetCellStringValue(excelFormat.DeptNoPos, sheet); var fieldList = Service.GetDtls(excelFormat.Id).OrderBy(o => o.cell_index).ToList(); DataTable dt = new DataTable(); foreach (var item in fieldList) { dt.Columns.Add(item.field_no); } dt.Columns.Add("bom_level"); //物料大类 是节点Bom头 设3半成品,其他是4物料 dt.Columns.Add("bom_prdt_knd"); dt.Columns.Add("check_result"); dt.Columns.Add("check_err_code"); dt.Columns.Add("check_ask_radio"); for (int i = excelFormat.StartRow - 1; i <= sheet.LastRowNum; ++i) { var newRow = dt.NewRow(); var row = sheet.GetRow(i); if (row == null) { break; } newRow["bom_level"] = NpoiExcelHelper.GetCellStringValue(row.GetCell(excelFormat.BomStructCell - 1)); newRow["check_result"] = ""; string bomLevel = NpoiExcelHelper.GetCellStringValue(row.GetCell(excelFormat.BomStructCell - 1)); //阶数中断了,代表最后 if (bomLevel.IsNullOrEmpty()) { break; } foreach (var field in fieldList) { string val = NpoiExcelHelper.GetCellStringValue(row.GetCell(field.cell_index - 1)); //阶数中断了,代表最后 if (val.IsNullOrEmpty() && field.cell_index == excelFormat.BomStructCell) { break; } newRow[field.field_no] = val; } dt.Rows.Add(newRow); } rInfo.boms = dt; return(rInfo); }