Exemplo n.º 1
0
        private byte[] BuildExcel(ExecuteResult result)
        {
            if (result.Result == null)
            {
                return(new byte[0]);
            }

            var workbook = new XSSFWorkbook();

            for (int i = 0; i < result.Result.Count; i++)
            {
                var resultItem = result.Result[i];

                ISheet     sheet = workbook.CreateSheet("result_" + (i + 1).ToString());
                ICellStyle style = sheet.Workbook.CreateCellStyle();
                style.Alignment         = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                int rowIndex = 0;
                #region 创建头部
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                int     colNum  = -1;
                for (int j = 0; j < resultItem.Columns.Count; j++)
                {
                    dataRow.WriteString(++colNum, resultItem.Columns[j], style);
                }
                #endregion
                sheet.CreateFreezePane(0, 1, 0, 1);//冻结首行

                var         doubleCellStyle   = sheet.CreateDoubleCellStyle();
                var         datetimeCellStyle = sheet.Workbook.CreateCellStyle();
                IDataFormat datastyle         = sheet.Workbook.CreateDataFormat();
                datetimeCellStyle.DataFormat = datastyle.GetFormat("yyyy-MM-dd hh:mm:ss");

                rowIndex++;
                foreach (var values in resultItem.Values)
                {
                    var row = sheet.CreateRow(rowIndex);
                    for (int j = 0; j < values.Length; j++)
                    {
                        var col = values[j];
                        if (null == col)
                        {
                        }
                        else if (col is int num_int)
                        {
                            row.WriteDouble(j, num_int);
                        }
                        else if (col is double num_double)
                        {
                            row.WriteDouble(j, num_double, doubleCellStyle);
                        }
                        else if (col is DateTime num_dt)
                        {
                            row.WriteDateTime(j, num_dt, datetimeCellStyle);
                        }
                        else if (col is string num_str)
                        {
                            row.WriteString(j, num_str);
                        }
                        else
                        {
                            row.WriteString(j, col.ToString());
                        }
                    }
                    rowIndex++;
                }
            }
            var sqlsheet = workbook.CreateSheet("sql");
            for (int i = 0; i < result.Result.Count; i++)
            {
                var resultItem = result.Result[i];
                var hrow       = sqlsheet.CreateRow(i);
                var cell       = hrow.CreateCell(0);
                cell.SetCellValue("result_" + (i + 1).ToString());
                var cell2 = hrow.CreateCell(1);
                cell2.SetCellValue(resultItem.Sql);
            }

            using (var ms = new MemoryStream()) {
                workbook.Write(ms);
                return(ms.ToArray());
            }
        }