using System;
using System.Linq;
using System.Drawing;
using OfficeOpenXml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OfficeOpenXml.Style;
using KART_API.Utils;
using Svg;
namespace KART_API.Services
{
public class MakeExcelService
{
public byte[] getTableExcel(string dataString, string isTurn)
{
JArray tableArray = Common.GetJArrayFromString(dataString);
string contentString = "목차";
string tableString = "테이블";
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add(contentString);
ExcelWorksheet wsSheet2 = ExcelPkg.Workbook.Worksheets.Add(tableString);
wsSheet1.Cells.Style.Font.Name = "맑은 고딕";
wsSheet1.Cells.Style.Font.Size = 10;
wsSheet1.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
wsSheet1.Cells.Style.WrapText = false;
wsSheet2.Cells.Style.Font.Name = "맑은 고딕";
wsSheet2.Cells.Style.Font.Size = 9;
wsSheet2.Cells.Style.WrapText = true;
for (int row = 1; row <= (tableArray.Count + 4); row++)
{
wsSheet1.Row(row).Height = 20;
}
////1,1부터 시작
using (ExcelRange Rng = wsSheet1.Cells[1, 1, 4, 2])
{
Rng.Value = "목차";
Rng.Merge = true;
Rng.Style.Font.Size = 25;
Rng.Style.Font.Bold = true;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
Rng.Style.Font.Color.SetColor(Color.FromArgb(0xFFFFFF));
Rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
Rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0x333333));
}
using (ExcelRange Rng = wsSheet1.Cells[5, 1, (5 + tableArray.Count-1), 2])
{
Rng.Style.Border.Top.Style = ExcelBorderStyle.Hair;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Hair;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Hair;
Rng.Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
int tableRow = 1;
for(int i = 0; i < tableArray.Count; i++)
{
var tableJson = tableArray[i];
if (String.IsNullOrEmpty(tableJson["body"].ToString()) == false)
{
///목차
string titleString = string.Empty;
if (String.IsNullOrEmpty(Common.GetNullString(tableJson["vname"])))
{
titleString = Common.StripHTML(tableJson["head"].ToString());
}
else
{
titleString = "<표 " + tableJson["vname"] + ">" + " " + Common.StripHTML(tableJson["head"].ToString());
}
titleString = titleString.Replace("
", "");
var titleCell = wsSheet1.Cells[(5 + i), 1, (5 + i), 1];
var linkCell = wsSheet1.Cells[(5 + i), 2, (5 + i), 2];
titleCell.Value = titleString;
Uri url = new Uri("#'" + tableString + "'!A" + tableRow, UriKind.Relative);
linkCell.Value = "Go";
linkCell.Hyperlink = url;
linkCell.Style.Font.UnderLine = true;
linkCell.Style.Font.Color.SetColor(Color.Blue);
///목차
///테이블
JArray tableBody = Common.GetJArrayFromString(tableJson["body"].ToString());
if (tableJson["qtype"].ToString() == "single" && isTurn == "1")
{
for(int brow = 0; brow < tableBody.Count; brow++)
{
string valueString = string.Empty;
if(brow == 0)
{
valueString = "계||";
}
else
{
valueString = "100.0";
}
JObject tempRow = tableBody[brow] as JObject;
tempRow.Add("add_0", valueString);
tableBody[brow] = tempRow;
}
}
using (ExcelRange Rng = wsSheet2.Cells[tableRow, 1, tableRow, tableBody.First.SelectTokens("*").Count()])
{
Rng.Value = titleString;
Rng.Merge = true;
Rng.Style.Font.Bold = true;
tableRow++;
}
int meanPoint = 0;
int tableStartRow = tableRow;
int startRowSpan = 0;
int startColSpan = 0;
for (int row = 0; row < tableBody.Count; row++)
{
var bodyRow = tableBody[row].Values();
int col = 1;
foreach (var bodyCell in bodyRow)
{
var cellDetail = bodyCell.ToString().Split('|');
if (cellDetail.Length == 3)
{
int rowSpan = Convert.ToInt32(Common.GetNullToZero(cellDetail[1]));
int colSpan = Convert.ToInt32(Common.GetNullToZero(cellDetail[2]));
if (rowSpan > 0)
{
rowSpan = rowSpan - 1;
}
if (colSpan > 0)
{
colSpan = colSpan - 1;
}
if (row == 0 && col == 1)
{
startRowSpan = rowSpan;
startColSpan = colSpan;
}
using (ExcelRange Rng = wsSheet2.Cells[tableRow, col, tableRow + rowSpan, col + colSpan])
{
if(rowSpan > 0 || colSpan > 0)
{
Rng.Merge = true;
}
Rng.Style.Border.BorderAround(ExcelBorderStyle.Thin);
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
if(row > startRowSpan - 1 && col < startColSpan + 2)
{
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
else
{
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
}
}
}
using (ExcelRange Rng = wsSheet2.Cells[tableRow, col, tableRow, col])
{
if (cellDetail[0] == "Mean")
{
if (isTurn != "1")
{
meanPoint = tableRow;
}
else
{
meanPoint = col;
}
}
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Value = Common.StripHTML(cellDetail[0]);
}
col++;
}
tableRow++;
}
int tableMidRow = tableStartRow + startRowSpan;
int tableMidCol = 1 + startColSpan;
int tableEndRow = tableStartRow + tableBody.Count - 1;
int tableEndCol = tableBody.First.SelectTokens("*").Count();
if (String.IsNullOrEmpty(tableJson["wcr"].ToString()) == false)
{
var svgDoc = SvgDocument.FromSvg(tableJson["wcr"].ToString());
svgDoc.CustomAttributes["fill"] = "white";
SvgRectangle rect = new SvgRectangle();
rect.Fill = new SvgColourServer(Color.White);
rect.Width = svgDoc.Width;
rect.Height = svgDoc.Height;
svgDoc.Children.Insert(0, rect);
var bitmap = svgDoc.Draw();
insertPicture(wsSheet2, bitmap, tableStartRow, 1 + tableEndCol);
}
wsSheet2.Cells[tableStartRow, 1, tableMidRow, tableMidCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet2.Cells[tableStartRow, tableMidCol + 1, tableMidRow, tableEndCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet2.Cells[tableMidRow + 1, 1, tableEndRow, tableMidCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet2.Cells[tableMidRow + 1, tableMidCol + 1, tableEndRow, tableEndCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
for (int row = tableMidRow + 1; row <= tableEndRow; row++)
{
for (int col = tableMidCol + 1; col <= tableEndCol; col++)
{
using (ExcelRange Rng = wsSheet2.Cells[row, col])
{
if (Common.IsNumeric(Rng.Value.ToString()))
{
if (Rng.Value.ToString().IndexOf(".") < 0)
{
///사례수
Rng.Value = "(" + Rng.Value + ")";
}
else if (isTurn != "1" && row == meanPoint || isTurn == "1" && col == meanPoint)
{
Rng.Style.Numberformat.Format = "0.00";
Rng.Value = Convert.ToDouble(Rng.Value);
}
else
{
Rng.Style.Numberformat.Format = "0.0";
Rng.Value = Convert.ToDouble(Rng.Value);
}
}
else
{
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
}
}
}
///테이블
tableRow++;
}
}
wsSheet1.Column(1).AutoFit();
wsSheet1.View.FreezePanes(5, 1);
return ExcelPkg.GetAsByteArray();
}
public byte[] getQuotaExcel(string dataString)
{
ExcelPackage ExcelPkg = new ExcelPackage();
JArray tableArray = Common.GetJArrayFromString(dataString);
for (int i = 0; i < tableArray.Count; i++)
{
var tableJson = tableArray[i];
string titleString = Common.StripHTML(tableJson["head"].ToString());
JArray tableBody = Common.GetJArrayFromString(tableJson["body"].ToString());
ExcelWorksheet wsSheet = ExcelPkg.Workbook.Worksheets.Add(titleString);
wsSheet.Cells.Style.Font.Name = "맑은 고딕";
wsSheet.Cells.Style.Font.Size = 9;
//wsSheet.Cells.Style.WrapText = true;
int tableRow = 1;
using (ExcelRange Rng = wsSheet.Cells[tableRow, 1, tableRow, tableBody.First.SelectTokens("*").Count()])
{
Rng.Value = titleString;
Rng.Merge = true;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Size = 14;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
tableRow++;
}
wsSheet.Row(1).Height = 30;
int tableStartRow = tableRow;
int startRowSpan = 0;
int startColSpan = 0;
for (int row = 0; row < tableBody.Count; row++)
{
var bodyRow = tableBody[row].Values();
int col = 1;
foreach (var bodyCell in bodyRow)
{
var cellDetail = bodyCell.ToString().Split('|');
if (cellDetail.Length == 3)
{
int rowSpan = Convert.ToInt32(Common.GetNullToZero(cellDetail[1]));
int colSpan = Convert.ToInt32(Common.GetNullToZero(cellDetail[2]));
if (rowSpan > 0)
{
rowSpan = rowSpan - 1;
}
if (colSpan > 0)
{
colSpan = colSpan - 1;
}
if (row == 0 && col == 1)
{
startRowSpan = rowSpan;
startColSpan = colSpan;
}
using (ExcelRange Rng = wsSheet.Cells[tableRow, col, tableRow + rowSpan, col + colSpan])
{
if (rowSpan > 0 || colSpan > 0)
{
Rng.Merge = true;
}
Rng.Style.Border.BorderAround(ExcelBorderStyle.Thin);
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
using (ExcelRange Rng = wsSheet.Cells[tableRow, col, tableRow, col])
{
string cellValue = Common.StripHTML(cellDetail[0]);
if (tableRow > tableStartRow + startRowSpan && col > 1 + startColSpan)
{
cellValue = cellValue.Substring(0, cellValue.IndexOf("/", cellValue.IndexOf("/") + 1));
}
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Value = Common.StripHTML(cellValue);
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
col++;
}
tableRow++;
}
int tableMidRow = tableStartRow + startRowSpan;
int tableMidCol = 1 + startColSpan;
int tableEndRow = tableStartRow + tableBody.Count - 1;
int tableEndCol = tableBody.First.SelectTokens("*").Count();
wsSheet.Cells[tableStartRow, 1, tableMidRow, tableMidCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet.Cells[tableStartRow, tableMidCol + 1, tableMidRow, tableEndCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet.Cells[tableMidRow + 1, 1, tableEndRow, tableMidCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet.Cells[tableMidRow + 1, tableMidCol + 1, tableEndRow, tableEndCol].Style.Border.BorderAround(ExcelBorderStyle.Thick);
wsSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
}
return ExcelPkg.GetAsByteArray();
}
public void insertPicture(ExcelWorksheet worksheet, Bitmap imageData, int cellY, int cellX)
{
string imgName = cellY + "_" + cellX;
var picture = worksheet.Drawings.AddPicture(imgName, imageData);
picture.SetSize(1000, 512);
picture.SetPosition(cellY - 1, 30, cellX - 1, 0);
}
}
}