public static double getTotalHeight(IXLWorksheet ws, int startRow)
{
var totalHeight = 0.0;
foreach (var row in ws.Rows(startRow, ws.LastRowUsed().RowNumber()))
{
totalHeight += row.Height;
}
return totalHeight;
}
public void AddContainerInfo(IXLWorksheet ws, Container container)
{
ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20;
ws.Range("A1:E1").Merge();
ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true;
ws.Range("A2:B3").Merge();
ws.Cell("A5").SetValue("Shipped Per");
ws.Cell("B5").SetValue(container.ShippedPer);
ws.Range("B5:C5").Merge();
ws.Cell("A6").SetValue("On/About");
ws.Cell("B6").SetValue(container.OnAbout);
ws.Range("B6:C6").Merge();
ws.Cell("A7").SetValue("From");
ws.Cell("B7").SetValue(container.From);
// ws.Row(7)
// .Style
// .Alignment.SetVertical(XLAlignmentVerticalValues.Top)
// .Alignment.SetWrapText(true);
ws.Range("B7:C7").Merge();
ws.Row(7).Height = 70;
ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No.");
ws.Cell("B8").SetValue(container.AirwayBillNumber);
ws.Range("B8:C8").Merge();
ws.Row(8).Height = 30;
// ws.Row(8).Style.Alignment.SetWrapText(true);
ws.Cell("A9").SetValue("Letter of\nCredit No.");
ws.Cell("B9").SetValue(container.LetterOfCreditNumber);
ws.Range("B9:C9").Merge();
ws.Row(9).Height = 30;
ws.Cell("A10").SetValue("Drawn Under");
ws.Cell("B10").SetValue(container.DrawnUnder)
.Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// .Alignment.SetWrapText(true);
ws.Range("B10:C10").Merge();
ws.Row(10).Height = 70;
// ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Range("A1:A10").Style.Font.Bold = true;
ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
// ws.Range("B5:C10").Style.Alignment.WrapText = true;
ws.Rows("5:10").Style.Alignment.SetWrapText(true)
.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
//Importer
ws.Cell("E5").SetValue(container.ImporterName +
"\n" + container.ImporterAddress +
"\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")")
.Style
.Alignment.SetVertical(XLAlignmentVerticalValues.Top)
.Alignment.SetWrapText();
ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
//Container Number + Container Date
ws.Cell("F2").SetValue("INVOICE NO:");
ws.Cell("G2").SetValue(container.CustomsInvoiceNumber);
ws.Cell("F3").SetValue("DATE:");
ws.Cell("G3").SetValue(container.Date);
ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range("G2:H2").Merge();
ws.Range("G3:H3").Merge();
ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Range("F2:H3").Style.Font.Bold = true;
}
void OutputOverdues(IXLWorksheet sheet,
XmlDocument dom,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("overdues/overdue");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 费用 --- " + nodes.Count,
XLColor.DarkRed,
2,
6);
int nRet = 0;
List<IXLCell> cells = new List<IXLCell>();
// 栏目标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("说明");
titles.Add("金额");
titles.Add("ID");
#if NO
titles.Add("以停代金情况");
titles.Add("起点日期");
titles.Add("期限");
titles.Add("终点日期");
#endif
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strReason = borrow.GetAttribute("reason");
string strPrice = borrow.GetAttribute("price");
string strID = borrow.GetAttribute("id");
string strRecPath = borrow.GetAttribute("recPath");
string strSummary = borrow.GetAttribute("summary");
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
nRet = this.MainForm.GetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strReason);
cols.Add(strPrice);
cols.Add(strID);
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
nColIndex++;
cells.Add(cell);
}
nItemIndex++;
nRowIndex++;
}
// 标题行下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
// parameters:
// bAdvanceXml 是否为 AdvanceXml 情况
static void OutputBorrowHistory(
IXLWorksheet sheet,
XmlDocument reader_dom,
ChargingHistoryLoader history_loader,
CacheableBiblioLoader summary_loader,
// Delegate_GetBiblioSummary procGetBiblioSummary,
ref int nRowIndex,
ref List<int> column_max_chars)
{
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 借阅历史 --- " + history_loader.GetCount(),
XLColor.DarkGreen,
2,
7);
List<IXLCell> cells = new List<IXLCell>();
// 册信息若干行的标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("借阅时间");
titles.Add("期限");
titles.Add("借阅操作者");
titles.Add("还书时间");
titles.Add("还书操作者");
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
List<string> item_barcodes = new List<string>();
List<Point> points = new List<Point>();
int nItemIndex = 0;
foreach (ChargingItemWrapper wrapper in history_loader)
{
ChargingItem item = wrapper.Item;
ChargingItem rel = wrapper.RelatedItem;
string strItemBarcode = item.ItemBarcode;
string strBorrowDate = rel == null ? "" : rel.OperTime;
string strBorrowPeriod = GetDisplayTimePeriodString(rel == null ? "" : rel.Period);
string strReturnDate = item.OperTime;
//string strRecPath = borrow.GetAttribute("recPath");
//string strIsOverdue = borrow.GetAttribute("isOverdue");
//bool bIsOverdue = DomUtil.IsBooleanTrue(strIsOverdue, false);
//string strOverdueInfo = borrow.GetAttribute("overdueInfo1");
string strSummary = "";
#if NO
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
int nRet = procGetBiblioSummary(strItemBarcode,
"", // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
#endif
item_barcodes.Add("@itemBarcode:" + strItemBarcode);
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strBorrowDate);
cols.Add(strBorrowPeriod);
cols.Add(rel == null ? "" : rel.Operator);
cols.Add(strReturnDate);
cols.Add(item.Operator);
int nColIndex = 2;
points.Add(new Point(nColIndex + 2, nRowIndex));
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
#if NO
// 超期的行为黄色背景
if (bIsOverdue)
{
var line = sheet.Range(nRowIndex, 2, nRowIndex, 2 + cols.Count - 1);
line.Style.Fill.BackgroundColor = XLColor.Yellow;
}
#endif
nItemIndex++;
nRowIndex++;
}
// 加入书目摘要
summary_loader.RecPaths = item_barcodes;
int i = 0;
foreach (BiblioItem biblio in summary_loader)
{
Point point = points[i];
int nColIndex = point.X;
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(biblio.Content));
IXLCell cell = null;
cell = sheet.Cell(point.Y, nColIndex).SetValue(biblio.Content);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
i++;
}
// 册信息标题下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
void OutputBorrows(IXLWorksheet sheet,
XmlDocument dom,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("borrows/borrow");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 在借 --- " + nodes.Count,
XLColor.DarkGreen,
2,
7);
List<IXLCell> cells = new List<IXLCell>();
// 册信息若干行的标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("借阅时间");
titles.Add("借期");
titles.Add("应还时间");
titles.Add("是否超期");
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strBorrowDate = ToLocalTime(borrow.GetAttribute("borrowDate"), "yyyy-MM-dd HH:mm");
string strBorrowPeriod = GetDisplayTimePeriodString(borrow.GetAttribute("borrowPeriod"));
string strReturningDate = ToLocalTime(borrow.GetAttribute("returningDate"), "yyyy-MM-dd");
string strRecPath = borrow.GetAttribute("recPath");
string strIsOverdue = borrow.GetAttribute("isOverdue");
bool bIsOverdue = DomUtil.IsBooleanTrue(strIsOverdue, false);
string strOverdueInfo = borrow.GetAttribute("overdueInfo1");
string strSummary = borrow.GetAttribute("summary");
#if NO
nRet = this.MainForm.GetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
#endif
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strBorrowDate);
cols.Add(strBorrowPeriod);
cols.Add(strReturningDate);
if (bIsOverdue)
cols.Add(strOverdueInfo);
else
cols.Add("");
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
// 超期的行为黄色背景
if (bIsOverdue)
{
var line = sheet.Range(nRowIndex, 2, nRowIndex, 2 + cols.Count - 1);
line.Style.Fill.BackgroundColor = XLColor.Yellow;
}
nItemIndex++;
nRowIndex++;
}
// 册信息标题下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
#if NO
// 第一行上面的横线
rngData = sheet.Range(cell_no, cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Medium;
#endif
sheet.Rows(nStartRow + 1, nRowIndex-1).Group();
}
public static void CLSetDefaultHeight(this IXLWorksheet @this, int headerHeight = 30, int rowsHeight = 20, XLColor back = null)
{
@this.Rows().Height = rowsHeight;
@this.Row(1).Height = headerHeight;
}
public IXLRange CopyTo(IXLRange range)
{
// LastCellUsed may produce the wrong result, see https://github.com/ClosedXML/ClosedXML/issues/339
var lastCell = _sheet.Cell(
_sheet.LastRowUsed(true).RowNumber(),
_sheet.LastColumnUsed(true).ColumnNumber());
var tempRng = _sheet.Range(_sheet.Cell(1, 1), lastCell);
var rowDiff = tempRng.RowCount() - range.RowCount();
if (rowDiff > 0)
{
range.InsertRowsBelow(rowDiff, true);
}
else if (rowDiff < 0)
{
range.Worksheet.Range(
range.LastRow().RowNumber() + rowDiff + 1,
range.FirstColumn().ColumnNumber(),
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsUp);
}
range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range));
var columnDiff = tempRng.ColumnCount() - range.ColumnCount();
if (columnDiff > 0)
{
range.InsertColumnsAfter(columnDiff, true);
}
else if (columnDiff < 0)
{
range.Worksheet.Range(
range.FirstRow().RowNumber(),
range.LastColumn().ColumnNumber() + columnDiff + 1,
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsLeft);
}
tempRng.CopyTo(range.FirstCell());
var tgtSheet = range.Worksheet;
var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;
using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber))
foreach (var row in srcRows)
{
var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
xlRow.OutlineLevel = row.OutlineLevel;
if (row.IsHidden)
{
xlRow.Collapse();
}
else
{
xlRow.Expand();
}
}
return(range);
}
/// <summary>
/// Create a worksheet
/// </summary>
/// <param name="worksheet">IXLWorksheet</param>
/// <param name="sheet">CExcelSheet</param>
private void CreateAWorkSheet(IXLWorksheet worksheet, CExcelSheet sheet)
{
//title
IXLAddress firstAdd = worksheet.Cell(1, COL_BEGIN).Address;
worksheet.Cell(1, 1).Value = sheet.Title;
worksheet.Cell(1, 1).Style.Font.Bold = true;
worksheet.Cell(1, 1).Style.Font.FontSize = 15;
worksheet.Cell(1, 1).Style.Font.FontColor = XLColor.White;
worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0x0066cc);
#region Export By
int rowIdx = ROW_BEGIN;
if (sheet.ExportBy != null)
{
foreach (var item in sheet.ExportBy)
{
// reset column
int colIdx = COL_BEGIN;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.key;
worksheet.Range(
worksheet.Cell(rowIdx, colIdx).Address,
worksheet.Cell(rowIdx, colIdx + 2).Address).Merge();
colIdx += 3;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.value;
// new row
rowIdx++;
}
}
#endregion
// Header
int col = COL_BEGIN;
#region Header
//Add No
if (sheet.IsRenderNo)
{
worksheet.Cell(rowIdx, col).Value = "No";
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(rowIdx, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string header in sheet.Header)
{
worksheet.Cell(rowIdx, col).Value = header;
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for header
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
//merge title
IXLAddress secondAdd = worksheet.Cell(1, col - 1).Address;
worksheet.Range(firstAdd, secondAdd).Merge();
worksheet.Range(firstAdd, secondAdd).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Merge export by
for (int i = ROW_BEGIN; i < rowIdx; i++)
{
worksheet.Range(
worksheet.Cell(i, 4).Address,
worksheet.Cell(i, col - 1).Address).Merge();
}
#endregion
// detail
// Comment when adding export by, rowIdx is the number of export by
//int idx_row = ROW_BEGIN + 1;
int idx_row = rowIdx + 1;
string preGroup = string.Empty;
ArrayList beginSubList = new ArrayList();
ArrayList endSubList = new ArrayList();
//linh.quang.le: Freeze panels
worksheet.SheetView.FreezeRows(sheet.FreezeRow);
worksheet.SheetView.FreezeColumns(sheet.FreezeColumn);
//linh.quang.le number
int no = 1;
#region Detail
foreach (Object row in sheet.List)
{
int idx_col = COL_BEGIN;
int index = 0;
bool hasMainColumnValue = HasMainColumnValue(row, sheet);
//linh.quang.le
#region GroupName
if (sheet.IsGroup)
{
string groupName = string.Empty;
groupName = row.GetType().GetProperty(sheet.GroupName).GetValue(row, null).ToString();
if (!String.IsNullOrEmpty(groupName) && preGroup != groupName)
{
if (beginSubList.Count != 0)
endSubList.Add(idx_row - 1);
worksheet.Cell(idx_row, COL_BEGIN).Value = groupName;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.Bold = true;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontSize = 12;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontColor = XLColor.Black;
worksheet.Cell(idx_row, COL_BEGIN).Style.Fill.BackgroundColor = XLColor.FromArgb(0xD9D9D9);
IXLAddress firstGroupAddr = worksheet.Cell(idx_row, COL_BEGIN).Address;
IXLAddress secondGroupAddr = worksheet.Cell(idx_row, (COL_BEGIN + (sheet.IsRenderNo ? sheet.ColumnList.Length : sheet.ColumnList.Length - 1))).Address;
worksheet.Range(firstGroupAddr, secondGroupAddr).Merge();
worksheet.Range(firstGroupAddr, secondGroupAddr).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
preGroup = groupName;
no = 1;
idx_row++;
beginSubList.Add(idx_row);
}
if (sheet.IsRenderNo && hasMainColumnValue)
{
worksheet.Cell(idx_row, idx_col).Value = no.ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
no++;
idx_col++;
}
}
else
{
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, idx_col).Value = (idx_row - ROW_BEGIN).ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
idx_col++;
}
}
#endregion
if (sheet.IsGroup)
{
if (hasMainColumnValue)
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Mangement Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
else
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Management Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
if (sheet.IsGroup)
{
if (!String.IsNullOrEmpty(sheet.GroupName))
{
for (int i = 0; i < beginSubList.Count; i++)
{
worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
if (i >= endSubList.Count)
worksheet.Rows((int)beginSubList[i], idx_row - 1).Group();
else
worksheet.Rows((int)beginSubList[i], (int)endSubList[i]).Group();
}
}
}
#endregion
#region Footer
if (sheet.Footer != null)
{
col = COL_BEGIN;
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, col).Value = "Total";
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string footer in sheet.Footer)
{
worksheet.Cell(idx_row, col).Value = footer;
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for footer
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
}
#endregion
}
public ResponseResult Import(HttpPostedFileBase file, int CTDTID)
{
ResponseResult res = null;
string s = "";
try
{
List <HocPhanTheoCTDTModel> dt = new List <HocPhanTheoCTDTModel>();
file.InputStream.Position = 0;
var memoryStream = new MemoryStream();
file.InputStream.CopyTo(memoryStream);
using (XLWorkbook workBook = new XLWorkbook(memoryStream))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
foreach (IXLRow row in workSheet.Rows())
{
int i = 0;
string check = row.Cell(3).Value.ToString();
if (!String.IsNullOrEmpty(check) && int.TryParse(check, out i) && int.Parse(check) > 0)
{
var a = new HocPhanTheoCTDTModel();
a.ChuongTrinhDaoTaoID = CTDTID;
a.MaHP = row.Cell(3).Value.ToString();
a.HocKi = "";
if (!String.IsNullOrEmpty(row.Cell(5).Value.ToString()))
{
a.HocKi += "1,";
}
if (!String.IsNullOrEmpty(row.Cell(6).Value.ToString()))
{
a.HocKi += "2,";
}
if (!String.IsNullOrEmpty(row.Cell(7).Value.ToString()))
{
a.HocKi += "3,";
}
if (!String.IsNullOrEmpty(row.Cell(8).Value.ToString()))
{
a.HocKi += "4,";
}
if (!String.IsNullOrEmpty(row.Cell(9).Value.ToString()))
{
a.HocKi += "5,";
}
if (!String.IsNullOrEmpty(row.Cell(10).Value.ToString()))
{
a.HocKi += "6,";
}
if (!String.IsNullOrEmpty(row.Cell(11).Value.ToString()))
{
a.HocKi += "7,";
}
if (!String.IsNullOrEmpty(row.Cell(12).Value.ToString()))
{
a.HocKi += "8,";
}
if (!String.IsNullOrEmpty(row.Cell(13).Value.ToString()))
{
a.HocKi += "9,";
}
if (a.HocKi != "")
{
a.HocKi = a.HocKi.Remove(a.HocKi.LastIndexOf(','));
}
a.NguoiTao = AccountUtils.CurrentUsername();
if (dt.Where(x => x.MaHP == a.MaHP) != null)
{
dt.Add(a);
}
foreach (var item in dt)
{
if (dt.Where(x => x.MaHP == a.MaHP) != null)
{
item.HocKi = a.HocKi;
break;
}
}
}
}
}
foreach (var item in dt)
{
res = HocPhanTheoCTDT_DA.ThemHocPhanTheoCTDT(item);
if (res.ResponseMessage != null)
{
s += res.ResponseMessage + ", ";
}
else if (res == null && dt.Count > 0)
{
break;
}
}
if (s != "")
{
s = s.Remove(s.LastIndexOf(','));
if (res != null)
{
res.ResponseMessage = s;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(res);
}
/// -----------------------------------------------------------------------------
/// <summary>
/// DataTableをもとにxlsxファイルを作成し、PDFファイルを作成
/// </summary>
/// <param name="dtHachu">発注のデータテーブル</param>
/// <returns>結合PDFファイル</returns>
/// -----------------------------------------------------------------------------
public string dbToPdf(DataTable dtHachu)
{
string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"];
string strFilePath = "./Template/A0120_ChumonShoPrint.xlsx";
string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
// excelのインスタンス生成
XLWorkbook workbook = new XLWorkbook(strFilePath, XLEventTracking.Disabled);
IXLWorksheet templatesheet = workbook.Worksheet(1); // テンプレートシート
IXLWorksheet currentsheet = null; // 処理中シート
int pageCnt = 0; // ページ(シート枚数)カウント
int xlsRowCnt = 11; // Excel出力行カウント（開始は出力行）
Boolean blnSheetCreate = false;
string strTorihikisakiCd = "";
string strHachusha = "";
// ClosedXMLで1行ずつExcelに出力
foreach (DataRow drHachu in dtHachu.Rows)
{
// 取引先コードが前行と違う場合、テンプレートシート作成
if (!strTorihikisakiCd.Equals(drHachu[1].ToString()))
{
// 取引先コードが空でない場合
if (!strTorihikisakiCd.Equals(""))
{
// 明細行が17行、18行の場合
if (xlsRowCnt == 28 || xlsRowCnt == 29)
{
// 不要な明細行、発注者行を削除
currentsheet.Rows(xlsRowCnt, 33).Delete();
pageCnt++;
xlsRowCnt = 11;
// テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー
templatesheet.CopyTo(templatesheet.Name);
}
// 不要な明細行を削除
currentsheet.Rows(xlsRowCnt, 28).Delete();
// 発注者
currentsheet.Cell(xlsRowCnt + 2, "K").Value = strHachusha;
currentsheet.Range(xlsRowCnt + 2, 11, xlsRowCnt + 4, 11).Merge();
}
strTorihikisakiCd = drHachu[1].ToString(); // 取引先コード
strHachusha = drHachu[11].ToString(); // 発注者
pageCnt++;
xlsRowCnt = 11;
blnSheetCreate = true;
// テンプレートシートからコピー
templatesheet.CopyTo("Page" + pageCnt.ToString());
currentsheet = workbook.Worksheet(workbook.Worksheets.Count);
}
// 明細行が19行になった場合
if (xlsRowCnt == 29)
{
// 発注者行を削除
currentsheet.Rows(xlsRowCnt, 33).Delete();
pageCnt++;
xlsRowCnt = 11;
// テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー
templatesheet.CopyTo(templatesheet.Name);
}
// 最初の明細行の場合
if (blnSheetCreate)
{
blnSheetCreate = false;
currentsheet.Cell("A4").Value = drHachu[2].ToString(); // 取引先名
currentsheet.Cell("B6").Value = drHachu[3].ToString(); // 電話番号
currentsheet.Cell("B7").Value = drHachu[4].ToString(); // FAX
currentsheet.Cell("E3").Value = drHachu[0].ToString(); // 年月日
currentsheet.Cell("K3").Value = drHachu[13].ToString(); // 営業所名
}
currentsheet.Cell(xlsRowCnt, "A").Value = drHachu[5].ToString(); // 商品名
currentsheet.Cell(xlsRowCnt, "E").Value = drHachu[6].ToString(); // 数量
currentsheet.Cell(xlsRowCnt, "F").Value = drHachu[7].ToString(); // 単価
currentsheet.Cell(xlsRowCnt, "G").Value = drHachu[8].ToString(); // 金額
currentsheet.Cell(xlsRowCnt, "I").Value = drHachu[9].ToString(); // 納期
currentsheet.Cell(xlsRowCnt, "J").Value = drHachu[10].ToString(); // 注文番号
currentsheet.Cell(xlsRowCnt, "K").Value = "'" + drHachu[12].ToString(); // 備考
xlsRowCnt++;
}
// 発注データがある場合
if (dtHachu.Rows.Count > 0)
{
// 明細行が17行、18行の場合
if (xlsRowCnt == 28 || xlsRowCnt == 29)
{
// 不要な明細行、発注者行を削除
currentsheet.Rows(xlsRowCnt, 33).Delete();
pageCnt++;
xlsRowCnt = 11;
// テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー
templatesheet.CopyTo(templatesheet.Name);
//this.sheetCopy(ref workbook, ref currentsheet, templatesheet, pageCnt);
}
// 不要な明細行を削除
currentsheet.Rows(xlsRowCnt, 28).Delete();
// 発注者
currentsheet.Cell(xlsRowCnt + 2, "K").Value = strHachusha;
currentsheet.Range(xlsRowCnt + 2, 11, xlsRowCnt + 4, 11).Merge();
}
// テンプレートシート削除
templatesheet.Delete();
// workbookを保存
string strOutXlsFile = strWorkPath + strDateTime + ".xlsx";
workbook.SaveAs(strOutXlsFile);
// workbookを解放
workbook.Dispose();
// CreatePdfのインスタンス生成
CreatePdf pdf = new CreatePdf();
// ロゴ貼り付け処理
int[] topRow = { 2 };
int[] leftColumn = { 8 };
pdf.logoPaste(strOutXlsFile, topRow, leftColumn, 200, 850, 57);
// PDF化の処理
return(pdf.createPdf(strOutXlsFile, strDateTime));
}
catch
{
throw;
}
finally
{
//// Workフォルダの作成日時ファイルを取得
//string[] files = Directory.GetFiles(strWorkPath, strDateTime + "*", SearchOption.AllDirectories);
//// Workフォルダ内のファイル削除
//foreach (string filepath in files)
//{
// File.Delete(filepath);
//}
}
}
public static Dictionary <string, string> ExportToExcel <T>(string fileName, string worksheetName, List <T> dataFromService, List <string> isCenter = null) where T : class
{
string sourcePath = Conf.DocumentFolder + fileName;
Dictionary <string, string> dt = new Dictionary <string, string>();
string columnName = "", fldName = "", fileExcel = "", startSheet = "", endSheet = "", cell = "";
int iSheetValue = 0;
#region temp variable
DateTime tmpDate; int tmpInt; decimal tmpDecimal;
#endregion
if (worksheetName == String.Empty)
{
worksheetName = "Sheet1";
}
var allData = dataFromService;
Dictionary <string, object> data2 = new Dictionary <string, object>();
//new workbook
XLWorkbook workBook = new XLWorkbook();
//worksheet
IXLWorksheet sheet = workBook.Worksheets.Add(worksheetName);
var data = Library.GetFieldAndType(typeof(T));
List <Dictionary <string, string> > col = new List <Dictionary <string, string> >();
foreach (var items in data)
{
Dictionary <string, string> tmpData = new Dictionary <string, string>();
tmpData.Add("fldName", items["fldName"]);
tmpData.Add("fldType", items["fldType"]);
col.Add(tmpData);
}
int i = 1;
int counter = 0;
foreach (Dictionary <string, string> x in col)
{
cell = Library.GetColumnExcel(i);
columnName = Library.GetColumnExcel(i) + "1";
fldName = x["fldName"];
sheet.Cell(columnName).Value = fldName;
iSheetValue = 2;
if (counter == 0)
{
startSheet = columnName;
}
else if (counter == col.Count - 1)
{
endSheet = columnName;
}
int countIndex = 0;
foreach (var row in allData)
{
columnName = Library.GetColumnExcel(i) + iSheetValue;
var rowValue = row.GetType().GetProperty(fldName).GetValue(allData[countIndex]).ToString();
sheet.Cell(columnName).Value = rowValue;
iSheetValue++;
if (DateTime.TryParse(rowValue, out tmpDate))
{
sheet.Cell(columnName).Style.DateFormat.Format = "dd-MMM-yyyy";
sheet.Cell(columnName).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else if (decimal.TryParse(rowValue, out tmpDecimal))
{
sheet.Cell(columnName).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
else if (int.TryParse(rowValue, out tmpInt))
{
sheet.Cell(columnName).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
if (isCenter != null)
{
foreach (var item in isCenter)
{
if (item == fldName)
{
if (DateTime.TryParse(rowValue, out tmpDate))
{
sheet.Cell(columnName).Style.DateFormat.Format = "dd-MMM-yyyy";
sheet.Cell(columnName).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
{
sheet.Cell(columnName).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
}
}
}
countIndex++;
}
counter++;
i++;
}
var rangeHeader = sheet.Range(startSheet + ":" + endSheet);
rangeHeader.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rangeHeader.Style.Font.Bold = true;
endSheet = endSheet.Remove(endSheet.Length - 1);
var rangeBorder = sheet.Range(startSheet + ":" + endSheet + (allData.Count + 1));
rangeBorder.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rangeBorder.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rangeBorder.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
sheet.Rows().AdjustToContents();
sheet.Columns().AdjustToContents();
fileExcel = fileName;
workBook.SaveAs(sourcePath);
dt.Add("fileExcel", fileExcel);
return(dt);
}
private IXLRow FindYellowRow()
{
return(sheet.Rows(1, 1000).FirstOrDefault(r => r.Cell(1).Style.Fill.BackgroundColor == XLColor.Yellow));
}
