如何将数据库中的树结构表导出EXCEL
我们常常会遇到这样的需求，将数据库中父子结构的表数据导出作为表头
我们实现的开发中就遇到这样的需求：将所有用户的权限表导出，权限作为表头
思路：sql求出每一条权限数据应该跨的行数,在代码中填充这些数据
create or replace function useEasy(mid in nvarchar2) return number is FunctionResult number; SubNum number; CurrNum number; begin CurrNum := 1; select count(*) into SubNum from sys_menu m where m.parentid = mid; --CurrNum := SubNum + CurrNum; if SubNum > 0 then CurrNum := CurrNum-1; FOR m1 IN (select m.id,m.parentid from sys_menu m where m.parentid = mid) LOOP CurrNum := CurrNum + useEasy(m1.id); end LOOP; end if; FunctionResult := CurrNum; return(FunctionResult); end useEasy;
select m.id,m.parentid,m.menutext,m.menucode,m.menutext,useEasy(m.id) OccupyRow from sys_menu m where m.parentid = '2D14DFA8-8D9B-4034-A2CD-2099AD4A0A33' order by m.menusort asc
public class ExportUserPower { public ExportUserPower(string sheetName) { this.sheetName = sheetName; } public HSSFWorkbook ExcelWork { get; set; } public string WorkName { get; set; } private string sheetName { get; set; } public string SheetName { get { return sheetName; } set { value = sheetName; } } public List<HeadMenuTree> MenuList { get; set; } private string systemCode; public string SystemCode { get { return systemCode; } set { value = systemCode; } } /// <summary> /// 创建Sheet /// </summary> /// <param name="hwb"></param> /// <param name="sheetName"></param> /// <returns></returns> public HSSFSheet CreateSheet(HSSFWorkbook hwb, string sheetName) { //创建一个excel中的表格 HSSFSheet sheet = (HSSFSheet)hwb.CreateSheet(sheetName); return sheet; } public ICell CreateCell(HSSFRow row, int num) { ICell cell = row.CreateCell(num); return cell; } public void ExportExcelWork() { //创建一个excel ExcelWork = new HSSFWorkbook(); //创建一个excel中的表格 HSSFSheet sheet = CreateSheet(ExcelWork, sheetName); CreateHeadMenu(sheet); } public void CreateHeadMenu(HSSFSheet sheet) { int _ColumnNum = 1; //定义列 int _RowNum = -1; //定义行 //HSSFRow headerrow = (HSSFRow)sheet.CreateRow(_RowNum); //headerrow.CreateCell(_ColumnNum).SetCellValue("用户"); //创建头部 列1 用户 //TreeMenu sys_root = MenuList.Where(w => w.menucode == systemCode).FirstOrDefault(); CreateAllMenuColl(sheet, MenuList, _RowNum, _ColumnNum); //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 2)); } public void CreateAllMenuColl(HSSFSheet sheet, List<HeadMenuTree> list, int row, int startColumn) { int f = 0; int thisRow = row + 1; IRow headerrow; if (sheet.LastRowNum < thisRow) headerrow = sheet.CreateRow(thisRow); else headerrow = sheet.GetRow(thisRow); if (headerrow == null) headerrow = sheet.CreateRow(thisRow); int thisColumn = startColumn; foreach (HeadMenuTree item in list) { //log4net.LogManager.GetLogger("OperationLogger").Error("thisColumn：" + thisColumn + "；thisRow：" + thisRow); ICell cell = headerrow.CreateCell(thisColumn); cell.CellStyle = CreateCellStyle(); cell.SetCellValue(item.Name); if (thisColumn != (thisColumn + item.OccupyRow - 1)) sheet.AddMergedRegion(new CellRangeAddress(thisRow, thisRow, thisColumn, (thisColumn + item.OccupyRow) - 1)); if (item.Children.Count > 0) CreateAllMenuColl(sheet, item.Children, thisRow, thisColumn); thisColumn = (thisColumn + item.OccupyRow); f++; } thisColumn = 1; thisRow = 0; } public void FillData() { SystemMenuService service = new SystemMenuService(); MenuList = service.GetAllHeadMenu("ZhanJiang"); } public static void DataTree(string path, DataTable table, int treeIndex = 10000) { using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { IWorkbook workBook = new HSSFWorkbook(); //现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错 //ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ... if (Path.GetExtension(path).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase)) { workBook = new XSSFWorkbook(); } string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet1" : table.TableName; ISheet sheet = workBook.CreateSheet(sheetName); IRow row = null; int colNum = table.Columns.Count; if (treeIndex < table.Columns.Count || treeIndex > 0) { colNum = treeIndex; } ICellStyle cellCenterStyle = GetCenter(workBook); int beginNum = 1;//排除列头，从1开始 //处理表格列头 row = sheet.CreateRow(beginNum - 1); for (int i = 0; i < table.Columns.Count; i++) { string strVal = table.Columns[i].ColumnName; ICell cell = row.CreateCell(i); cell.SetCellValue(strVal); cell.CellStyle = cellCenterStyle; row.Height = 350; sheet.AutoSizeColumn(i); } //处理数据内容 for (int i = 0; i < table.Rows.Count; i++) { row = sheet.CreateRow(beginNum + i); row.Height = 250; for (int j = 0; j < table.Columns.Count; j++) { string strVal = table.Rows[i][j].ToString(); ICell currCell = row.CreateCell(j); currCell.SetCellValue(strVal); currCell.CellStyle = cellCenterStyle; sheet.SetColumnWidth(j, 256 * 15); } } for (int i = 0; i < colNum; i++) { List<int> lstColWidth = new List<int>(); string currVal = string.Empty; string nextVal = string.Empty; for (int j = beginNum; j <= sheet.LastRowNum; j++) { currVal = sheet.GetRow(j).Cells[i].StringCellValue; int mk = j; if (!string.IsNullOrWhiteSpace(currVal))//排除 空值，空值不做合并处理 { for (int k = j + 1; k <= sheet.LastRowNum; k++) { nextVal = sheet.GetRow(k).Cells[i].StringCellValue; if (currVal != nextVal) { //因为k 累加所以导致当前值与下个值 不相同，所以记录 当前行数要 减去1 mk = k - 1; break; } else if (k == sheet.LastRowNum) //边界值，处理最后一行，则提前Break 并记录当前 k { mk = k; break; } } } if (mk != j)//排除 空值外，下个值的行数不等于当前行数，则需要合并 { sheet.AddMergedRegion(new CellRangeAddress(j, mk, i, i)); } //if (i == 0) //如果是第一列，则 垂直水平居中 { sheet.GetRow(j).Cells[i].CellStyle = cellCenterStyle; } //跳到执行下一个不同数据的行 j = mk; //记录列长度 lstColWidth.Add(DataLength(currVal)); } //设置列宽 int maxWidth = lstColWidth.Max() * 200; sheet.SetColumnWidth(i, maxWidth); } //固定列、行 滚动时不变 sheet.CreateFreezePane(3, 1, 3, 1); //写入数据流 workBook.Write(fs); } } private static ICellStyle GetCenter(IWorkbook workBook, short fontSize = 10) { ICellStyle cellStyle = workBook.CreateCellStyle(); IFont font = workBook.CreateFont(); font.FontName = "微软雅黑"; font.FontHeightInPoints = fontSize; cellStyle.SetFont(font); cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.Alignment = HorizontalAlignment.Center; return cellStyle; } /// <summary> /// 获取字符串长度（中文按2个字节长度） /// </summary> /// <param name="stringWithEnglishAndChinese"></param> /// <returns></returns> private static int DataLength(string stringWithEnglishAndChinese) { int lng = 0; for (int i = 0; i < stringWithEnglishAndChinese.Length; i++) { byte[] b = System.Text.Encoding.Default.GetBytes(stringWithEnglishAndChinese.Substring(i, 1)); if (b.Length > 1) lng += 2; else lng += 1; } return lng; } public ICellStyle CreateCellStyle() { ICellStyle cellStyle = ExcelWork.CreateCellStyle(); cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; return cellStyle; } }
