/// <summary> /// 添加列表格式的数据 /// </summary> /// <param name="sheet">需要添加数据的表格</param> /// <param name="data">需要添加的数据</param> /// <param name="titleMaps">标题映射</param> /// <param name="startRow">开始行</param> /// <returns></returns> public ExcelAddDataReturnModel AddData <T>(ISheet sheet, List <T> data, Dictionary <string, string> titleMaps = null, int startRow = 0) { var result = new ExcelAddDataReturnModel(); var value = new object(); ICell cell = null; Type type = null; var columnWidthList = new Dictionary <int, int>(); var strValue = string.Empty; // 处理标题问题 var titles = new List <string>(); var props = typeof(T).GetProperties().Where(it => it.CanRead && it.PropertyType.IsPublic).ToArray(); for (int i = 0; i < props.Length; i++) { titles.Add(props[i].Name); } // 添加标题 IRow row = SetTitleValue(sheet, titleMaps, startRow, columnWidthList, titles); startRow++; // 添加内容 for (int i = 0; i < data.Count; i++) { var obj = data[i]; row = sheet.CreateRow(startRow); for (int j = 0; j < titles.Count; j++) { cell = row.CreateCell(j); type = props[j].PropertyType; value = props[j].GetValue(obj); columnWidthList[j] = SetValue(value, cell, type, columnWidthList[j]); } startRow++; } result.StartRow = startRow; result.ColumnWidthList = columnWidthList; return(result); }
/// <summary> /// 添加DataTable格式的数据 /// </summary> /// <param name="sheet">需要添加数据的表格</param> /// <param name="dt">需要添加的数据</param> /// <param name="titleMaps">标题映射</param> /// <param name="startRow">开始行</param> /// <returns></returns> public ExcelAddDataReturnModel AddData(ISheet sheet, DataTable dt, Dictionary <string, string> titleMaps = null, int startRow = 0) { var result = new ExcelAddDataReturnModel(); var value = new object(); ICell cell = null; Type type = null; var columnWidthList = new Dictionary <int, int>(); var strValue = string.Empty; // 处理标题问题 var titles = new List <string>(); for (int i = 0; i < dt.Columns.Count; i++) { titles.Add(dt.Columns[i].ColumnName); } // 添加标题 IRow row = SetTitleValue(sheet, titleMaps, startRow, columnWidthList, titles); startRow++; // 添加内容 for (int i = 0; i < dt.Rows.Count; i++) { var dtRow = dt.Rows[i]; row = sheet.CreateRow(startRow); for (int j = 0; j < titles.Count; j++) { cell = row.CreateCell(j); type = dt.Columns[titles[j]].DataType; value = dtRow[titles[j]]; columnWidthList[j] = SetValue(value, cell, type, columnWidthList[j]); } startRow++; } result.StartRow = startRow; result.ColumnWidthList = columnWidthList; return(result); }
public static void GenerateExcel() { var manager = new ExcelManager(); var sheet = manager.GetSheet("测试"); //var personList = new List<Person>(); //for (int i = 0; i < 10000; i++) //{ // personList.Add(new Person() // { // Id = i, // Name = "test" + i, // Age = i, // Address = "dkjgjkgjkdjkakdafiokadkghkahkg" + i // }); //} //Stopwatch sw = new Stopwatch(); //sw.Start(); //for (int i = 0; i < 100; i++) //{ // manager.AddData(sheet, personList, null, i * 10000); //} //manager.SaveWorkBook(wb, DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"); //sw.Stop(); //Console.WriteLine(sw.ElapsedMilliseconds); var dt = new DataTable(); dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Name"); dt.Columns.Add("Age", typeof(double)); dt.Columns.Add("Birthday", typeof(DateTime)); dt.Columns.Add("Address"); var titleMap = new Dictionary <string, string>(); titleMap.Add("Id", "编号"); titleMap.Add("Name", "姓名"); titleMap.Add("Age", "年龄"); titleMap.Add("Birthday", "生日"); titleMap.Add("Address", "地址"); Random rnd = new Random(); for (int i = 0; i < 10; i++) { var row = dt.NewRow(); row["Id"] = i; row["Name"] = "test" + i; row["Age"] = rnd.Next(1000000) * 1.0 / 1000; row["Address"] = "dkjgjkgjkdjkakdafiokadkghkahkg" + i; row["Birthday"] = DateTime.Now; dt.Rows.Add(row); } var sw = new Stopwatch(); var colList = new ExcelAddDataReturnModel(); sw.Start(); for (int i = 0; i < 1; i++) { colList = manager.AddData(sheet, dt, titleMap, i * 10000); } manager.SetColumnWidth(sheet, colList.ColumnWidthList, 5, null); manager.SaveWorkBook(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"); sw.Stop(); Console.WriteLine(sw.ElapsedMilliseconds); //var style = manager.CreateCommonCellStyle(wb); //var colWidthDic = new Dictionary<int, int>(); //var mergeRegionList = new List<CellRangeAddress>(); //var rowNum = 0; //colWidthDic[0] = 10; //colWidthDic[1] = 20; //colWidthDic[2] = 30; //manager.SetColumnWidth(sheet, colWidthDic, 3, 20); //var row = sheet.CreateRow(rowNum); //rowNum++; //manager.CreateCell(row, "Excel文件导出测试", 0); //mergeRegionList.Add(new CellRangeAddress(0, 0, 0, 2)); //for (int i = 0; i < 3; i++) //{ // row = sheet.CreateRow(rowNum); // manager.CreateCell(row, i.ToString(), 0, style); // manager.CreateCell(row, "姓名" + i, 1, style); // manager.CreateCell(row, "序号" + i, 2); // rowNum++; //} //manager.CreateMergedRegion(sheet, mergeRegionList); ////add picture data to this workbook. //byte[] bytes = System.IO.File.ReadAllBytes(@"D:\MyProject\NPOIDemo\ShapeImage\image1.jpg"); //int pictureIdx = wb.AddPicture(bytes, PictureType.JPEG); //// Create the drawing patriarch. This is the top level container for all shapes. //var patriarch = sheet.CreateDrawingPatriarch(); ////add a picture //HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3); //HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx); //FileStream fs = File.OpenWrite(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"); //wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。 //fs.Close(); }