using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using HotelPms.Share.Util;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Diagnostics;
|
using System.Drawing;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using HotelPms.Share.Windows.Util;
|
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
|
using BorderStyle = NPOI.SS.UserModel.BorderStyle;
|
|
namespace HotelPms.Share.Windows.Report
|
{
|
public class GridExcel : ReportBase, IDisposable, IReport
|
{
|
#region ★★★★★ Declartions ★★★★★
|
|
private bool m_Disposed = false;
|
private string m_File = string.Empty;
|
|
#endregion
|
|
#region ★★★★★ Property ★★★★★
|
|
private bool m_XlsxEnabled = true;
|
|
public bool XlsxEnabled
|
{
|
get { return m_XlsxEnabled; }
|
set { m_XlsxEnabled = value; }
|
}
|
|
#endregion
|
|
#region ★★★★★ Class Event ★★★★★
|
|
public GridExcel(DataTable data, GridStyle style, bool xlsxEnabled, string file)
|
{
|
m_Data = data;
|
m_Style = style;
|
m_XlsxEnabled = xlsxEnabled;
|
m_File = file;
|
}
|
|
~GridExcel()
|
{
|
Dispose(false);
|
}
|
|
protected virtual void Dispose(bool disposing)
|
{
|
if (!m_Disposed) //一回だけ
|
{
|
if (disposing)
|
{
|
//Managed Resources
|
}
|
|
//Unmanaged resources
|
m_Disposed = true;
|
}
|
}
|
|
public void Dispose()
|
{
|
Dispose(true);
|
GC.SuppressFinalize(this);
|
}
|
|
#endregion
|
|
#region ★★★★★ Control Event ★★★★★
|
#endregion
|
|
#region ★★★★★ Private Function ★★★★★
|
#endregion
|
|
#region ★★★★★ Public Function ★★★★★
|
|
public void Output()
|
{
|
string path = m_File + (m_XlsxEnabled ? ".xlsx" : ".xls");
|
Output(path);
|
//Process.Start(path);
|
CConvert.StartFile(path);
|
}
|
|
public void Output(string file)
|
{
|
using (FileStream stream = new FileStream(file, FileMode.Create, FileAccess.Write))
|
{
|
IWorkbook wb = null;
|
if (m_XlsxEnabled) { wb = new XSSFWorkbook(); } else { wb = new HSSFWorkbook(); }
|
ISheet sheet = wb.CreateSheet("Sheet1");
|
ICreationHelper cH = wb.GetCreationHelper();
|
|
// Create a new font and alter it.
|
IFont font = wb.CreateFont();
|
font.FontHeightInPoints = (short)m_Style.Font.SizeInPoints;
|
font.FontName = m_Style.Font.Name;
|
|
//center
|
ICellStyle style = wb.CreateCellStyle();
|
style.SetFont(font);
|
style.BorderBottom = BorderStyle.Thin;
|
style.BottomBorderColor = IndexedColors.Black.Index;
|
style.BorderLeft = BorderStyle.Thin;
|
style.LeftBorderColor = IndexedColors.Black.Index;
|
style.BorderRight = BorderStyle.Thin;
|
style.RightBorderColor = IndexedColors.Black.Index;
|
style.BorderTop = BorderStyle.Thin;
|
style.TopBorderColor = IndexedColors.Black.Index;
|
style.Alignment = HorizontalAlignment.Center;
|
style.VerticalAlignment = VerticalAlignment.Center;
|
|
//left
|
ICellStyle style1 = wb.CreateCellStyle();
|
style1.SetFont(font);
|
style1.BorderBottom = BorderStyle.Thin;
|
style1.BottomBorderColor = IndexedColors.Black.Index;
|
style1.BorderLeft = BorderStyle.Thin;
|
style1.LeftBorderColor = IndexedColors.Black.Index;
|
style1.BorderRight = BorderStyle.Thin;
|
style1.RightBorderColor = IndexedColors.Black.Index;
|
style1.BorderTop = BorderStyle.Thin;
|
style1.TopBorderColor = IndexedColors.Black.Index;
|
style1.Alignment = HorizontalAlignment.Left;
|
style1.VerticalAlignment = VerticalAlignment.Center;
|
|
//right
|
ICellStyle style3 = wb.CreateCellStyle();
|
style3.SetFont(font);
|
style3.Alignment = HorizontalAlignment.Right;
|
style3.VerticalAlignment = VerticalAlignment.Bottom;
|
|
IFont font18 = wb.CreateFont();
|
font18.FontHeightInPoints = (short)18;
|
font18.FontName = m_Style.Font.Name;
|
|
ICellStyle style2 = wb.CreateCellStyle();
|
style2.SetFont(font18);
|
style2.Alignment = HorizontalAlignment.Left;
|
style2.VerticalAlignment = VerticalAlignment.Center;
|
|
IRow row = sheet.CreateRow(0);
|
ICell cell = row.CreateCell(0);
|
cell.SetCellValue("データ一覧");
|
cell.CellStyle = style2;
|
|
cell = row.CreateCell(m_Style.ColumnStyle.Count - 1);
|
cell.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日(ddd) HH:mm:ss"));
|
cell.CellStyle = style3;
|
|
//タイトル
|
row = sheet.CreateRow(1);
|
//row.HeightInPoints = 16;
|
int col = 0;
|
foreach (ColumnStyle cs in m_Style.ColumnStyle)
|
{
|
cell = row.CreateCell(col);
|
cell.CellStyle = style;
|
if (cs.MaxCharLen < GeneralSub.GetStrLenB(cs.Text)) { cs.MaxCharLen = GeneralSub.GetStrLenB(cs.Text); }
|
cell.SetCellValue(cH.CreateRichTextString(cs.Text));
|
col++;
|
}
|
|
//データ
|
for (int i = 0; i < m_Data.Rows.Count; i++)
|
{
|
row = sheet.CreateRow(i + 2);
|
//row.HeightInPoints = 16;
|
col = 0;
|
foreach (ColumnStyle cs in m_Style.ColumnStyle)
|
{
|
cell = row.CreateCell(col);
|
if (cs.DefaultCellStyle != null)
|
{
|
if (cs.DefaultCellStyle.Alignment == StringAlignment.Center)
|
{
|
cell.CellStyle = style;
|
}
|
else
|
{
|
cell.CellStyle = style1;
|
}
|
}
|
string text = m_Data.Rows[i][cs.DataField].ToString();
|
if (cs.MaxCharLen < GeneralSub.GetStrLenB(text)) { cs.MaxCharLen = GeneralSub.GetStrLenB(text); }
|
cell.SetCellValue(cH.CreateRichTextString(text));
|
col++;
|
}
|
}
|
|
//fit
|
col = 0;
|
foreach (ColumnStyle cs in m_Style.ColumnStyle)
|
{
|
sheet.SetColumnWidth(col, (int)((cs.MaxCharLen + 0.72) * 256));
|
//sheet.AutoSizeColumn(col);
|
col++;
|
}
|
wb.Write(stream, true);
|
}
|
}
|
|
/// <summary>
|
/// 横水平方向
|
/// </summary>
|
/// <param name="align"></param>
|
/// <returns></returns>
|
public HorizontalAlignment ToHorizontalAlignment(StringAlignment align)
|
{
|
HorizontalAlignment ag = HorizontalAlignment.Center;
|
switch (align)
|
{
|
case StringAlignment.Center:
|
ag = HorizontalAlignment.Center;
|
break;
|
case StringAlignment.Near:
|
ag = HorizontalAlignment.Left;
|
break;
|
case StringAlignment.Far:
|
ag = HorizontalAlignment.Right;
|
break;
|
}
|
return ag;
|
}
|
|
/// <summary>
|
/// 縦垂直方向
|
/// </summary>
|
/// <param name="align"></param>
|
/// <returns></returns>
|
public VerticalAlignment ToVerticalAlignment(StringAlignment align)
|
{
|
VerticalAlignment ag = VerticalAlignment.Center;
|
switch (align)
|
{
|
case StringAlignment.Center:
|
ag = VerticalAlignment.Center;
|
break;
|
case StringAlignment.Near:
|
ag = VerticalAlignment.Top;
|
break;
|
case StringAlignment.Far:
|
ag = VerticalAlignment.Bottom;
|
break;
|
}
|
return ag;
|
}
|
|
#endregion
|
|
}
|
}
|
