/// <summary> /// 从excel导入数据到数组 /// </summary> public List <T> Import <T>(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, List <ExcelHeader> list) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = workbook.GetSheetAt(SheetIndex); List <T> resultList = new List <T>(); HSSFRow headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; Dictionary <int, string> dict = new Dictionary <int, string>(); for (int i = headerRow.FirstCellNum; i < cellCount; i++) { dict.Add(i, headerRow.GetCell(i).StringCellValue); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i); Dictionary <string, object> valueMap = new Dictionary <string, object>(); for (int j = row.FirstCellNum; j < cellCount; j++) { ExcelHeader header = list.Find(f => f.HeaderText == dict[j]); string value = row.GetCell(j) == null ? "" : row.GetCell(j).ToString(); if (header != null) { valueMap.Add(header.PropertyName, value); } } resultList.Add(this.Create <T>(list, valueMap)); } ExcelFileStream.Close(); workbook = null; sheet = null; return(resultList); }
/// <summary> /// 返回列头 /// </summary> /// <param name="template"></param> /// <returns></returns> public List <ExcelHeader> GetHeaderList(string template) { XmlTextReader reader = new XmlTextReader(template); XmlDocument doc = new XmlDocument(); doc.Load(reader); List <ExcelHeader> headerList = new List <ExcelHeader>(); foreach (XmlNode node in doc.DocumentElement.ChildNodes) { ExcelHeader header = new ExcelHeader(); if (node.Attributes["HeaderText"] != null) { header.HeaderText = node.Attributes["HeaderText"].Value; } if (node.Attributes["PropertyName"] != null) { header.PropertyName = node.Attributes["PropertyName"].Value; } if (node.Attributes["DataType"] != null) { header.DataType = node.Attributes["DataType"].Value; } if (node.Attributes["StringFormat"] != null) { header.StringFormat = node.Attributes["StringFormat"].Value; } if (node.Attributes["MappingTo"] != null) { header.MappingTo = node.Attributes["MappingTo"].Value; } if (node.Attributes["Width"] != null) { string width = node.Attributes["Width"].Value; if (Regexlib.MatchInt(width)) { header.Width = int.Parse(width); } } foreach (XmlNode subNode in node.ChildNodes) { header.Mapping.Add(new Mapping { Original = subNode.Attributes["Original"].Value, Target = subNode.Attributes["Target"].Value }); } headerList.Add(header); } return(headerList); }
/// <summary> /// ListToMemoryStream /// </summary> /// <param name="list">数据源</param> /// <param name="nameList">列头信息</param> public MemoryStream Export <T>(BindingList <T> list, string strHeaderText, List <ExcelHeader> hearderList) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(strHeaderText); #region 文件属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = this.Author; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; si.ApplicationName = ApplicationName; si.Comments = Comments; si.Title = strHeaderText; si.Subject = strHeaderText; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(0); HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 600; headStyle.SetFont(font); headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; headStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; headStyle.BorderBottom = CellBorderType.THIN; headStyle.BorderLeft = CellBorderType.THIN; headStyle.BorderRight = CellBorderType.THIN; headStyle.BorderTop = CellBorderType.THIN; for (int i = 0; i < hearderList.Count; i++) { headerRow.CreateCell(i).SetCellValue(hearderList[i].HeaderText); headerRow.GetCell(i).CellStyle = headStyle; sheet.SetColumnWidth(i, hearderList[i].Width * 40);//设置列宽 } headerRow.Dispose(); } #endregion int rowIndex = 1; HSSFRow dataRow; HSSFCellStyle style = workbook.CreateCellStyle(); style.BorderBottom = CellBorderType.THIN; style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; style.BorderTop = CellBorderType.THIN; foreach (Object obj in list) { dataRow = sheet.CreateRow(rowIndex); PropertyInfo[] pis = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//就是这个对象所有属性的集合 if (pis != null) { for (int j = 0; j < hearderList.Count; j++) { HSSFCell newCell = dataRow.CreateCell(j); ExcelHeader eh = hearderList[j]; newCell.CellStyle = style; foreach (PropertyInfo pi in pis)//针对每一个属性进行循环 { if (eh.PropertyName.Equals(pi.Name)) { object PropertyValue = pi.GetValue(obj, null); if (PropertyValue != null) { string drValue = PropertyValue.ToString(); switch (PropertyValue.GetType().ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); if (eh.StringFormat != null) { newCell.SetCellValue(string.Format(eh.StringFormat, dateV)); } else { newCell.SetCellValue(string.Format("{0:yyyy-MM-dd}", dateV)); } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } } } } rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); }