private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
updateRowFormulas(row, Shifter);
}
}
public XSSFRowShifter(XSSFSheet sh)
{
sheet = sh;
}
//If this code does not exist in the file, add the code into the file after the following line:
protected void CheckBT_Click(object sender, EventArgs e)
{
////引用//ReferenceCode/ExcelColumn.cs的類別
//ExcelImportTemplate GetExcelDefine = new ExcelImportTemplate();
////建立匯入table
//GetExcelDefine.F_ImportTable();
////上傳路徑
//// 根目錄/路徑(~/路徑)
//String savePath = Server.MapPath(@"~\ExcelUpLoad\Sales\AMZForcast");
//DataTable D_table = new DataTable("Excel");
////建立Excel欄位
//D_table = GetExcelDefine.Dt1.Copy();
//DataTable D_errortable = new DataTable("Error");
////實際顯示欄位
//int Excel欄位數 = D_table.Columns.Count - 2;
if ((upload_file.PostedFile != null) && (upload_file.PostedFile.ContentLength > 0))
{
string fileName = System.IO.Path.GetFileName(upload_file.PostedFile.FileName);
string LocationFiled = Server.MapPath(Str上傳路徑);
string str頁簽名稱 = "";
try
{
DataTable D_table = new DataTable("Excel");
DataTable D_errortable = new DataTable("Error");
string 副檔名 = System.IO.Path.GetExtension(fileName);
DataTable DtColumnDefine = GetDBData("欄位定義");
if (Session["DataDefine"] != null)
{
Session.Remove("DataDeffine");
}
Session["DataDeffine"] = DtColumnDefine;
//指定Import Sheet Name
string StrSheetNameCheck = "";
Boolean BCheck = false;
int I資料起始欄, I資料起始列;
#region 基本資料欄位
//D_table.Columns.Add("預設資料");
#endregion
#region ErrorTable
//D_errortable.Columns.Add("SheetName");
//D_errortable.Columns.Add("Dept");
D_errortable.Columns.Add("Error");
#endregion
//
foreach (DataRow Dr in DtColumnDefine.Rows)
{
D_table.Columns.Add(Dr["資料名稱中文"].ToString());
}
if (DtColumnDefine.Rows.Count > 0)
{
StrSheetNameCheck = (string.IsNullOrEmpty(DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString()))?"": DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString();
if (!Boolean.TryParse(DtColumnDefine.Rows[0]["是否指定頁籤"].ToString(), out BCheck))
{
BCheck = false;
}
if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始列"].ToString(), out I資料起始列))
{
I資料起始列 = 1;
}
if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始欄"].ToString(), out I資料起始欄))
{
I資料起始欄 = 0;
}
while (File.Exists(LocationFiled + fileName))
{
fileName = fileName.Substring(0, fileName.Length - 副檔名.Length) + DateTime.Now.ToString("yyyyMMddhhmmssfff") + 副檔名;
}
upload_file.PostedFile.SaveAs(LocationFiled + fileName);
if (副檔名.ToUpper() == ".XLSX")
{
XSSFWorkbook workbook = new XSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream
for (int x = 0; x < workbook.NumberOfSheets; x++)
{
//-- 0表示：第一個 worksheet工作表
XSSFSheet u_sheet = (XSSFSheet)workbook.GetSheetAt(x);
str頁簽名稱 = u_sheet.SheetName.ToString();
//檢查是否有要對應資料
if (BCheck && StrSheetNameCheck != str頁簽名稱)
{
continue;
}
//-- Excel 表頭列
XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列);
//-- for迴圈的「啟始值」要加一，表示不包含 Excel表頭列
// for (int i = (u_sheet.FirstRowNum + 1); i <= u_sheet.LastRowNum; i++)
//-- 每一列做迴圈
//i=1第二列開始
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
#region 關鍵資料沒有不執行，避免USER亂填EXCEL
string Str款號 = "";
try
{
Str款號 = row.GetCell(1).ToString();
}
catch (Exception)
{
}
if (string.IsNullOrEmpty(Str款號))
{
continue;
}
#endregion
F_資料確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
//-- 釋放 NPOI的資源
workbook = null;
}
else
{
HSSFWorkbook workbook = new HSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream
for (int x = 0; x < workbook.NumberOfSheets; x++)
{
HSSFSheet u_sheet = (HSSFSheet)workbook.GetSheetAt(x); //-- 0表示：第一個 worksheet工作表
HSSFRow headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列); //-- Excel 表頭列
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列); //-- v.1.2.4版修改
//檢查是否有要對應資料
if (BCheck && StrSheetNameCheck != str頁簽名稱)
{
continue;
}
str頁簽名稱 = u_sheet.SheetName.ToString();
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++) //-- 每一列做迴圈
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
#region 關鍵資料沒有不執行，避免USER亂填EXCEL
string Str款號 = "";
try
{
Str款號 = row.GetCell(1).ToString();
}
catch (Exception)
{
}
if (string.IsNullOrEmpty(Str款號))
{
continue;
}
#endregion
F_資料確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
//-- 釋放 NPOI的資源
workbook = null;
}
//--錯誤資料顯示
if (D_errortable.Rows.Count > 0)
{
DataView D_View3 = new DataView(D_errortable);
ErrorGV.DataSource = D_View3;
ErrorGV.DataBind();
}
if (D_table.Rows.Count > 0)
{
GridView1.DataSource = D_table;
GridView1.DataBind();
if (D_errortable.Rows.Count == 0)
{
Session["ImportExcelData"] = D_table;
}
}
}
else
{
F_ErrorShow("Please contact Mis : Import format is not defined");
}
}
catch (Exception ex)
{
F_ErrorShow($"Error: {ex.Message}");
}
}
else
{
F_ErrorShow("Please select a file to upload.");
}
}
protected void CheckBT_Click(object sender, EventArgs e)
{
if ((upload_file.PostedFile != null) && (upload_file.PostedFile.ContentLength > 0))
{
string fileName = System.IO.Path.GetFileName(upload_file.PostedFile.FileName);
string LocationFiled = Server.MapPath(Str上傳路徑);
string str頁簽名稱 = "";
try
{
DataTable D_table = new DataTable("Excel");
DataTable D_errortable = new DataTable("Error");
DataTable DtCula = new DataTable("CulTable");
string 副檔名 = System.IO.Path.GetExtension(fileName);
if (Session["DataDefine"] != null)
{
Session.Remove("DataDeffine");
}
int I資料起始列;
#region 基本資料欄位
#endregion
#region ErrorTable
//D_errortable.Columns.Add("SheetName");
//D_errortable.Columns.Add("Dept");
D_errortable.Columns.Add("Error");
#endregion
//
//foreach (DataRow Dr in DtColumnDefine.Rows)
//{
// D_table.Columns.Add(Dr["資料名稱中文"].ToString());
//}
I資料起始列 = 0;
while (File.Exists(LocationFiled + fileName))
{
fileName = fileName.Substring(0, fileName.Length - 副檔名.Length) + DateTime.Now.ToString("yyyyMMddhhmmssfff") + 副檔名;
}
upload_file.PostedFile.SaveAs(LocationFiled + fileName);
if (副檔名.ToUpper() == ".XLSX")
{
XSSFWorkbook workbook = new XSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream
for (int x = 0; x < workbook.NumberOfSheets; x++)
{
//-- 0表示：第一個 worksheet工作表
XSSFSheet u_sheet = (XSSFSheet)workbook.GetSheetAt(x);
str頁簽名稱 = u_sheet.SheetName.ToString();
//-- Excel 表頭列
XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列);
//i=1第二列開始
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
F_資料確認(ref D_table, ref D_errortable, ref DtCula, row, i);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
//-- 釋放 NPOI的資源
workbook = null;
}
else
{
HSSFWorkbook workbook = new HSSFWorkbook(upload_file.PostedFile.InputStream); //==只能讀取 System.IO.Stream
for (int x = 0; x < workbook.NumberOfSheets; x++)
{
HSSFSheet u_sheet = (HSSFSheet)workbook.GetSheetAt(x); //-- 0表示：第一個 worksheet工作表
HSSFRow headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列); //-- Excel 表頭列
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列); //-- v.1.2.4版修改
str頁簽名稱 = u_sheet.SheetName.ToString();
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++) //-- 每一列做迴圈
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
F_資料確認(ref D_table, ref D_errortable, ref DtCula, row, i);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
//-- 釋放 NPOI的資源
workbook = null;
}
//--錯誤資料顯示
if (D_errortable.Rows.Count > 0)
{
DataView D_View3 = new DataView(D_errortable);
ErrorGV.DataSource = D_View3;
ErrorGV.DataBind();
}
if (D_table.Rows.Count > 0)
{
var DistinctColor = ListColor.Distinct().ToList();
if (DistinctColor.Count() > 0)
{
using (DataTable dt = DtCula.Clone())
{
dt.Columns.RemoveAt(1);
dt.Columns[0].ColumnName = "單一顏色";
foreach (var item in DistinctColor)
{
try
{
DataRow dr = dt.NewRow();
dr[0] = item;
DataView dv1 = new DataView(DtCula);
DataView dv2 = new DataView(DtCula);
dv1.RowFilter = " 計算color1 = '" + item + "'";
dv2.RowFilter = " 計算color2 = '" + item + "'";
for (int i = 2; i < dt.Columns.Count + 1; i++)
{
try
{
int x = 0;
if (dv1.Count > 0)
{
foreach (DataRowView dv1Row in dv1)
{
x += int.Parse(dv1Row[i].ToString());
}
}
if (dv2.Count > 0)
{
foreach (DataRowView dv2Row in dv2)
{
x += int.Parse(dv2Row[i].ToString());
}
}
dr[i - 1] = x;
}
catch (Exception ex2)
{
}
}
dt.Rows.Add(dr);
}
catch (Exception ex1)
{
}
}
if (dt.Rows.Count > 0)
{
GridView3.DataSource = dt;
GridView3.DataBind();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(DtCula, "AMZ_PO_Qty");
wb.Worksheets.Add(dt, "AMZ_Color_Qty");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", "AMZ_PO"));
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
}
}
}
catch (Exception ex)
{
F_ErrorShow($"Error: {ex.Message}");
}
}
}
public void XSSFTextParagraph_()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Rgb24 color = new Rgb24(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = "Arial";
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet);
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = ("Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (".");
Assert.AreEqual(".", text.BulletCharacter);
//Assert.IsNull(text.BulletFontColor);
Assert.AreEqual(POIUtils.Color_Empty, text.BulletFontColor);
text.BulletFontColor = (color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (-2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (-4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (-8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (1);
Assert.AreEqual(1, text.Level);
text.Level = (4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (false);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (true);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
//Assert.AreEqual(0, text.BulletAutoNumberStart);
//This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
Assert.AreEqual(1, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public byte[] ExprotExcel(IList <View_客戶資訊清單> data)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Export");
XSSFCellStyle styletital = (XSSFCellStyle)workbook.CreateCellStyle();
styletital.Alignment = HorizontalAlignment.Center;
styletital.BorderBottom = BorderStyle.Thin;
styletital.BorderLeft = BorderStyle.Thin;
styletital.BorderRight = BorderStyle.Thin;
styletital.BorderTop = BorderStyle.Thin;
styletital.FillForegroundColor = HSSFColor.Grey25Percent.Index;
styletital.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle stylecon = (XSSFCellStyle)workbook.CreateCellStyle();
stylecon.Alignment = HorizontalAlignment.Left; //對齊
stylecon.BorderBottom = BorderStyle.Thin;
stylecon.BorderLeft = BorderStyle.Thin;
stylecon.BorderRight = BorderStyle.Thin;
stylecon.BorderTop = BorderStyle.Thin;
#region HeaderRow
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("客戶名稱");
headerRow.Cells[0].CellStyle = styletital;
headerRow.CreateCell(1).SetCellValue("客戶銀行資訊數量");
headerRow.Cells[1].CellStyle = styletital;
headerRow.CreateCell(2).SetCellValue("客戶聯絡人數量");
headerRow.Cells[2].CellStyle = styletital;
#endregion
#region DetailRow
int rowIndex = 1;
foreach (var item in data)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(item.客戶名稱);
dataRow.Cells[0].CellStyle = stylecon;
dataRow.CreateCell(1).SetCellValue(item.客戶銀行資訊數量.ToString());
dataRow.Cells[1].CellStyle = stylecon;
dataRow.CreateCell(2).SetCellValue(item.客戶聯絡人數量.ToString());
dataRow.Cells[2].CellStyle = stylecon;
rowIndex++;
}
#endregion
for (int i = 0; i < sheet.GetRow(0).Cells.Count; i++)
{
sheet.AutoSizeColumn(i);
}
// code to create workbook
byte[] fileContents = null;
using (var memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
fileContents = memoryStream.ToArray();
}
return(fileContents);
}
public static void ListToSheetXlsx <T>(XSSFWorkbook workbook, List <T> list, string sheetName)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(sheetName);
XSSFCellStyle headStyle = workbook.GetHeadStyle();
//值类型直接返回第一列
Type tp = typeof(T);
//属性列表
PropertyInfo[] properties = tp.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
//property.Name是属性的英文名，怎么转换成中文？使用DescriptionAttribute特性
List <string> fieldStringArray = new List <string>();
List <PropertyInfo> propertiesUsed = new List <PropertyInfo>();
foreach (var property in properties)
{
if (Attribute.IsDefined(property, typeof(DescriptionAttribute)))
{
fieldStringArray.Add(property.GetEnumDescription());
propertiesUsed.Add(property);
}
}
int fieldCount = fieldStringArray.Count;
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
for (int i = 0; i < fieldCount; i++)
{
#region 表头及样式
headerRow.CreateCell(i).SetCellValue(fieldStringArray[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.AutoSizeColumn(i);
#endregion
}
var count = list.Count();
#region 单元格样式
ICellStyle styleCell = workbook.CreateCellStyle();
styleCell.Alignment = HorizontalAlignment.Center; //居中
styleCell.VerticalAlignment = VerticalAlignment.Center; //垂直居中
#endregion
for (int i = 0; i < count; i++)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i + 1);
var data = list[i];
for (int cellIndex = 0; cellIndex < fieldCount; cellIndex++)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(cellIndex, CellType.String);
var property = propertiesUsed[cellIndex];
if (Attribute.IsDefined(property, typeof(TimeAttribute)))
{
try
{
TimeSpan ts = new TimeSpan(0, 0, (int)property.GetValue(data));
StringBuilder sb = new StringBuilder();
if ((int)ts.TotalHours > 0)
{
sb.Append((int)ts.TotalHours + "h");
}
if (ts.Minutes > 0)
{
sb.Append(ts.Minutes + "m");
}
if (ts.Seconds > 0)
{
sb.Append(ts.Seconds + "s");
}
newCell.SetCellValue(sb.ToString());
}
catch (Exception ex)
{
ILogger logger = ServiceProviderServiceExtensions.GetRequiredService <ILogger>(
ServiceProviderExtension.ServiceProvider);
logger.LogError($"Second转换失败：" + ex.Source + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Message + Environment.NewLine + ex.InnerException);
newCell.SetCellValue(property.GetValue(data).ToString());
}
}
else
{
var propertyValue = property.GetValue(data);
if (propertyValue == null)
{
newCell.SetCellValue("");
}
else
{
newCell.SetCellValue(propertyValue.ToString());
}
}
newCell.CellStyle = styleCell;
}
}
//统一设置列宽度
sheet.SetColumnWidth(fieldCount);
}
///// <summary>
///// 清除时间
///// </summary>
///// <param name="sender"></param>
///// <param name="e"></param>
//private void ClearDatePickerTime(object sender, MouseButtonEventArgs e)
//{
// DatePicker dp = sender as DatePicker;
// dp.Text = "";
//}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void ExportExcelFile_Click(object sender, RoutedEventArgs e)
{
try
{
string saveExcelPath = ConfigurationManager.AppSettings["Excel保存地址"];
if (string.IsNullOrEmpty(saveExcelPath))
{
saveExcelPath = System.Windows.Forms.Application.StartupPath + @"\比对记录导出";
}
else
{
saveExcelPath += @"\比对记录导出";
}
await Task.Run(
() =>
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
XSSFCellStyle style2 = (XSSFCellStyle)workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style2.BorderBottom = BorderStyle.Thin;
style2.BorderLeft = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
style2.SetFont(font);
XSSFCellStyle style0 = (XSSFCellStyle)workbook.CreateCellStyle();
style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style0.BorderBottom = BorderStyle.Thin;
style0.BorderLeft = BorderStyle.Thin;
style0.BorderRight = BorderStyle.Thin;
style0.BorderTop = BorderStyle.Thin;
IDataFormat dataformat = workbook.CreateDataFormat();
style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 h:mm:ss 上午/下午");
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("比对记录");
int row = 0;
List <MyCmpFaceLogWidthImgModel> allCmpInfo = new List <MyCmpFaceLogWidthImgModel>();
allCmpInfo = GetCmpFaceLogByPage(1);
foreach (var item in allCmpInfo)
{
if (row == 0)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue("序号");
sheet.GetRow(row).CreateCell(1).SetCellValue("抓拍通道");
sheet.GetRow(row).CreateCell(2).SetCellValue("抓拍时间");
sheet.GetRow(row).CreateCell(3).SetCellValue("模版姓名");
sheet.GetRow(row).CreateCell(4).SetCellValue("注册类型");
sheet.GetRow(row).CreateCell(5).SetCellValue("相似度");
sheet.GetRow(row).CreateCell(6).SetCellValue("抓拍照片");
sheet.GetRow(row).CreateCell(7).SetCellValue("模版照片");
sheet.GetRow(row).GetCell(0).CellStyle = style2;
sheet.GetRow(row).GetCell(1).CellStyle = style2;
sheet.GetRow(row).GetCell(2).CellStyle = style2;
sheet.GetRow(row).GetCell(3).CellStyle = style2;
sheet.GetRow(row).GetCell(4).CellStyle = style2;
sheet.GetRow(row).GetCell(5).CellStyle = style2;
sheet.GetRow(row).GetCell(6).CellStyle = style2;
sheet.GetRow(row).GetCell(7).CellStyle = style2;
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
row++;
}
sheet.CreateRow(row).CreateCell(0).SetCellValue(row);
sheet.GetRow(row).Height = 100 * 40;
sheet.GetRow(row).CreateCell(1).SetCellValue(item.channelName);
sheet.GetRow(row).CreateCell(2).SetCellValue(item.time);
sheet.GetRow(row).CreateCell(3).SetCellValue(item.name);
sheet.GetRow(row).CreateCell(4).SetCellValue(item.type);
sheet.GetRow(row).CreateCell(5).SetCellValue(item.score);
sheet.GetRow(row).CreateCell(6).SetCellValue("");
sheet.GetRow(row).CreateCell(7).SetCellValue("");
sheet.GetRow(row).GetCell(0).CellStyle = style;
sheet.GetRow(row).GetCell(1).CellStyle = style;
sheet.GetRow(row).GetCell(2).CellStyle = style0;
sheet.GetRow(row).GetCell(3).CellStyle = style;
sheet.GetRow(row).GetCell(4).CellStyle = style;
sheet.GetRow(row).GetCell(5).CellStyle = style;
sheet.GetRow(row).GetCell(6).CellStyle = style;
sheet.GetRow(row).GetCell(7).CellStyle = style;
if (true)
{
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
List <byte[]> capImage = new List <byte[]>();
capImage = thirft.QueryCmpLogImageH(item.ID, exportCurrDay);
if (capImage.Count > 0)
{
byte[] bytes = capImage[0];
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 6, row, 7, row + 1);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
var faceObject = thirft.QueryFaceObj(item.tcUuid);
if (faceObject.Count > 0 && faceObject[0].Tmplate.Count > 0)
{
byte[] bytes2 = faceObject[0].Tmplate[0].Img;
int pictureIdx2 = workbook.AddPicture(bytes2, PictureType.JPEG);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 7, row, 8, row + 1);
XSSFPicture pict2 = (XSSFPicture)patriarch.CreatePicture(anchor2, pictureIdx2);
}
}
row++;
Thread.Sleep(1);
}
FileStream fs = new FileStream(saveExcelPath + ".xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fs);
workbook = null;
});
MB_MODULES.Views.MyMessage.showYes("导出完成!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 資料確認
/// </summary>
/// <param name="Str匯入資料Table">匯入資料表名稱</param>
public void F_LoadData(string Str匯入資料Table)
{
if (XSSworkbook != null || HSSworkbook != null)
{
//string str頁簽名稱 = "";
//int ISheetCheck = 0;//確認Sheet 資料是否正確，每份Sheet只會有一個對應
try
{
DTDefine = GetDBData("GetDefine", Str匯入資料Table);
//指定Import Sheet Name
string StrSheetNameCheck = "";
Boolean BCheck = false;
int I資料起始欄, I資料起始列;
DTError.Columns.Add("Error");
foreach (DataRow Dr in DTDefine.Rows)
{
if (Dr["IsUsing"].ToString() == "1")
{
DTImportData.Columns.Add(Dr["欄位名稱"].ToString());
}
}
if (DTImportData.Rows.Count > 0)
{
StrSheetNameCheck = (string.IsNullOrEmpty(DTImportData.Rows[0]["指定頁籤名稱"].ToString())) ? "" : DTImportData.Rows[0]["指定頁籤名稱"].ToString();
if (!bool.TryParse(DTImportData.Rows[0]["是否指定頁籤"].ToString(), out BCheck))
{
BCheck = false;
}
if (!int.TryParse(DTImportData.Rows[0]["資料起始列"].ToString(), out I資料起始列))
{
I資料起始列 = 1;
}
if (!int.TryParse(DTImportData.Rows[0]["資料起始欄"].ToString(), out I資料起始欄))
{
I資料起始欄 = 0;
}
if (Str副檔名.ToUpper() == ".XLSX")
{
for (int x = 0; x < XSSworkbook.NumberOfSheets; x++)
{
//-- 0表示：第一個 worksheet工作表
XSSFSheet u_sheet = (XSSFSheet)XSSworkbook.GetSheetAt(x);
Str匯入頁簽 = u_sheet.SheetName.ToString();
//檢查是否有要對應資料
if (BCheck && StrSheetNameCheck != Str匯入頁簽)
{
continue;
}
//else
//{
// ISheetCheck = +1;
//}
//-- Excel 表頭列
XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列);
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
F_資料確認(row, i, I資料起始欄);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
}
else
{
for (int x = 0; x < HSSworkbook.NumberOfSheets; x++)
{
HSSFSheet u_sheet = (HSSFSheet)HSSworkbook.GetSheetAt(x); //-- 0表示：第一個 worksheet工作表
HSSFRow headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列); //-- Excel 表頭列
IRow DateRow = (IRow)u_sheet.GetRow(I資料起始列); //-- v.1.2.4版修改
//檢查是否有要對應資料
if (BCheck && StrSheetNameCheck != Str匯入頁簽)
{
continue;
}
//else
//{
// ISheetCheck = +1;
//}
Str匯入頁簽 = u_sheet.SheetName.ToString();
for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++) //-- 每一列做迴圈
{
//--不包含 Excel表頭列的 "其他資料列"
IRow row = (IRow)u_sheet.GetRow(i);
F_資料確認(row, i, I資料起始欄);
}
//-- 釋放 NPOI的資源
u_sheet = null;
}
}
//--錯誤資料顯示
DataView D_View3 = new DataView(DTError);
if (DTError.Rows.Count > 0)
{
//if (str頁簽名稱 == "AMZCapacity")
//{
// //CapacityErrorGV.DataSource = D_View3;
// //CapacityErrorGV.DataBind();
//}
//else
//{
// //GuidanceErrorGV.DataSource = D_View3;
// //GuidanceErrorGV.DataBind();
//}
//if (ISheetCheck != 1)
//{
// //F_ErrorShow(str頁簽名稱 + "Sheet 比對失敗");
// StrErrorShow = Str頁簽名稱 + "Sheet 比對失敗";
//}
}
if (this.DTImportData.Rows.Count > 0)
{
if (DTError.Rows.Count == 0)
{
//Session[StrExcelSheet] = this.DTImportData;
}
}
}
else
{
StrErrorShow = "Please contact Mis : Import format is not defined";
}
}
catch (Exception ex)
{
StrErrorShow = $"Error: {ex.Message}";
}
}
else
{
StrErrorShow = "Data check fail";
}
}
public XSSFRowShifter(XSSFSheet sh)
{
sheet = sh;
}
public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData)
{
MemoryStream ms = new MemoryStream();
if (header != null && header.Rows.Count > 0)
{
MDataTable importHeader = header.FindAll("Import=1");
try
{
XSSFWorkbook export = new XSSFWorkbook();
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
#region 创建引用
int rowStartIndex = 1;
CreateValidationSheet(export, validateData, rowStartIndex);
#endregion
importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%')
MDataTable headTable = importHeader.Clone();
int ColTitleRowCount = 0;
Dictionary <string, int> formatdic = new Dictionary <string, int>();
for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE
{
if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1)
{
importHeader.Rows.RemoveAt(i);//非字段列移除
}
}
int colSum = importHeader.Rows.Count;//实际列数
importHeader.Rows.Sort("ORDER BY OrderNum ASC");
if (!ExportMulHeader(header, true))
{
IRow row = sheet.CreateRow(0);
ICell cell;
for (int i = 0; i < colSum; i++)
{
string title = importHeader.Rows[i]["Title"].Value.ToString();
cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum);
ColTitleRowCount -= 1;
}
for (int i = 0; i < importHeader.Rows.Count; i++)
{
string formater = importHeader.Rows[i].Get <string>("Formatter");
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 50000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
XSSFSheet xssfSheet = (XSSFSheet)sheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
for (int i = 0; i < importHeader.Rows.Count; i++)
{
MDataRow dtRow = importHeader.Rows[i];
string formatter = dtRow.Get <string>("Formatter");
if (formatter == "boolFormatter")
{
formatter = "#是否"; //对bool型特殊处理。
}
if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
{
//处理数据的有效性
CellRangeAddressList regions = null;
IDataValidationConstraint constraint = null;
IDataValidation dataValidate = null;
//int maxRow = 65535;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
/*03版本api
* constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
* dataValidate = new HSSFDataValidation(regions, constraint);
*/
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i);
//string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
//constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
//dataValidate = new HSSFDataValidation(regions, constraint);
//sheet.AddValidationData(dataValidate);
}
//
if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
{
string Parentformatter = formatter;
while (formatdic.ContainsKey(Parentformatter))
{
int point = 0;
int parentindex = formatdic[Parentformatter];
formatdic.Remove(Parentformatter);
foreach (var item in formatdic)
{
if (item.Key.IndexOf('=') > -1)
{
string parent = item.Key.Split('=')[1];
parent = parent.Replace(">", "#");
if (parent.Equals(Parentformatter.Split('=')[0]))
{
int selfindex = item.Value;
//int parentindex = formatdic[hereformatter];
string t = IntToMoreChar(parentindex);
for (int im = ColTitleRowCount; im < maxRow; im++)
{
string func = string.Format("@INDIRECT({0}{1})", t, im + 1);
regions = new CellRangeAddressList(im, im, selfindex, selfindex);
constraint = dvHelper.CreateFormulaListConstraint(func);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
//for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow
//{
// string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联，03可以，其他没测过
// regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex);
// constraint = DVConstraint.CreateFormulaListConstraint(func);
// dataValidate = new HSSFDataValidation(regions, constraint);
// sheet.AddValidationData(dataValidate);
//}
Parentformatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
Parentformatter = string.Empty;
}
}
}
}
}
export.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(ms);
}
/**
* Parse a given excel file containing interested addresses data
* and extract needfull data (complete address, territory number
* and appartment information). A specific row formatting is applied
* depending on the rows contents.
*
* param name="sourceFile" is the location of the file to parse.
*/
public void ParseTerritoryHelperInterestedFile(String sourceFile)
{
XSSFWorkbook wb;
// Reading some configuration/initialization data...
Console.WriteLine("Reading some configuration/initialization data...");
string excelFileLocation = System.Configuration.ConfigurationManager.AppSettings["sourceFilePath"];
string lastDataColumnsCell = System.Configuration.ConfigurationManager.AppSettings["excel-last-data-column"];
string wantedColumnsListString = System.Configuration.ConfigurationManager.AppSettings["excel-wanted-columns"];
// If the default source file location should be overwritten...
if (sourceFile != null)
{
// Overwrite the default source file location
excelFileLocation = sourceFile;
}
// Wait until anexcel addresses file is available at the location defined by 'excelFileLocation'
int count = 0;
while (!File.Exists(excelFileLocation) || IsFileLocked(excelFileLocation))
{
if (count == 0)
{
Console.WriteLine("Wait until an excel addresses file is available at " + excelFileLocation + "...");
}
count++;
}
using (FileStream file = new FileStream(@excelFileLocation, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
XSSFSheet sh = (XSSFSheet)wb.GetSheetAt(0);
int rowCount = sh.LastRowNum + 1;
Console.WriteLine("Source file row count = " + rowCount);
//Console.WriteLine("Source file columns count = " + sh.GetColumnHelper().lastDataColumnsCell);
SetSheetSpecialConditonalFormatting(sh);
Console.WriteLine("Setting columns auto filters...");
// Set the columns auto filters
sh.SetAutoFilter(CellRangeAddress.ValueOf("A1" + ":" + lastDataColumnsCell + rowCount));
// Get the list of column which should remain in the resulting output.
List <int> wantedColumnsList = wantedColumnsListString.Split(',').Select(int.Parse).ToList();
int columnCount = AlphabetOrdinal(lastDataColumnsCell);
Console.WriteLine("Hiding unnecessary columns...");
// Hide all not wanted columns
for (int i = 0; i < columnCount; i++)
{
if (!wantedColumnsList.Contains(i))
{
sh.SetColumnHidden(i, true);
}
}
// Freeze first row ( headers )
sh.CreateFreezePane(0, 1);
string fileLocation = System.Configuration.ConfigurationManager.AppSettings["destinationFilePath"];
//string fileLocation = "C:\\Development\\c-sharp\\ExcelNPOI\\adresses-bielefeld.xlsx";
Console.WriteLine("Writing Resulting Excel File to disk: " + fileLocation);
using (var stream = new FileStream(fileLocation, FileMode.Create, FileAccess.Write))
{
wb.Write(stream);
}
//Console.WriteLine("Opening the resulting processed file...");
//Process.Start(fileLocation);
}
/// <summary>
/// DataSet 을 Excel 파일로 저장한다.
/// [ NPOI 라이브러리를 이용한 컨버팅 작업 진행중 ]
/// </summary>
/// <param name="FileName">
/// Excel File 명 PullPath
/// </param>
/// <param name="DS">
/// Excel 로 저장할 대상 DataSet 객체.
/// </param>
/// <param name="ExistDel">
/// 동일한 파일명이 있을 때 삭제 할 것인지 여부, 파일이 있고 false 면 저장안하고 그냥 false 를 리턴.
/// </param>
/// <param name="OldExcel">
/// xls 형태로 저장할 것인지 여부, false 이면 xlsx 형태로 저장함.
/// </param>
private static bool SaveExcel(string FileName, DataSet DS, bool ExistDel, bool OldExcel)
{
bool result = true;
if (File.Exists(FileName))
{
if (ExistDel)
{
File.Delete(FileName);
}
else
{
return(result);
}
}
string TempFile = FileName;
// 파일 확장자가 xls 이나 xlsx 가 아니면 아예 파일을 안만들어서
// 템프파일로 생성후 지정한 파일명으로 변경..
try
{
XSSFWorkbook WB = new XSSFWorkbook();
foreach (DataTable DT in DS.Tables)
{
XSSFSheet WS = WB.CreateSheet(DT.TableName) as XSSFSheet;
XSSFRow HR = WS.CreateRow(0) as XSSFRow;
XSSFCellStyle HeaderStyle = WB.CreateCellStyle() as XSSFCellStyle;
HeaderStyle.Alignment = HorizontalAlignment.Center;
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
XSSFFont HeaderFont = WB.CreateFont() as XSSFFont;
HeaderFont.FontHeightInPoints = 11;
HeaderFont.FontName = "맑은 고딕";
HeaderFont.Boldweight = (short)FontBoldWeight.Bold;
HeaderStyle.SetFont(HeaderFont);
int[] ColumnWidths = new int[] { 9, 12, 8, 8, 45, 45, 14 };
// 엑셀의 헤더 부분(DataTable의 Columns 기록) 정의 및 출력
for (int i = 0; i < DT.Columns.Count; i++)
{
XSSFCell HC = HR.CreateCell(i) as XSSFCell;
HC.SetCellValue(DT.Columns[i].ColumnName);
HC.CellStyle = HeaderStyle;
WS.SetColumnWidth(i, (int)((ColumnWidths[i] + 0.72) * 256));
}
XSSFCellStyle BodyStyle = WB.CreateCellStyle() as XSSFCellStyle;
BodyStyle.Alignment = HorizontalAlignment.Center;
BodyStyle.VerticalAlignment = VerticalAlignment.Center;
BodyStyle.WrapText = true;
XSSFFont BodyFont = WB.CreateFont() as XSSFFont;
BodyFont.FontHeightInPoints = 11;
BodyFont.FontName = "맑은 고딕";
BodyFont.Boldweight = (short)FontBoldWeight.None;
BodyStyle.SetFont(BodyFont);
// 엑셀의 바디 부분(Datable의 Rows 기록) 정의 및 출력
int RowCount = 0;
foreach (DataRow DR in DT.Rows)
{
XSSFRow BR = WS.CreateRow(++RowCount) as XSSFRow;
BR.HeightInPoints = 28 * Math.Max(DR["상점 내역"].ToString().Split('\n').Length, DR["벌점 내역"].ToString().Split('\n').Length);
int ColumnCount = 0;
foreach (DataColumn DC in DT.Columns)
{
XSSFCell BC = BR.CreateCell(ColumnCount++) as XSSFCell;
BC.SetCellValue(DR[DC.ColumnName] as string);
BC.CellStyle = BodyStyle;
}
}
}
using (FileStream FS = new FileStream(TempFile, FileMode.Create, FileAccess.Write))
{
WB.Write(FS);
}
}
catch (Exception)
{
result = false;
}
return(result);
}
