public void ReadRowInSheet(IRow row) { heads = new List <ExcelHeadAttribute>(); short cellIndex = row.LastCellNum;//总共多少列 for (short i = 0; i < cellIndex; i++) { ICell cell = row.GetCell(i); if (cell == null) { //对于空列的处理 continue; } IRichTextString rtext = cell.RichStringCellValue; string text = rtext.String; //文本内容,列索引 if (!string.IsNullOrEmpty(text)) { ExcelHeadAttribute head = new ExcelHeadAttribute() { ColumnIndex = i, ColumnName = text.Trim() }; heads.Add(head); } } }
void CompareListView_Select(object obj, EventArgs e) {//移除选择项,添加到左右两侧 ListView lst = obj as ListView; if (lst.SelectedItems.Count == 0) { return; } ListViewItem focus = lst.FocusedItem; int index = focus.Index; CompareData merge = focus.Tag as CompareData; if (leftHead.Count > index) { //左侧 ExcelHeadAttribute left = new ExcelHeadAttribute() { ColumnIndex = merge.OriginHeadIndex, ColumnName = merge.OriginHeadName }; lstLeft.InsertRow(left); leftHead.RemoveAt(index); } if (rightHead.Count > index) {//右侧 ExcelHeadAttribute right = new ExcelHeadAttribute() { ColumnIndex = merge.NewHeadIndex, ColumnName = merge.NewHeadName }; lstRight.InsertRow(right); rightHead.RemoveAt(index); } //合并项 lst.Items.RemoveAt(index); }
/// <summary> /// 获取头部集合 /// </summary> /// <returns></returns> public static List <ExcelHeadDTO> GetHeads() { //结果集 List <ExcelHeadDTO> result = new List <ExcelHeadDTO>(); PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties(); //遍历属性 foreach (PropertyInfo info in propertyInfos) { //获取头部特性 ExcelHeadAttribute attribute = info.GetCustomAttribute <ExcelHeadAttribute>(); if (attribute != null) { //头部实体赋值 ExcelHeadDTO dto = new ExcelHeadDTO(); dto.PropertyName = info.Name; dto.HeadName = attribute.HeadName; dto.IsSetHeadColor = attribute.IsSetHeadColor; dto.IsLocked = attribute.IsLocked; dto.SortValue = attribute.SortValue; dto.ColumnType = attribute.ColumnType; dto.IsHiddenColumn = attribute.IsHiddenColumn; dto.ColumnWidth = attribute.ColumnWidth; dto.Format = attribute.Format; dto.BackgroundColor = attribute.BackgroundColor; dto.HeaderBackgroundColor = attribute.HeaderBackgroundColor; //获取属性 RequiredAttribute requiredAttribute = info.GetCustomAttribute <RequiredAttribute>(); List <string> disableAttrs = new RequiredAttributeValidation <TEntity>().GetDisableAttributes(); //启用必填属性 if (requiredAttribute != null && (disableAttrs == null || disableAttrs.Contains(info.Name) == false)) { dto.IsValidationHead = true; } else { dto.IsValidationHead = false; } //启用必填属性 Dictionary <string, BaseAttribute> enableAttrs = new RequiredAttributeValidation <TEntity>().GetEnableAttributes(); if (enableAttrs != null && enableAttrs.Keys.Contains(info.Name) == true) { dto.IsValidationHead = true; } //添加至结果集 result.Add(dto); } } int columnIndex = 0; //返回结果集 result = result.OrderBy(it => it.SortValue).ToList(); result.ForEach(it => { it.ColumnIndex = columnIndex++; }); return(result); }
//表头制作 public void CreateHead(IWorkbook workBook, ISheet sheet, ICellStyle cellStyleHeadTitle, ExcelHeadAttribute eha) { IRow RowHeadTitle = sheet.CreateRow(eha.rowIndex); //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(eha.firstRow, eha.lastRow, eha.firstCol, eha.lastCol)); RowHeadTitle.CreateCell(0).SetCellValue(eha.name); /*****单元格样式 start******/ cellStyleHeadTitle.WrapText = true; //高度 RowHeadTitle.HeightInPoints = eha.HeightInPoints; //2 * sheet.DefaultRowHeight / 10; //字体设置,字体要调用CreateFont() IFont fontheadTitle = workBook.CreateFont(); fontheadTitle.FontHeightInPoints = eha.fontSize; //HSSFColor.OliveGreen.Black.Index fontheadTitle.Color = eha.fontColor; cellStyleHeadTitle.SetFont(fontheadTitle); //这里调试出来的,样式一定要给到单元格才有效 RowHeadTitle.Cells[0].CellStyle = cellStyleHeadTitle; }
//导出 public BaseResult DownLoadBase(string name, int?_year, List <VillageWorkingGroupViewModel> RList) { BaseResult _br = new BaseResult(); var _filesavepath = "Files/" + RealName + "/WorkingGroup"; var _fileexits = System.Web.HttpContext.Current.Server.MapPath("~/" + _filesavepath); if (!Directory.Exists(_fileexits)) { Directory.CreateDirectory(_fileexits); } var path = System.Web.HttpContext.Current.Server.MapPath("~/" + _filesavepath + "/" + name + ".xls"); var dt = new System.Data.DataTable(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("村级防汛防台工作组"); //合并第一行单元格 //IRow RowHead = sheet.CreateRow(0); //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //string noitce = "注意: "; //noitce += "请不要修改<村级防汛防台工作组>表的格式,如果在系统上新增了岗位请重新下载数据模板"; //RowHead.CreateCell(0).SetCellValue(noitce); #region 标题 var cellStyleHeadTitle = hssfworkbook.CreateCellStyle(); cellStyleHeadTitle.Alignment = HorizontalAlignment.Center; //居中显示 cellStyleHeadTitle.VerticalAlignment = VerticalAlignment.Top; //垂直居中 ExcelHeadAttribute eha = new ExcelHeadAttribute() { rowIndex = 0, firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 5, fontColor = 8, fontSize = 16, HeightInPoints = 50, name = "村级防汛防台工作组" }; CreateHead(hssfworkbook, sheet, cellStyleHeadTitle, eha); #endregion #region 提醒 var cellStyleHeadNotice = hssfworkbook.CreateCellStyle(); cellStyleHeadNotice.Alignment = HorizontalAlignment.Left; //居中显示 cellStyleHeadNotice.VerticalAlignment = VerticalAlignment.Top; //垂直居中 string noitce = "注意: "; noitce += "请不要修改<村级防汛防台工作组>表的格式,如果在系统上新增了岗位请重新下载数据模板"; ExcelHeadAttribute ehanotice = new ExcelHeadAttribute() { rowIndex = 1, firstRow = 1, lastRow = 1, firstCol = 0, lastCol = 5, fontColor = 10, fontSize = 10, HeightInPoints = 20, name = noitce }; CreateHead(hssfworkbook, sheet, cellStyleHeadNotice, ehanotice); #endregion //表头 IRow RowBody = sheet.CreateRow(2); List <string> listtitle = new List <string>(); listtitle.Add("行政村名称"); listtitle.Add("岗位"); listtitle.Add("责任人姓名"); listtitle.Add("责任人职务"); listtitle.Add("责任人手机"); listtitle.Add("备注"); for (int iColumnIndex = 0; iColumnIndex < listtitle.Count; iColumnIndex++) { RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString()); RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20; //单元格样式 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //设置单元格上下左右边框线 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Top; //是否换行 cellStyle.WrapText = true; RowBody.Cells[iColumnIndex].CellStyle = cellStyle; //字体大小 IFont cellfont = hssfworkbook.CreateFont(); cellfont.FontHeightInPoints = 14; cellStyle.SetFont(cellfont); sheet.SetColumnWidth(iColumnIndex, 20 * 256); } /****内容****/ try { int i = 0; RList.ForEach(w => { IRow row1 = sheet.CreateRow(i + 3); for (int j = 0; j < 6; j++) { ICell cell = row1.CreateCell(j); switch (j) { case 0: cell.SetCellValue(RList[i].adnm.ToString()); break; case 1: cell.SetCellValue(RList[i].Post.ToString()); break; case 2: cell.SetCellValue(RList[i].PersonLiable.ToString()); break; case 3: cell.SetCellValue(RList[i].Position.ToString()); break; case 4: cell.SetCellValue(RList[i].HandPhone.ToString()); break; case 5: cell.SetCellValue(RList[i].Remarks.ToString()); break; } } i++; }); _br.IsSuccess = true; #region 日志 StringBuilder sb = new StringBuilder(); sb.Append("在栏目{组织责任/行政村防汛防台工作组}下,导出数据{" + RList.Count + "}条"); _ILogHelper.WriteLog(sb.ToString(), OperationTypeEnums.导出); #endregion } catch (Exception ex) { _br.IsSuccess = false; throw new Exception(ex.Message); } //转为字节数组 MemoryStream stream = new MemoryStream(); hssfworkbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } _br.filepath = _filesavepath + "/" + name + ".xls"; /*******************************/ return(_br); }
private ISheet createSheet1(HSSFWorkbook workBook, string sheetName) { ISheet sheet = workBook.CreateSheet(sheetName); #region 标题 var cellStyleHeadTitle = workBook.CreateCellStyle(); cellStyleHeadTitle.Alignment = HorizontalAlignment.Center; //居中显示 cellStyleHeadTitle.VerticalAlignment = VerticalAlignment.Top; //垂直居中 ExcelHeadAttribute eha = new ExcelHeadAttribute() { rowIndex = 0, firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 5, fontColor = 8, fontSize = 16, HeightInPoints = 50, name = "村级防汛防台工作组" }; CreateHead(workBook, sheet, cellStyleHeadTitle, eha); #endregion #region 提醒 var cellStyleHeadNotice = workBook.CreateCellStyle(); cellStyleHeadNotice.Alignment = HorizontalAlignment.Left; //居中显示 cellStyleHeadNotice.VerticalAlignment = VerticalAlignment.Top; //垂直居中 string noitce = "注意: "; noitce += "1、行政村请在“行政村名录工作表”中复制,否则无法导入。"; noitce += "2、一人兼多岗,也请明确要逐条列出。 "; noitce += "3、加*列为必填项。 "; noitce += "4、岗位请下拉选择。 "; noitce += "5、小灵通(格式:区号\" - \"号码)。"; ExcelHeadAttribute ehanotice = new ExcelHeadAttribute() { rowIndex = 1, firstRow = 1, lastRow = 1, firstCol = 0, lastCol = 5, fontColor = 10, fontSize = 14, HeightInPoints = 40, name = noitce }; CreateHead(workBook, sheet, cellStyleHeadNotice, ehanotice); #endregion /*********添加表头 s********/ IRow RowBody = sheet.CreateRow(2); List <string> listtitle = new List <string>(); listtitle.Add("*行政村"); listtitle.Add("*岗位(仅限下拉内容)"); listtitle.Add("*责任人姓名"); listtitle.Add("责任人职务"); listtitle.Add("*责任人手机"); listtitle.Add("备注"); for (int iColumnIndex = 0; iColumnIndex < listtitle.Count(); iColumnIndex++) { //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 //if(iColumnIndex >=2 && iColumnIndex <= 4) //{ // sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4)); // RowBody.CreateCell(iColumnIndex).SetCellValue("责任人"); // RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString()); //} //else //{ // sheet.AddMergedRegion(new CellRangeAddress(1, 2, iColumnIndex, iColumnIndex)); // RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString()); //} RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString()); RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20; //单元格样式 ICellStyle cellStyle = workBook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //设置单元格上下左右边框线 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Top; //是否换行 cellStyle.WrapText = true; RowBody.Cells[iColumnIndex].CellStyle = cellStyle; //字体大小 IFont cellfont = workBook.CreateFont(); cellfont.FontHeightInPoints = 14; cellStyle.SetFont(cellfont); sheet.SetColumnWidth(iColumnIndex, 25 * 256); } /*********添加表头 e********/ //设置下拉框 setSheet2(workBook, sheet); return(sheet); }
void ListViewRow_SelctClick(object sender, EventArgs e) { ListView lst = sender as ListView; //当前选择行的序号 if (lst.SelectedItems.Count == 0) {//点击之后会两次触发,一次是选中,还有一次是释放选中 return; } ListViewItem item = lst.FocusedItem; //当前选择项的行索引 int index = item.Index; ECompareTarget area = (ECompareTarget)lst.Tag; rtbNote.Text += string.Format("Dirction:【{0}】 Row Index :【{1}】\r\n", area.ToString(), index); ExcelHeadAttribute head = item.Tag as ExcelHeadAttribute; CompareData compare = null; bool isInsert = false; int dataRowIndex = 0; if (leftHead.Count == rightHead.Count || (leftHead.Count > rightHead.Count && area == ECompareTarget.Left) || (leftHead.Count < rightHead.Count && area == ECompareTarget.Right)) { //什么情况是进行添加操作呢? /* * 1.左右两侧都已近补充完整 * 2.等待添加的数据来自现补充列较多的 */ compare = new CompareData(); isInsert = true; } else if (area == ECompareTarget.Left) {//此时进行的是单方补充 //当前是数据补充到缺少项中 dataRowIndex = leftHead.Count; compare = lstCompare.Items[dataRowIndex].Tag as CompareData; } else { dataRowIndex = rightHead.Count; compare = lstCompare.Items[dataRowIndex].Tag as CompareData; } switch (area) { case ECompareTarget.Left: compare.OriginHeadName = head.ColumnName; compare.OriginHeadIndex = head.ColumnIndex; leftHead.Add(true); break; case ECompareTarget.Right: compare.NewHeadName = head.ColumnName; compare.NewHeadIndex = head.ColumnIndex; rightHead.Add(true); break; } //添加到中间区域时进行判断【组合成一项完整的匹配列】 //【左右都存在内容才是一组完整数据】 if (isInsert) { lstCompare.InsertRow(compare); } else {//还需要对于UI进行改动 ListViewItem vc = lstCompare.Items[dataRowIndex]; //界面重绘 switch (area) { case ECompareTarget.Left: foreach (var column in leftExcelData) { vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key); } break; case ECompareTarget.Right: foreach (var column in rightExcelData) { vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key); } break; } } lstCompare.Refresh(); lst.Items.RemoveAt(index); }