public void Insert_RichText(ref ExcelWorksheet worksheet, int IndexRow, int IndexColumn, string Value, bool isBold, bool isRed = false)
{
worksheet.Cells[IndexRow, IndexColumn].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[IndexRow, IndexColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ExcelRichText RichText1 = worksheet.Cells[IndexRow, IndexColumn].RichText.Add(Value);
if (isRed)
{
RichText1.Bold = false;
RichText1.Italic = false;
RichText1.Color = Color.Red;
RichText1.FontName = "Arial Narrow";
}
else
{
RichText1.Color = Color.Black;
}
worksheet.Cells[IndexRow, IndexColumn].Style.WrapText = true;
worksheet.Cells[IndexRow, IndexColumn].IsRichText = true;
worksheet.Row(IndexRow).Height = 20;
//Auto height row
worksheet.Row(IndexRow).CustomHeight = false;
RichText1.Bold = isBold;
}
private void SetRichText(ExcelRange pregunta, string p)
{
pregunta.IsRichText = true;
ExcelRichText ert = pregunta.RichText.Add(p);
ert.Bold = true;
ert.Size = 11;
ert.Color = Color.White;
}
private void AddRunToCell(ExcelRange cell, string text, bool bolded, bool italicized, bool superscripted, bool underlined)
{
ExcelRichText rt = cell.RichText.Add(text);
rt.Bold = bolded;
rt.Italic = italicized;
rt.UnderLine = underlined;
if (superscripted)
{
rt.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
}
}
/// <summary>
/// Adds the scientific name of the taxon.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <param name="taxonItem">The taxon item.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="columnIndex">Index of the column.</param>
private void AddScientificName(
ExcelWorksheet worksheet,
ExportTaxonItem taxonItem,
Int32 rowIndex,
Int32 columnIndex)
{
if ((_options.OutputAuthorInAllNameCells || _options.OutputCommonNameInAllNameCells) &&
(taxonItem.Taxon.Category.SortOrder >= _genusTaxonCategory.SortOrder))
{
using (ExcelRange range = worksheet.Cells[rowIndex, columnIndex])
{
if (!_options.OutputAuthorInAllNameCells && !_options.OutputCommonNameInAllNameCells)
{
//Måste ändå kolla på synonymer _options.OutputExcludeAuthorForSynonyms
range.Value = taxonItem.Taxon.ScientificName;
}
else
{
range.IsRichText = true;
ExcelRichText ert = range.RichText.Add(taxonItem.Taxon.ScientificName);
ert.Italic = true;
//Kolla också på synonymer _options.OutputExcludeAuthorForSynonyms
if (_options.OutputAuthorInAllNameCells && !string.IsNullOrEmpty(taxonItem.Taxon.Author))
{
ert = range.RichText.Add(string.Format(" {0}", taxonItem.Taxon.Author));
ert.Italic = false;
}
if (_options.OutputCommonNameInAllNameCells && !string.IsNullOrEmpty(taxonItem.Taxon.CommonName))
{
ert = range.RichText.Add(string.Format(" {0}", taxonItem.Taxon.CommonName));
ert.Italic = false;
}
}
}
}
else
{
worksheet.Cells[rowIndex, columnIndex].Value = taxonItem.GetScientificName(
_options.OutputAuthorInAllNameCells,
_options.OutputCommonNameInAllNameCells);
}
}
public void AddingCommentsWithRichText()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Rich Comments");
ExcelComment comment = sheet.Cells["A1"].AddComment("Bold title:\r\n", "evil corp");
comment.Font.Bold = true;
comment.AutoFit = true;
ExcelRichText rt = comment.RichText.Add("Unbolded subtext");
rt.Bold = false;
// A more extensive example can be found in Sample6.cs::AddComments of the official examples project
// https://github.com/JanKallman/EPPlus/blob/master/SampleApp/Sample6.cs
package.SaveAs(new FileInfo(BinDir.GetPath()));
}
}
// Excel formatting can be at the entire cell level (e.g. the entire cell is marked italic)
// or at the text level (e.g. some words in the cell are marked italic).
// We detect and import both types, but if the user mixes levels for the same formatting type
// e.g.selects the entire cell, bolds it, then selected some text within the cell and unbolds it,
// we may get weird results, so we should tell users to use text-level formatting only
/// <param name="formattingText">Has any text-level formatting we want this run to have. Text content does not matter.</param>
/// <param name="cellFormatting">Has any cell-level formatting we want this run to have.</param>
/// <param name="text">The text content of this run</param>
/// <param name="stringBuilder">The string builder to which we are adding the xmlstring of this run
private static void AddRunToXmlString(ExcelRichText formattingText, ExcelFont cellFormatting, string text, StringBuilder stringBuilder)
{
if (text.Length == 0)
{
return;
}
List <string> endTags = new List <string>();
if (formattingText.Bold || cellFormatting.Bold)
{
addTags("strong", endTags);
}
if (formattingText.Italic || cellFormatting.Italic)
{
addTags("em", endTags);
}
if (formattingText.UnderLine || cellFormatting.UnderLine)
{
addTags("u", endTags);
}
if (formattingText.VerticalAlign == ExcelVerticalAlignmentFont.Superscript ||
cellFormatting.VerticalAlign == ExcelVerticalAlignmentFont.Superscript)
{
addTags("sup", endTags);
}
stringBuilder.Append(text);
endTags.Reverse();
foreach (var endTag in endTags)
{
stringBuilder.Append(endTag);
}
void addTags(string tagName, List <string> endTag)
{
stringBuilder.Append("<" + tagName + ">");
endTag.Add("</" + tagName + ">");
}
}
public static void SQLtoExcel(FileInfo source, FileInfo dest, string sql)
{
var queryResult = QuerySheet(source, sql);
using (ExcelPackage pkg = new ExcelPackage(dest)) {
using (var planilha = pkg.Workbook.Worksheets.Add("Planilha1")) {
int l = 1;
var firstItem = queryResult.First();
for (int c = 0; c < firstItem.Keys.Count(); c++)
{
var key = firstItem.Keys.ToArray()[c];
planilha.Cells[l, c + 1].IsRichText = true;
ExcelRichText richtext = planilha.Cells[l, c + 1].RichText.Add(key);
richtext.Bold = true;
}
l++;
foreach (var item in queryResult)
{
for (int c = 0; c < item.Keys.Count(); c++)
{
var key = item.Keys.ToArray()[c];
var value = item[key];
var cell = planilha.Cells[l, c + 1];
cell.Value = value;
if (value is DateTime)
{
cell.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
l++;
}
pkg.Save();
}
}
}
public static void WriteSpreadsheet(InternalSpreadsheet spreadsheet, string outputPath, bool retainMarkup, IWebSocketProgress progress = null)
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("BloomBook");
worksheet.DefaultColWidth = languageColumnWidth;
for (int i = 1; i <= spreadsheet.StandardLeadingColumns.Length; i++)
{
worksheet.Column(i).Width = standardLeadingColumnWidth;
}
var imageSourceColumn = spreadsheet.GetColumnForTag(InternalSpreadsheet.ImageSourceColumnLabel);
var imageThumbnailColumn = spreadsheet.GetColumnForTag(InternalSpreadsheet.ImageThumbnailColumnLabel);
// Apparently the width is in some approximation of 'characters'. This empirically determined
// conversion factor seems to do a pretty good job.
worksheet.Column(imageThumbnailColumn + 1).Width = defaultImageWidth / 6.88;
int r = 0;
foreach (var row in spreadsheet.AllRows())
{
r++;
for (var c = 0; c < row.Count; c++)
{
// Enhance: Excel complains about cells that contain pure numbers
// but are created as strings. We could possibly tell it that cells
// that contain simple numbers can be treated accordingly.
// It might be helpful for some uses of the group-on-page-index
// if Excel knew to treat them as numbers.
var sourceCell = row.GetCell(c);
var content = sourceCell.Content;
// Parse xml for markdown formatting on language columns,
// Display formatting in excel spreadsheet
ExcelRange currentCell = worksheet.Cells[r, c + 1];
if (!string.IsNullOrEmpty(sourceCell.Comment))
{
// Second arg is supposed to be the author.
currentCell.AddComment(sourceCell.Comment, "Bloom");
}
if (!retainMarkup &&
IsWysiwygFormattedColumn(row, c) &&
IsWysiwygFormattedRow(row))
{
MarkedUpText markedUpText = MarkedUpText.ParseXml(content);
if (markedUpText.HasFormatting)
{
currentCell.IsRichText = true;
foreach (MarkedUpTextRun run in markedUpText.Runs)
{
if (!run.Text.Equals(""))
{
ExcelRichText text = currentCell.RichText.Add(run.Text);
text.Bold = run.Bold;
text.Italic = run.Italic;
text.UnderLine = run.Underlined;
if (run.Superscript)
{
text.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
}
}
}
}
else
{
currentCell.Value = markedUpText.PlainText();
}
}
else
{
// Either the retainMarkup flag is set, or this is not book text. It could be header or leading column.
// Generally, we just want to blast our cell content into the spreadsheet cell.
// However, there are cases where we put an error message in an image thumbnail cell when processing the image path.
// We don't want to overwrite these. An easy way to prevent it is to not overwrite any cell that already has content.
// Since export is creating a new spreadsheet, cells we want to write will always be empty initially.
if (currentCell.Value == null)
{
currentCell.Value = content;
}
}
//Embed any images in the excel file
if (c == imageSourceColumn)
{
var imageSrc = sourceCell.Content;
// if this row has an image source value that is not a header
if (imageSrc != "" && !row.IsHeader)
{
var sheetFolder = Path.GetDirectoryName(outputPath);
var imagePath = Path.Combine(sheetFolder, imageSrc);
//Images show up in the cell 1 row greater and 1 column greater than assigned
//So this will put them in row r, column imageThumbnailColumn+1 like we want
var rowHeight = embedImage(imagePath, r - 1, imageThumbnailColumn);
worksheet.Row(r).Height = rowHeight * 72 / 96 + 3; //so the image is visible; height seems to be points
}
}
}
if (row is HeaderRow)
{
using (ExcelRange rng = GetRangeForRow(worksheet, r))
rng.Style.Font.Bold = true;
}
if (row.Hidden)
{
worksheet.Row(r).Hidden = true;
SetBackgroundColorOfRow(worksheet, r, InternalSpreadsheet.HiddenColor);
}
else if (row.BackgroundColor != default(Color))
{
SetBackgroundColorOfRow(worksheet, r, row.BackgroundColor);
}
}
worksheet.Cells[1, 1, r, spreadsheet.ColumnCount].Style.WrapText = true;
int embedImage(string imageSrcPath, int rowNum, int colNum)
{
int finalHeight = 30; // a reasonable default if we don't manage to embed an image.
try
{
using (Image image = Image.FromFile(imageSrcPath))
{
string imageName = Path.GetFileNameWithoutExtension(imageSrcPath);
var origImageHeight = image.Size.Height;
var origImageWidth = image.Size.Width;
int finalWidth = defaultImageWidth;
finalHeight = (int)(finalWidth * origImageHeight / origImageWidth);
var size = new Size(finalWidth, finalHeight);
using (Image thumbnail = ImageUtils.ResizeImageIfNecessary(size, image, false))
{
var excelImage = worksheet.Drawings.AddPicture(imageName, thumbnail);
excelImage.SetPosition(rowNum, 2, colNum, 2);
}
}
}
catch (Exception)
{
string errorText;
if (!RobustFile.Exists(imageSrcPath))
{
errorText = "Missing";
}
else if (Path.GetExtension(imageSrcPath).ToLowerInvariant().Equals(".svg"))
{
errorText = "Can't display SVG";
}
else
{
errorText = "Bad image file";
}
progress?.MessageWithoutLocalizing(errorText + ": " + imageSrcPath);
worksheet.Cells[r, imageThumbnailColumn + 1].Value = errorText;
}
return(Math.Max(finalHeight, 30));
}
foreach (var iColumn in spreadsheet.HiddenColumns)
{
// This is pretty yucky... our internal spreadsheet is all 0-based, but the EPPlus library is all 1-based...
var iColumn1Based = iColumn + 1;
worksheet.Column(iColumn1Based).Hidden = true;
SetBackgroundColorOfColumn(worksheet, iColumn1Based, InternalSpreadsheet.HiddenColor);
}
try
{
RobustFile.Delete(outputPath);
var xlFile = new FileInfo(outputPath);
package.SaveAs(xlFile);
}
catch (IOException ex) when((ex.HResult & 0x0000FFFF) == 32) //ERROR_SHARING_VIOLATION
{
Console.WriteLine("Writing Spreadsheet failed. Do you have it open in another program?");
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
progress?.Message("Spreadsheet.SpreadsheetLocked", "",
"Bloom could not write to the spreadsheet because another program has it locked. Do you have it open in another program?",
ProgressKind.Error);
}
catch (Exception ex)
{
progress?.Message("Spreadsheet.ExportFailed", "",
"Export failed: " + ex.Message,
ProgressKind.Error);
}
}
}
public byte[] gate_pass(string session, int gate_pass_no, string image)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"SELECT
a.session,
gate_pass_no,
b.sr_number,
date_time,
std_relation,
escorter_name,
escorter_address,
reason,
CONCAT(IFNULL(std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name,
CONCAT(IFNULL(class_name, ''),
' ',
IFNULL(section_name, '')) std_class,
std_father_name,
COALESCE(std_contact, std_contact1, std_contact2) contact_no,
g.pickup_point
FROM
std_halfday_log a,
sr_register b,
mst_std_class c,
mst_std_section d,
mst_class e,
mst_section f,
mst_transport g
WHERE
a.session = @session
AND a.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = f.session
AND f.session = g.session
AND a.sr_number = b.sr_number
AND b.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND c.class_id = e.class_id
AND d.section_id = f.section_id
AND g.pickup_id = b.std_pickup_id
AND a.gate_pass_no = @gate_pass_no";
var result = con.Query <ExcelGatePass>(query, new { session = session, gate_pass_no = gate_pass_no }).SingleOrDefault();
string gatePassNo = gate_pass_no.ToString().PadLeft(4, '0');
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Gate Pass");
ws.PrinterSettings.TopMargin = 1m / 2.54m;
ws.PrinterSettings.BottomMargin = 1m / 2.54m;
ws.PrinterSettings.LeftMargin = 1.8m / 2.54m;
ws.PrinterSettings.RightMargin = 1.8m / 2.54m;
ws.PrinterSettings.HeaderMargin = 0.0m / 2.54m;
ws.PrinterSettings.FooterMargin = 0.0m / 2.54m;
ws.PrinterSettings.HorizontalCentered = false;
ws.Column(1).Width = ExcelTc_form.GetTrueColumnWidth(13.18);
ws.Column(2).Width = ExcelTc_form.GetTrueColumnWidth(11.09);
ws.Column(3).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(4).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(5).Width = ExcelTc_form.GetTrueColumnWidth(9.18);
ws.Column(6).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(7).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(8).Width = ExcelTc_form.GetTrueColumnWidth(14.55);
for (int i = 2; i <= 15; i++)
{
ws.Row(i).Height = 17;
}
ws.Row(1).Height = 108.8;
using (ExcelRange Rng = ws.Cells["A1:H1"])
{
Rng.Merge = true;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
Rng.Style.WrapText = true;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Gate Pass\n");
RichText.Size = 14;
RichText = RichTxtCollection.Add(SchoolName + "\n");
RichText.Size = 28;
RichText.Bold = true;
RichText = RichTxtCollection.Add(Address + "\n");
RichText.Size = 11;
RichText = RichTxtCollection.Add(Affiliation);
RichText.Size = 11;
RichText.Bold = false;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
if (image == "../../images/person.png")
{
using (System.Drawing.Image img = System.Drawing.Image.FromFile(HttpContext.Current.Server.MapPath("/images/person.png")))
{
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
}
else
{
var img = Base64StringToBitmap(image.Remove(0, 22));
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
ws.Cells["A2"].Value = "Gate Pass No: ";
ws.Cells["A2"].Style.Font.Name = "Calibri";
ws.Cells["A2"].Style.Font.Size = 11;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B2"].Value = session + "/" + gatePassNo.ToString();;
ws.Cells["B2"].Style.Font.Name = "Calibri";
ws.Cells["B2"].Style.Font.Size = 11;
ws.Cells["B2"].Style.Font.UnderLine = true;
ws.Cells["B2"].Style.Font.Italic = true;
ws.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D2"].Value = "Adm No: ";
ws.Cells["D2"].Style.Font.Name = "Calibri";
ws.Cells["D2"].Style.Font.Size = 11;
ws.Cells["D2"].Style.Font.Bold = true;
ws.Cells["D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["E2"].Value = result.sr_number.ToString();
ws.Cells["E2"].Style.Font.Name = "Calibri";
ws.Cells["E2"].Style.Font.Size = 11;
ws.Cells["E2"].Style.Font.UnderLine = true;
ws.Cells["E2"].Style.Font.Italic = true;
ws.Cells["E2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["E2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["G2"].Value = "Date: ";
ws.Cells["G2"].Style.Font.Name = "Calibri";
ws.Cells["G2"].Style.Font.Size = 11;
ws.Cells["G2"].Style.Font.Bold = true;
ws.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["G2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H2"].Value = result.date_time.ToString("dd/MM/yyyy hh:mm");
ws.Cells["H2"].Style.Font.Name = "Calibri";
ws.Cells["H2"].Style.Font.Size = 11;
ws.Cells["H2"].Style.Font.UnderLine = true;
ws.Cells["H2"].Style.Font.Italic = true;
ws.Cells["H2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A4"].Value = "Name ";
ws.Cells["A4"].Style.Font.Name = "Calibri";
ws.Cells["A4"].Style.Font.Size = 11;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B4"].Value = result.std_name;
ws.Cells["B4"].Style.Font.Name = "Calibri";
ws.Cells["B4"].Style.Font.Size = 11;
ws.Cells["B4"].Style.Font.UnderLine = true;
ws.Cells["B4"].Style.Font.Italic = true;
ws.Cells["B4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A5"].Value = "Class ";
ws.Cells["A5"].Style.Font.Name = "Calibri";
ws.Cells["A5"].Style.Font.Size = 11;
ws.Cells["A5"].Style.Font.Bold = true;
ws.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B5"].Value = result.std_class;
ws.Cells["B5"].Style.Font.Name = "Calibri";
ws.Cells["B5"].Style.Font.Size = 11;
ws.Cells["B5"].Style.Font.UnderLine = true;
ws.Cells["B5"].Style.Font.Italic = true;
ws.Cells["B5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A6"].Value = "Father Name ";
ws.Cells["A6"].Style.Font.Name = "Calibri";
ws.Cells["A6"].Style.Font.Size = 11;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B6"].Value = result.std_father_name;
ws.Cells["B6"].Style.Font.Name = "Calibri";
ws.Cells["B6"].Style.Font.Size = 11;
ws.Cells["B6"].Style.Font.UnderLine = true;
ws.Cells["B6"].Style.Font.Italic = true;
ws.Cells["B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A7"].Value = "Contact No ";
ws.Cells["A7"].Style.Font.Name = "Calibri";
ws.Cells["A7"].Style.Font.Size = 11;
ws.Cells["A7"].Style.Font.Bold = true;
ws.Cells["A7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B7"].Value = result.contact_no;
ws.Cells["B7"].Style.Font.Name = "Calibri";
ws.Cells["B7"].Style.Font.Size = 11;
ws.Cells["B7"].Style.Font.UnderLine = true;
ws.Cells["B7"].Style.Font.Italic = true;
ws.Cells["B7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A8"].Value = "Person Name ";
ws.Cells["A8"].Style.Font.Name = "Calibri";
ws.Cells["A8"].Style.Font.Size = 11;
ws.Cells["A8"].Style.Font.Bold = true;
ws.Cells["A8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B8"].Value = result.escorter_name;
ws.Cells["B8"].Style.Font.Name = "Calibri";
ws.Cells["B8"].Style.Font.Size = 11;
ws.Cells["B8"].Style.Font.UnderLine = true;
ws.Cells["B8"].Style.Font.Italic = true;
ws.Cells["B8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A9"].Value = "Relationship ";
ws.Cells["A9"].Style.Font.Name = "Calibri";
ws.Cells["A9"].Style.Font.Size = 11;
ws.Cells["A9"].Style.Font.Bold = true;
ws.Cells["A9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B9"].Value = result.std_relation;
ws.Cells["B9"].Style.Font.Name = "Calibri";
ws.Cells["B9"].Style.Font.Size = 11;
ws.Cells["B9"].Style.Font.UnderLine = true;
ws.Cells["B9"].Style.Font.Italic = true;
ws.Cells["B9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A10"].Value = "Person Address ";
ws.Cells["A10"].Style.Font.Name = "Calibri";
ws.Cells["A10"].Style.Font.Size = 11;
ws.Cells["A10"].Style.Font.Bold = true;
ws.Cells["A10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B10"].Value = result.escorter_address;
ws.Cells["B10"].Style.Font.Name = "Calibri";
ws.Cells["B10"].Style.Font.Size = 11;
ws.Cells["B10"].Style.Font.UnderLine = true;
ws.Cells["B10"].Style.Font.Italic = true;
ws.Cells["B10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A11"].Value = "Reason ";
ws.Cells["A11"].Style.Font.Name = "Calibri";
ws.Cells["A11"].Style.Font.Size = 11;
ws.Cells["A11"].Style.Font.Bold = true;
ws.Cells["A11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B11"].Value = result.reason;
ws.Cells["B11"].Style.Font.Name = "Calibri";
ws.Cells["B11"].Style.Font.Size = 11;
ws.Cells["B11"].Style.Font.UnderLine = true;
ws.Cells["B11"].Style.Font.Italic = true;
ws.Cells["B11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A12"].Value = "Transport ";
ws.Cells["A12"].Style.Font.Name = "Calibri";
ws.Cells["A12"].Style.Font.Size = 11;
ws.Cells["A12"].Style.Font.Bold = true;
ws.Cells["A12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B12"].Value = result.pickup_point;
ws.Cells["B12"].Style.Font.Name = "Calibri";
ws.Cells["B12"].Style.Font.Size = 11;
ws.Cells["B12"].Style.Font.UnderLine = true;
ws.Cells["B12"].Style.Font.Italic = true;
ws.Cells["B12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A16"].Value = "Parents Sign ";
ws.Cells["A16"].Style.Font.Name = "Calibri";
ws.Cells["A16"].Style.Font.Size = 11;
ws.Cells["A16"].Style.Font.Bold = true;
ws.Cells["A16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D16"].Value = "Auth Sign. ";
ws.Cells["D16"].Style.Font.Name = "Calibri";
ws.Cells["D16"].Style.Font.Size = 11;
ws.Cells["D16"].Style.Font.Bold = true;
ws.Cells["D16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H16"].Value = "Incharge Sign. ";
ws.Cells["H16"].Style.Font.Name = "Calibri";
ws.Cells["H16"].Style.Font.Size = 11;
ws.Cells["H16"].Style.Font.Bold = true;
ws.Cells["H16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
return(pck.GetAsByteArray());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void SetValueInCellExport(ExcelWorksheet ew1, object value, UI_SELECT_GRID_SETTINGSResult setting, bool?is_date_time)
{
bool is_write = false;
if (setting == null)
{
is_write = true;
}
else if ((bool)setting.global_visible && (bool)setting.is_visible)
{
is_write = true;
}
if (is_write)
{
if (value != null && value is string | value is DateTime)
{
if (value is DateTime)
{
if (is_date_time == true)
{
value = DateTime.Parse(Convert.ToString(value));
}
else
{
value = DateTime.Parse(Convert.ToString(value)).ToShortDateString();
}
}
string[] arrayHTMLValue = Convert.ToString(value).Split(new string[] { "<br>" }, StringSplitOptions.None);
if (arrayHTMLValue != null && arrayHTMLValue.Length != 0)
{
ExcelRichTextCollection rtfCollection = ew1.Cells[rowCount, columnCount].RichText;
ExcelRichText ert = rtfCollection.Add(CommonMethods.HtmlToText(arrayHTMLValue[0]));
if (arrayHTMLValue[0].IndexOf("orange") > 0)
{
ert.Color = System.Drawing.Color.Orange;
}
if (arrayHTMLValue.Length > 1)
{
for (int f = 1; f < arrayHTMLValue.Length; f++)
{
rtfCollection.Add(CommonMethods.HtmlToText("\n"));
ExcelRichText ert1 = rtfCollection.Add(CommonMethods.HtmlToText(arrayHTMLValue[f]));
if (arrayHTMLValue[f].IndexOf("orange") > 0)
{
ert1.Color = System.Drawing.Color.Orange;
}
}
}
}
else
{
ew1.Cells[rowCount, columnCount].Value = CommonMethods.HtmlToText(Convert.ToString(value));
}
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
if (value != null && value is double | value is int | value is float | value is decimal)
{
ew1.Cells[rowCount, columnCount].Value = double.Parse(CommonMethods.HtmlToText(CommonMethods.ObjectToString(value)).Replace(".", ","));
ew1.Cells[rowCount, columnCount].Style.Numberformat.Format = CommaFind(CommonMethods.HtmlToText(CommonMethods.ObjectToString(value)).Replace(".", ","));
}
//ew1.Cells[rowCount, columnCount].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Right.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.WrapText = true;
//ew1.Cells[rowCount, columnCount].Style.Font.Name = "Calibri";
//ew1.Cells[rowCount, columnCount].Style.Font.Size = 8;
if (value is string)
{
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
if (value is double | value is int | value is float)
{
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
ew1.Cells[rowCount, columnCount].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
columnCount++;
}
}
private void MachineMtnReportDatas_GetExcel_Daily(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable <MachineMtnReportData> data, ExcelPackage ep, List <BuildArgs> buildArgs)
{
//init value.
//fix cứng số cột: số thự tự tên nước, saleperson, Year và danh sách 12 tháng (orderMonthFromIndex --> orderMonthToIndex)
string prefixTotalColumn = "_*_";
int startRowIdx = 8; // title table index.
int machinePartName_ColIdx = 2;
int contentMtn_ColIdx = 3;
int toolMtn_ColIdx = 4;
int methodMtn_ColIdx = 5;
int standard_ColIdx = 6;
int runningFrom_ColIdx = 7;
int DayAndShiftOrder_FromColIdx = runningFrom_ColIdx;
int DayAndShiftOrder_ToColIdx = 0;
int totalColumns_ColIdx = 0; // tổng số cột.
int rowIndex = startRowIdx;
//total Result index.
int totalResult_RowIdx = 0;
int OperatorRowIdx;
int CheckerRowIdx;
// workbook
var wb = ep.Workbook;
// new worksheet
var ws = wb.Worksheets.Add("Sheet01");
#region Title.
ws.Cells[2, 7].Value = ("Bảng kiểm tra bảo trì máy/Machine maintenance checksheet").ToUpper();
ws.Cells[2, 7].Style.Font.Bold = true;
ws.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[2, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 2,
FromColumn = 7,
ToRow = 2,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[3, 7]) //machineid.
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("MachineID: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineID);
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Machine Name: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineName);
RichText.Bold = true;
}
ws.Cells[3, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[3, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 3,
FromColumn = 7,
ToRow = 3,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[4, 7]) //FromDate ToDate
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Từ ngày/FromDate: ");
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.FromDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Đến ngày/ToDate: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.ToDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
}
ws.Cells[4, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[4, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 4,
FromColumn = 7,
ToRow = 4,
ToColumn = 14
});
#endregion
#region Header.
// Header Value.
ws.Cells[rowIndex, machinePartName_ColIdx].Value = "Part Name";
ws.Cells[rowIndex, contentMtn_ColIdx].Value = "Content";
ws.Cells[rowIndex, toolMtn_ColIdx].Value = "Tool";
ws.Cells[rowIndex, methodMtn_ColIdx].Value = "Method";
ws.Cells[rowIndex, standard_ColIdx].Value = "Standard";
//-- MaintenanceDate headers
var dict_DayOrder = new Dictionary <string, int>();
int countDayAndShiftOrderColumn = 0;
var dict_DayAndShiftOrder = new Dictionary <string, int>();
for (var day = machineMtnReportDataSearch.FromDate; day <= machineMtnReportDataSearch.ToDate; day = day.AddDays(1))
{
int oneDay_FullShift_FromColIndex = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn;
for (int i = 0; i <= 2; i++) // i = order number column in excel, i++ = shift number.
{
//date header.
ws.Cells[rowIndex, (countDayAndShiftOrderColumn) + runningFrom_ColIdx].Value = day.ToString("MM/dd", enUS);
//shift header.
ws.Cells[rowIndex + 1, countDayAndShiftOrderColumn + runningFrom_ColIdx].Value = (i + 1).ToString();
dict_DayOrder.Add(day.ToString("MM/dd", enUS) + "_" + (i + 1).ToString(), countDayAndShiftOrderColumn); // sample "15/3_1" = date is 15/3 and shift = 1
dict_DayAndShiftOrder.Add(day.ToString("MM/dd", enUS) + (i + 1).ToString(), countDayAndShiftOrderColumn);
countDayAndShiftOrderColumn++;
}
int oneDay_FullShift_ToColIndex = oneDay_FullShift_FromColIndex + 2;
//merge cell MaintenanceDate.
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = rowIndex,
FromColumn = oneDay_FullShift_FromColIndex,
ToRow = rowIndex,
ToColumn = oneDay_FullShift_ToColIndex
});
//intDayOrder++;
}
DayAndShiftOrder_ToColIdx = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn - 1;
totalColumns_ColIdx = DayAndShiftOrder_ToColIdx;
// Headers style
using (var cell = ws.Cells[rowIndex, machinePartName_ColIdx, rowIndex + 1, totalColumns_ColIdx])
{
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 11;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
rowIndex++;
rowIndex++;
#endregion
//checking data.
if (data == null)
{
return;
}
#region Add TotalResult.
var maintenanceDateGroups = data.GroupBy(d => d.MaintenanceDate);
foreach (var maintenanceDateGroup in maintenanceDateGroups)
{
MachineMtnReportData tMachineMtn = maintenanceDateGroup.FirstOrDefault();
MachineMtnReportData TotalResult_Row = new MachineMtnReportData();
TotalResult_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
TotalResult_Row.Shift = tMachineMtn.Shift;
TotalResult_Row.FrequencyID = tMachineMtn.FrequencyID;
TotalResult_Row.MachinePartID = int.MaxValue;
TotalResult_Row.MachinePartName = "zzzzz01" + prefixTotalColumn + "Total Result";
TotalResult_Row.MtnDetailResult = tMachineMtn.TotalResult;
TotalResult_Row.MtnDetailResultContents = tMachineMtn.TotalResultContents;
MachineMtnReportData Operator_Row = new MachineMtnReportData();
Operator_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Operator_Row.Shift = tMachineMtn.Shift;
Operator_Row.MachinePartID = int.MaxValue;
Operator_Row.MachinePartName = "zzzzz02" + prefixTotalColumn + "Operator";
Operator_Row.MtnDetailResult = tMachineMtn.OperatorName;
MachineMtnReportData Checker_Row = new MachineMtnReportData();
Checker_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Checker_Row.Shift = tMachineMtn.Shift;
Checker_Row.MachinePartID = int.MaxValue;
Checker_Row.MachinePartName = "zzzzz03" + prefixTotalColumn + "Checker";
Checker_Row.MtnDetailResult = tMachineMtn.CheckerName;
IEnumerable <MachineMtnReportData> x = new MachineMtnReportData[] { TotalResult_Row, Operator_Row, Checker_Row };
data = data.Concat(x);
}
#endregion
//***************** Data *************
#region MachinePartName.
var machinePartNameGroups = data.GroupBy(d => new { d.MachinePartID, d.MachinePartName }).OrderBy(g => g.Key.MachinePartName);
#endregion
//var Start_RunningFromRowIndex = rowIndex;
foreach (var machinePartNameGroup in machinePartNameGroups)
{
int machinePartNameGroup_FromRowIndex = rowIndex;
#region ContentMtn.
int contentMtnFromRowIndex = rowIndex;
var contentMtnGroups = machinePartNameGroup.GroupBy(d => d.ContentMtn).OrderBy(g => g.Key);
#endregion
foreach (var contentMtnGroup in contentMtnGroups)
{
int contentMtnGroup_FromRowIndex = rowIndex;
#region ToolMtn.
int toolMtnFromRowIndex = rowIndex;
var toolMtnGroups = contentMtnGroup.GroupBy(d => d.ToolMtn).OrderBy(g => g.Key);
#endregion
foreach (var toolMtnGroup in toolMtnGroups)
{
int toolMtnGroup_FromRowIndex = rowIndex;
#region MethodMtn.
int methodMtnFromRowIndex = rowIndex;
var methodMtnGroups = toolMtnGroup.GroupBy(d => d.MethodMtn).OrderBy(g => g.Key);
#endregion
foreach (var methodMtnGroup in methodMtnGroups)
{
#region Standard.
int standard_FromRowIndex = rowIndex;
var standardGroups = methodMtnGroup.GroupBy(d => d.Standard).OrderBy(g => g.Key);
foreach (var standardGroup in standardGroups)
{
//insert MachinePartName, Tool, Content, ......
string _machinePartName = machinePartNameGroup.Key.MachinePartName;
if (_machinePartName.Contains(prefixTotalColumn)) //totalResult.
{
if (_machinePartName.Contains("zzzzz01")) //get totalResult Row index.
{
totalResult_RowIdx = rowIndex;
}
_machinePartName = _machinePartName.Remove(0, _machinePartName.IndexOf(prefixTotalColumn) + prefixTotalColumn.Length);
}
ws.Cells[rowIndex, machinePartName_ColIdx].Value = _machinePartName;
ws.Cells[rowIndex, contentMtn_ColIdx].Value = contentMtnGroup.Key;
ws.Cells[rowIndex, toolMtn_ColIdx].Value = toolMtnGroup.Key;
ws.Cells[rowIndex, methodMtn_ColIdx].Value = methodMtnGroup.Key;
ws.Cells[rowIndex, standard_ColIdx].Value = standardGroup.Key;
#region Maintenance Days.
int maintenanceDayFromRowIndex = rowIndex;
var maintenanceDayGroups = standardGroup.GroupBy(d => d.MaintenanceDate.ToString("MM/dd")).OrderBy(g => g.Key);
#endregion
int orderDay = 1; // *************** checking aaaa : orderday: phai lay theo thu tu cua methodMtnGroup
ws.Cells[rowIndex, DayAndShiftOrder_FromColIdx, rowIndex, DayAndShiftOrder_ToColIdx].Value = "";
foreach (var maintenanceDayGroup in maintenanceDayGroups)
{
var mtnDayShiftGroups = maintenanceDayGroup.GroupBy(d => d.Shift);
#region one day.
foreach (var mtnDayShiftGroup in mtnDayShiftGroups)
{
#region one day and one shift.
// ******************* Gán cụ thể vào Excel.
DateTime currentMtnDate = mtnDayShiftGroup.FirstOrDefault().MaintenanceDate; //.ToString("MM/dd", enUS);
MachineMtnReportData currentMtnReportData = mtnDayShiftGroup.FirstOrDefault();
int currentOrderDayShift = dict_DayOrder[currentMtnDate.ToString("MM/dd", enUS) + "_" + currentMtnReportData.Shift];
var currentCells = ws.Cells[rowIndex, currentOrderDayShift - 1 + runningFrom_ColIdx];
//set value for cell.
currentCells.Value = currentMtnReportData.MtnDetailResult;
//set color for OK, NG.
if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_OK)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_OK_Color);
}
else if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_NG)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_NG_Color);
}
orderDay++;
#endregion
}
#endregion
}
rowIndex++;
//Borders.
}
int standard_ToRowIndex = rowIndex - 1;
#endregion
}
int toolMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = toolMtnGroup_FromRowIndex,
FromColumn = toolMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = toolMtn_ColIdx
});
}
int contentMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = contentMtnGroup_FromRowIndex,
FromColumn = contentMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = contentMtn_ColIdx
});
}
int machinePartNameGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = machinePartNameGroup_FromRowIndex,
FromColumn = machinePartName_ColIdx,
ToRow = rowIndex - 1,
ToColumn = machinePartName_ColIdx
});
}
#region Footer
#endregion
#region Border Style.
int toRowIndex = rowIndex - 1;
// cells borders
ws.View.ShowGridLines = false;
foreach (var cell in ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Top.Color.SetColor(Color.Black);
cell.Style.Border.Bottom.Color.SetColor(Color.Black);
cell.Style.Border.Left.Color.SetColor(Color.Black);
cell.Style.Border.Right.Color.SetColor(Color.Black);
}
#endregion
#region Manipulate Cells (merge).
// merge cells
foreach (var args in buildArgs.Where(ba => ba.Action == BuildAction.Merge))
{
using (var cells = args.Worksheet.Cells[args.FromRow, args.FromColumn, args.ToRow, args.ToColumn])
{
cells.Merge = true;
}
}
// Vertical Alignment body table.
ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// new line Cell.
foreach (var cell in ws.Cells[startRowIdx, machinePartName_ColIdx, toRowIndex, standard_ColIdx])
{
if (cell.Value != null)
{
cell.Value = cell.Value.ToString().Replace("/", Environment.NewLine);
}
}
// wordwrap entire table.
using (var AllCellsTables = ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
AllCellsTables.Style.WrapText = true;
//AllCellsTables.AutoFitColumns();
}
//set height Row.
for (int intRow = startRowIdx; intRow < toRowIndex; intRow++)
{
if (ws.Cells[intRow, machinePartName_ColIdx] != null && ws.Cells[intRow, machinePartName_ColIdx].Value != null)
{
ws.Row(intRow).Height = SMCommon.MeasureTextHeight(ws.Cells[intRow, machinePartName_ColIdx].Value.ToString(), ws.Cells[intRow, machinePartName_ColIdx].Style.Font, 1);
}
}
//set width column.
ws.Column(machinePartName_ColIdx).Width = 25;
ws.Column(contentMtn_ColIdx).Width = 25;
ws.Column(toolMtn_ColIdx).Width = 25;
ws.Column(methodMtn_ColIdx).Width = 20;
ws.Column(standard_ColIdx).Width = 20;
#endregion
}
public void Undo(ExcelRichText rt)
{
_setter(rt, _oldValue);
}
public void Apply(ExcelRichText rt)
{
_oldValue = _getter(rt);
_setter(rt, _newValue);
}
public async Task <ResponseResult <Response> > Handle(
Command request,
CancellationToken cancellationToken)
{
var queryResult = new MaterialTypeExport();
IEnumerable <MaterialPropertyExport> basicProperties;
IEnumerable <Lunz.ProductCenter.MService.QueryStack.Models.Trade> trades;
using (var scope = _databaseScopeFactory.CreateReadOnly())
{
queryResult = await _repository.FindCustomPropertiesByTypeAsync <MaterialTypeExport>(request.TypeId);
basicProperties = await _repository.FindBasicPropertiesAsync <MaterialPropertyExport>();
trades = await _repository.FindAllTradePropertiesAsync <QueryStack.Models.Trade>();
}
string url = string.Empty;
if (queryResult != null)
{
var createResult = await CreateFolder();
var path = createResult.Item1;
var fileName = $"物料模板-{request.TypeName}{request.UserName}-{createResult.Item2}.xlsx";
FileInfo file = new FileInfo($@"{path}\{fileName}");
if (file?.Exists ?? false)
{
file.Delete();
}
using (ExcelPackage package = new ExcelPackage(file))
{
// 物料模板sheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("物料");
// 物料属性sheet
ExcelWorksheet propertysheet = package.Workbook.Worksheets.Add("属性");
// 第一行
worksheet.Cells[1, 1].Value = $"物料类型Id:{queryResult.Id}";
worksheet.Cells[1, 2].Value = $"物料类型:{queryResult.TypeName}";
worksheet.Cells[1, 3].Value = $"物料编码:{request.TypeCode}";
worksheet.Cells[1, 4].Value = $"模板导出时间:{string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)}";
// row 2, col 1, 物料名称列
worksheet.Cells[2, 1].IsRichText = true;
ExcelRichText ert = worksheet.Cells[2, 1].RichText.Add("物料名称");
ert = worksheet.Cells[2, 1].RichText.Add("*");
ert.Color = Color.Red;
// 从row 3到maxRows, col 1, 物料名称列验证
var minLength = 1;
var maxLength = 40;
int maxRows = ExcelPackage.MaxRows;
var textValidation = worksheet.DataValidations.AddTextLengthValidation($"A3:A{maxRows}");
textValidation.ShowErrorMessage = true;
textValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
textValidation.ErrorTitle = "物料名称";
textValidation.Error = string.Format("物料名称不能为空，且最大长度不能超过{0}个字符以内的汉字、字母、数字、符号的组合", maxLength);
textValidation.Formula.Value = minLength;
textValidation.Formula2.Value = maxLength;
// 属性sheet
var optionRow = 2;
var optionCol = 1;
// 物料sheet, row 2, col 2, col 3, DT0000000015: 物料单位, DT0000000031: 物料规格
if (basicProperties?.Count() == 2)
{
optionCol = 1;
// 物料sheet, 从row 3到maxRows, col 2, col 3, 物料规格列下拉列表, 物料单位列下拉列表
var col = 2;
foreach (var property in basicProperties)
{
worksheet.Cells[2, col].IsRichText = true;
ert = worksheet.Cells[2, col].RichText.Add(property.DisplayName);
ert = worksheet.Cells[2, col].RichText.Add("*");
ert.Color = Color.Red;
var basicPropertiesRange = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col);
// 属性值
var options = property.Options;
if (options?.Any() ?? false)
{
var basicPropertiesValidation = worksheet.DataValidations.AddListValidation(basicPropertiesRange);
basicPropertiesValidation.ShowErrorMessage = true;
basicPropertiesValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
basicPropertiesValidation.ErrorTitle = "选择基本属性";
basicPropertiesValidation.Error = "请从属性列表中选择一项";
var valueRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true);
basicPropertiesValidation.Formula.ExcelFormula = $@"属性!{valueRange}";
// 属性sheet, 属性名
propertysheet.Cells[1, optionCol].Value = property.DisplayName;
optionRow = 2;
foreach (var option in options)
{
// 属性sheet, 所有属性选项值
propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}";
optionRow++;
}
}
optionCol++;
col++;
}
}
else
{
worksheet.Cells[2, 2].IsRichText = true;
ert = worksheet.Cells[2, 2].RichText.Add("物料规格");
ert = worksheet.Cells[2, 2].RichText.Add("*");
ert.Color = Color.Red;
worksheet.Cells[2, 3].IsRichText = true;
ert = worksheet.Cells[2, 3].RichText.Add("物料单位");
ert = worksheet.Cells[2, 3].RichText.Add("*");
ert.Color = Color.Red;
}
// row 2, col 4, 是否为生产物料列, 【是否为生产物料】选择为“是”时，【是否有独立编码】默认为“否”且不可修改。
worksheet.Cells[2, 4].IsRichText = true;
ert = worksheet.Cells[2, 4].RichText.Add("是否为生产物料");
ert = worksheet.Cells[2, 4].RichText.Add("*");
ert.Color = Color.Red;
// 是否为生产物料下拉列表
var range = ExcelRange.GetAddress(3, 4, ExcelPackage.MaxRows, 4);
var listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
listValidation.ErrorTitle = "选择基本属性";
listValidation.Error = "请从属性列表中选择一项";
listValidation.Formula.Values.Add("0:否");
listValidation.Formula.Values.Add("1:是");
// row 2, col 5, 是否有独立编号列
worksheet.Cells[2, 5].IsRichText = true;
ert = worksheet.Cells[2, 5].RichText.Add("是否有独立编号");
ert = worksheet.Cells[2, 5].RichText.Add("*");
ert.Color = Color.Red;
// 是否有独立编号下拉列表
range = ExcelRange.GetAddress(3, 5, ExcelPackage.MaxRows, 5);
listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
listValidation.ErrorTitle = "选择基本属性";
listValidation.Error = "请从属性列表中选择一项";
listValidation.Formula.Values.Add("0:否");
listValidation.Formula.Values.Add("1:是");
// row 2, col 6, 采购主体列
StringBuilder tradeValue = new StringBuilder();
tradeValue.Append(Environment.NewLine);
tradeValue.Append("(多个采购主体用英文逗号隔开)");
if (trades?.Any() ?? false)
{
tradeValue.Append(Environment.NewLine);
foreach (var trade in trades)
{
tradeValue.Append($"{trade.Id}:{trade.TradeName},");
}
}
var value = tradeValue.ToString();
worksheet.Cells[2, 6].IsRichText = true;
ert = worksheet.Cells[2, 6].RichText.Add("采购主体");
if (!string.IsNullOrEmpty(request.TypeCode) && request.TypeCode.StartsWith(_tradCode))
{
ert = worksheet.Cells[2, 6].RichText.Add("*");
ert.Color = Color.Red;
}
ert = worksheet.Cells[2, 6].RichText.Add(value.Remove(value.LastIndexOf(',')));
ert.Color = Color.Black;
worksheet.Cells[2, 6].Style.WrapText = true;
// row 2, col 7, 建议采购价列
worksheet.Cells[2, 7].Value = "建议采购价(5位以内的正数,两位小数)";
worksheet.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
// 从row 3到maxRows, col 7, 建议采购价列验证
range = ExcelRange.GetAddress(3, 7, ExcelPackage.MaxRows, 7);
var decimalValidation = worksheet.DataValidations.AddDecimalValidation(range);
decimalValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
decimalValidation.ErrorTitle = "建议采购价";
decimalValidation.Error = "建议采购价是5位以内的正数";
decimalValidation.ShowErrorMessage = true;
decimalValidation.Operator = ExcelDataValidationOperator.between;
decimalValidation.Formula.Value = 0.00;
decimalValidation.Formula2.Value = 99999.99;
// 属性sheet
optionRow = 2;
// row 2, 从col 8开始, 自定义属性列
var properties = queryResult.Properties;
if (properties?.Any() ?? false)
{
optionCol = 3;
var col = 8;
foreach (var property in properties)
{
// 从row 3开始，添加自定义属性选项值下拉列表
range = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col);
// 属性选项值
var options = property.Options;
if (options?.Any() ?? false)
{
listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorTitle = "选择自定义属性";
listValidation.Error = "请从属性列表中选择一项";
var customerOptionRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true);
listValidation.Formula.ExcelFormula = $@"属性!{customerOptionRange}";
// 属性sheet, 属性名
propertysheet.Cells[1, optionCol].Value = $"{property.PropId}:{property.DisplayName}";
optionRow = 2;
foreach (var option in options)
{
// 属性sheet, 所有属性选项值
propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}";
optionRow++;
}
}
// 自定义属性是否必填
if (property.IsNecessary)
{
worksheet.Cells[2, col].IsRichText = true;
ert = worksheet.Cells[2, col].RichText.Add($"{property.PropId}:{property.DisplayName}");
ert = worksheet.Cells[2, col].RichText.Add("*");
ert.Color = Color.Red;
if (options?.Any() ?? false)
{
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
}
}
else
{
worksheet.Cells[2, col].Value = $"{property.PropId}:{property.DisplayName}";
if (options?.Any() ?? false)
{
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
}
}
optionCol++;
col++;
}
}
worksheet.View.FreezePanes(3, 1);
worksheet.Cells.AutoFitColumns();
propertysheet.Cells.AutoFitColumns();
worksheet.Column(6).Width = 32;
package.Save();
}
string ossEndpoint = "http://oss-cn-hangzhou.aliyuncs.com";
string ossAccessKeyId = "LTAIFAiipRXidbYT";
string ossAccessKeySecret = "62zh9kaAd60NAqMxNPCqVcvxhNxH0H";
string ossBucketName = "basichz";
string ossRootFolder = "productcenter";
string ossAccessUrl = "//oss.lunz.cn/";
var fileManager = new OssFileManager(ossEndpoint, ossAccessKeyId, ossAccessKeySecret, ossBucketName, ossRootFolder, ossAccessUrl);
// 上传至阿里云
var uploadResult = fileManager.Upload(file.Name.Trim(), $@"{path}\{file.Name.Trim()}");
// 返回阿里云Url地址
url = $@"http://basichz.lunz.cn/{uploadResult.Key}";
// 删除文件夹
DirectoryInfo directory = new DirectoryInfo(path);
if (directory.Exists)
{
directory.Delete(true);
}
}
return(ResponseResult <Response> .Ok(new Response(url)));
}
private void writeToExcelDM(FileInfo newFile, string type)
{
// EPPlus library is required
ExcelPackage package = new ExcelPackage(newFile);
MaintainStaffControl staffControl = new MaintainStaffControl();
MaintainExaminationControl examControl = new MaintainExaminationControl();
MaintainInvigilationDutyControl dutyControl = new MaintainInvigilationDutyControl();
MaintainExemptionControl exemptionControl = new MaintainExemptionControl();
MaintainPaperExaminedControl paperExaminedControl = new MaintainPaperExaminedControl();
//Check for CHIEF, or others
if (type.Equals("CHIEF"))
{
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);
// Set column width
worksheet.Column(1).Width = 4.5;
worksheet.Column(2).Width = 45;
//print header
worksheet.Cells[1, 1].Value = "No.";
worksheet.Cells[1, 1, 3, 1].Merge = true;
worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 2, 3, 2].Merge = true;
worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
List <Staff> staffList = staffControl.getStaffList();
staffControl.shutDown();
List <string> timeslotList = examControl.getTimeslot();
examControl.shutDown();
List <Exemption> exemptionList = null;
List <InvigilationDuty> dutyList = null;
List <PaperExamined> paperExaminedList = null;
//row and column as counter
int rowNoStaff = 4;
int rowNoDate = 1;
int colNoDate = 3;
int colNoTotal = 0;
string timeslotID = "EMPTY";
//array as counter for printing logo purpose
List <DateTime> dateList = new List <DateTime>();
//print timeslot
for (int i = 0; i < timeslotList.Count; i++)
{
//checking to avoid printing the same date twice
if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
{
timeslotID = timeslotList[i];
int year = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[i].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
dateList.Add(dt);
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rowNoDate = 1;
}
}
//print total header
worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//assign the col of "Total" for writing inv duties
colNoTotal = colNoDate;
//set column width
worksheet.Column(colNoDate).Width = 5.5;
while (colNoDate >= 4)
{
worksheet.Column((colNoDate--) - 1).Width = 6.5;
}
//count number of inv printed
int countInvi = 0;
//print staffs
for (int i = 0; i < staffList.Count; i++)
{
//check and print chief invigilator name
if (staffList[i].IsChief == 'Y')
{
//print chief invigilator
worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;
//check and print exemption
exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
if (exemptionList.Count > 0)
{
for (int a = 0; a < exemptionList.Count; a++)
{
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);
//get the column number if not AM
if (exemptionList[a].Session.Equals("PM"))
{
colToInsert += 1;
}
else if (exemptionList[a].Session.Equals("EV"))
{
colToInsert += 2;
}
//insert exemption
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
}
}
//count total number of duties for each inv
int totalDuty = 0;
//check and print invigilation duty
dutyList = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
if (dutyList.Count > 0)
{
totalDuty = 0;
for (int a = 0; a < dutyList.Count; a++)
{
//get timeslot Date to compare with dutyList Date
int year = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
int month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
int day = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);
//get the column number if not AM
if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
{
colToInsert += 1;
}
else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
{
colToInsert += 2;
}
//retrieve course list(exam) on given location and timeslotID
List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);
//check if the chief is also an examiner
var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);
//insert only if no exemption is inserted
if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
{
totalDuty++;
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
//compare duty type
if (dutyList[a].Location.Equals("Block V") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("V");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SE") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SE");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SD") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SD");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block SB") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("SB");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block R") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block Q") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("Q");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block PA, PA7-PA12") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA7");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block PA, PA1-PA6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("PA1");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block M") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("M");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block L") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("L");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block KS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("KS");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H, H7-H14") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H7");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H, H1-H6") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H1");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block H") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("H");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Dewan Utama") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DU");
ert.Size = 7;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Location.Equals("Block DS") && dutyList[a].CategoryOfInvigilator.Equals("Chief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
}
//if printing last duty in a duty list, print totalDuty
if (a + 1 == dutyList.Count)
{
worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
}
}
}
}
}
//shutdown connection
exemptionControl.shutDown();
dutyControl.shutDown();
paperExaminedControl.shutDown();
//center "No." and all the ticks
worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
else
{
char facultyCode = '\0';
//compare type to get facultyCode
if (type.Equals("CNBL"))
{
facultyCode = 'E';
}
else if (type.Equals("FEBE"))
{
facultyCode = 'T';
}
else if (type.Equals("FAFB"))
{
facultyCode = 'B';
}
else if (type.Equals("FASC"))
{
facultyCode = 'A';
}
// Add a worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(type);
// Set column width
worksheet.Column(1).Width = 4.5;
worksheet.Column(2).Width = 45;
//print header
worksheet.Cells[1, 1].Value = "No.";
worksheet.Cells[1, 1, 3, 1].Merge = true;
worksheet.Cells[1, 1, 3, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 1, 3, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 2, 3, 2].Merge = true;
worksheet.Cells[1, 2, 3, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[1, 2, 3, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
List <Staff> staffList = staffControl.getStaffList();
staffControl.shutDown();
List <string> timeslotList = examControl.getTimeslot();
examControl.shutDown();
List <Exemption> exemptionList = null;
List <InvigilationDuty> dutyList = null;
List <PaperExamined> paperExaminedList = null;
//row and column as counter
int rowNoStaff = 4;
int rowNoDate = 1;
int colNoDate = 3;
int colNoTotal = 0;
string timeslotID = "EMPTY";
//array as counter for printing logo purpose
List <DateTime> dateList = new List <DateTime>();
//print timeslot
for (int i = 0; i < timeslotList.Count; i++)
{
//checking to avoid printing the same date twice
if (!timeslotID.Substring(2).Equals(timeslotList[i].Substring(2)))
{
timeslotID = timeslotList[i];
int year = Convert.ToInt32("20" + timeslotList[i].Substring(6, 2));
int month = Convert.ToInt32(timeslotList[i].Substring(4, 2));
int day = Convert.ToInt32(timeslotList[i].Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
dateList.Add(dt);
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("ddd").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate].Value = dt.ToString("dd/MM/yy").ToUpper();
worksheet.Cells[rowNoDate, colNoDate, rowNoDate, colNoDate + 2].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, rowNoDate++, colNoDate + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate++].Value = "AM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "PM";
worksheet.Cells[rowNoDate, colNoDate++].Value = "EV";
worksheet.Cells[rowNoDate, colNoDate - 3, rowNoDate, colNoDate - 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rowNoDate = 1;
}
}
//print total header
worksheet.Cells[rowNoDate, colNoDate].Value = "Total";
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Merge = true;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[rowNoDate, colNoDate, 3, colNoDate].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//assign the col of "Total" for writing inv duties
colNoTotal = colNoDate;
//set column width
worksheet.Column(colNoDate).Width = 5.5;
while (colNoDate >= 4)
{
worksheet.Column((colNoDate--) - 1).Width = 6.5;
}
//count inv number of inv printed
int countInvi = 0;
//print staffs
for (int i = 0; i < staffList.Count; i++)
{
//check and print invigilator based on faculty
if (staffList[i].IsInvi == 'Y' && staffList[i].FacultyCode == facultyCode)
{
//print invigilator
worksheet.Cells[countInvi + 4, 1].Value = ++countInvi;
worksheet.Cells["B" + rowNoStaff++.ToString()].Value = staffList[i].Name + " " + staffList[i].Title;
//check and print exemption
exemptionList = exemptionControl.searchExemption(staffList[i].StaffID);
if (exemptionList.Count > 0)
{
for (int a = 0; a < exemptionList.Count; a++)
{
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == exemptionList[a].Date) * 3);
//get the column number if not AM
if (exemptionList[a].Session.Equals("PM"))
{
colToInsert += 1;
}
else if (exemptionList[a].Session.Equals("EV"))
{
colToInsert += 2;
}
//insert exemption
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("X");
}
}
//count total number of duties for each inv
int totalDuty = 0;
//check and print invigilation duty
dutyList = dutyControl.searchInvigilationDuty2(Convert.ToInt32(staffList[i].StaffID));
paperExaminedList = paperExaminedControl.getPaperExaminedList(staffList[i].StaffID);
if (dutyList.Count > 0)
{
totalDuty = 0;
for (int a = 0; a < dutyList.Count; a++)
{
//get timeslot Date to compare with dutyList Date
int year = Convert.ToInt32("20" + dutyList[a].TimeslotID.Substring(6, 2));
int month = Convert.ToInt32(dutyList[a].TimeslotID.Substring(4, 2));
int day = Convert.ToInt32(dutyList[a].TimeslotID.Substring(2, 2));
DateTime dt = new DateTime(year, month, day);
//get the column number to insert
int colToInsert = 3 + (dateList.FindIndex(b => b.Date == dt.Date) * 3);
//get the column number if not AM
if (dutyList[a].TimeslotID.Substring(0, 2).Equals("PM"))
{
colToInsert += 1;
}
else if (dutyList[a].TimeslotID.Substring(0, 2).Equals("EV"))
{
colToInsert += 2;
}
//retrieve course list(exam) on given location and timeslotID
List <string> paperList = paperExaminedControl.searchPaperExamined(dutyList[a].Location, dutyList[a].TimeslotID);
//check if the chief is also an examiner
var result = paperExaminedList.Select(s => s.CourseCode).Intersect(paperList);
//insert only if no exemption is inserted
if (worksheet.Cells[rowNoStaff - 1, colToInsert].Value == null)
{
totalDuty++;
worksheet.Cells[rowNoStaff - 1, colToInsert].IsRichText = true;
//compare duty type
if (dutyList[a].Location.Equals("Block DS"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("DS");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].CategoryOfInvigilator.Equals("Relief"))
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("R");
ert.Size = 7;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
if (dutyList[a].Duration != 2)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add(dutyList[a].Duration.ToString());
ert.Size = 7;
}
}
else if (dutyList[a].Duration == 3)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("3");
ert.Size = 7;
}
else if (dutyList[a].Duration == 2)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
}
else if (dutyList[a].Duration == 1)
{
ExcelRichText ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("\u2713");
ert.Size = 12;
if (result.ToList().Count > 0)
{
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("E");
ert.Size = 7;
}
ert = worksheet.Cells[rowNoStaff - 1, colToInsert].RichText.Add("1");
ert.Size = 7;
}
}
//if printing last duty in a duty list, print totalDuty
if (a + 1 == dutyList.Count)
{
worksheet.Cells[rowNoStaff - 1, colNoTotal].Value = totalDuty;
}
}
}
}
}
//shutdown connection
exemptionControl.shutDown();
dutyControl.shutDown();
//center "No." and all the ticks
worksheet.Cells[4, 1, staffList.Count + 4, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[4, 3, rowNoStaff, colNoTotal].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
//save new workbook
package.Save();
}
protected void lnkExportExcel_Click(object sender, EventArgs e)
{
DateTime searchDate = DateTime.Today;
if (!string.IsNullOrEmpty(txtDate.Text))
{
searchDate = Convert.ToDateTime(txtDate.Text);
}
List <SummaryPayFeeDaily> dataList = CommonList.GetSummaryPayFeeDailyData(searchDate, DateTime.MinValue, txtSearch.Text, STORE_ID);
if (dataList.Any())
{
using (ExcelPackage package = new ExcelPackage())
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("BTP");
worksheet.View.ZoomScale = 90;
worksheet.Cells.Style.Font.Size = 12;
worksheet.Cells.Style.Font.Name = "Times New Roman";
worksheet.Cells[1, 1, 1, 8].Merge = true;
worksheet.Cells[1, 1, 1, 8].Value = "Bảng Thu Phí " + dataList[0].STORE_NAME;
worksheet.Row(1).Height = 20;
worksheet.Cells[1, 1, 1, 8].Style.Font.Bold = true;
worksheet.Cells[1, 1, 1, 8].Style.Font.Size = 14;
worksheet.Cells[1, 1, 1, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1, 2, 8].Merge = true;
worksheet.Cells[2, 1, 2, 8].Value = "(" + searchDate + ")";
worksheet.Row(2).Height = 35;
worksheet.Cells[2, 1, 2, 8].Style.Font.Bold = true;
worksheet.Cells[2, 1, 2, 8].Style.Font.Size = 18;
worksheet.Cells[2, 1, 2, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[3, 1, 3, 8].Merge = true;
worksheet.Cells[3, 1, 3, 8].Value = "";
worksheet.Row(2).Height = 20;
worksheet.Column(1).Width = 5;
worksheet.Column(2).Width = 35;
worksheet.Column(3).Width = 30;
worksheet.Column(4).Width = 30;
worksheet.Column(5).Width = 25;
worksheet.Column(6).Width = 30;
worksheet.Column(7).Width = 20;
worksheet.Column(8).Width = 15;
worksheet.Column(7).Style.WrapText = true;
worksheet.Cells[4, 1].Value = "#";
worksheet.Cells[4, 2].Value = "Tên khách hàng";
worksheet.Cells[4, 3].Value = "Loại hình thuê";
worksheet.Cells[4, 4].Value = "Số ĐT khách hàng";
worksheet.Cells[4, 5].Value = "Giá trị HĐ/Phí";
worksheet.Cells[4, 6].Value = "Ghi chú";
worksheet.Cells[4, 7].Value = "Số lần đóng phí";
worksheet.Cells[4, 8].Value = "Thông báo";
worksheet.Cells[4, 1, 4, 8].Style.Font.Bold = true;
worksheet.Cells[4, 1, 4, 8].Style.Font.Size = 13;
worksheet.Row(4).Height = 25;
int no = 1;
int index = 5;
foreach (var contract in dataList)
{
worksheet.Cells[index, 1].Value = no;
worksheet.Cells[index, 2].Style.WrapText = true;
worksheet.Cells[index, 2].IsRichText = true;
ExcelRichText ert = worksheet.Cells[index, 2].RichText.Add(contract.CUSTOMER_NAME);
ert.Bold = true;
//ert = worksheet.Cells[index, 2].RichText.Add("\n(" + (contract.BIRTH_DAY == null ? "" : contract.BIRTH_DAY.Value.ToString("dd/MM/yyyy")) + ")");
//ert.Bold = false;
worksheet.Cells[index, 3].Value = contract.RENT_TYPE_NAME;
worksheet.Cells[index, 4].Value = contract.PHONE;
worksheet.Cells[index, 5].Value = string.Format("{0:0,0}", contract.PAY_FEE);
worksheet.Cells[index, 6].Value = contract.NOTE;
worksheet.Cells[index, 7].Value = contract.PAY_TIME + " lần";
worksheet.Cells[index, 8].Value = contract.PAY_MESSAGE;
no += 1;
index += 1;
}
worksheet.Cells[4, 1, index, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[4, 1, index, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[4, 1, index, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells[4, 1, index, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
if ((Request.Browser.Browser.ToLower() == "ie") && (Request.Browser.MajorVersion < 9))
{
Response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
Response.Cache.SetMaxAge(TimeSpan.FromMilliseconds(1));
}
else
{
Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache); //IE set to not cache
Response.Cache.SetNoStore(); //Firefox/Chrome not to cache
Response.Cache.SetExpires(DateTime.UtcNow); //for safe measure expire it immediately
}
string fileName = string.Format("BTP {0}.{1}", searchDate.ToString("dd-MM-yyyy"), "xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=\"" + fileName + "\"");
Response.BinaryWrite(package.GetAsByteArray());
Response.Flush();
Response.Close();
Response.End();
}
}
}
public void ExportExcelSpecial()
{
try
{
if (dataList != null && dataList.Count > 0)
{
string tmpFileName = FilesUtil.GetTmpXlsxFileName();
FileInfo newFile = new FileInfo(tmpFileName);
using (ExcelPackage NewPck = new ExcelPackage(newFile))
using (ExcelWorkbook NewWb = NewPck.Workbook)
{
//load template
string TEMPLATE_FILE_NAME = "RPT001_Template.xlsx";
string TemplatePath = Path.Combine(Application.StartupPath, OISBaseConstant.PATH_TEMPLATE, TEMPLATE_FILE_NAME);
FileInfo OrgFile = new FileInfo(TemplatePath);
//create sheet
using (ExcelPackage OrgPck = new ExcelPackage(OrgFile))
using (ExcelWorkbook OrgWb = OrgPck.Workbook)
{
ExcelWorksheet OrgSheet;
int firstRecordRow, templateColumnCount, LV3Row, LV2Row, LOCRow;
if (HasResultDayAndNight)
{
OrgSheet = OrgWb.Worksheets[2];
firstRecordRow = 4;
templateColumnCount = 7;
LV3Row = 4;
LV2Row = 5;
LOCRow = 6;
}
else
{
OrgSheet = OrgWb.Worksheets[1];
firstRecordRow = 3;
templateColumnCount = 6;
LV3Row = 3;
LV2Row = 4;
LOCRow = 5;
}
//add sheet
NewWb.Worksheets.Add(dataList[0].REPORTNUMBER, OrgSheet);
ExcelWorksheet sht = NewWb.Worksheets[1];
sht.DeleteRow(firstRecordRow, 4);
sht.Cells.Worksheet.Workbook.Styles.UpdateXml();
int nextRow = firstRecordRow;
string tmpLV3 = "", tmpLV2 = "";
for (int i = 0; i < dataList.Count; i++)
{
sp_RPT001_GetWorkPlaceLightReport_Result data = dataList[i];
if (!data.LOC_NAME_LV3.IsNull())
{
if (tmpLV3 != data.LOC_NAME_LV3)
{
tmpLV3 = data.LOC_NAME_LV3;
OrgSheet.Cells[LV3Row, 1, LV3Row, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]);
sht.Cells[nextRow, 2].Value = data.LOC_NAME_LV3;
nextRow++;
}
}
if (!data.LOC_NAME_LV2.IsNull())
{
if (tmpLV2 != data.LOC_NAME_LV2)
{
tmpLV2 = data.LOC_NAME_LV2;
OrgSheet.Cells[LV2Row, 1, LV2Row, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]);
sht.Cells[nextRow, 2].Value = data.LOC_NAME_LV2;
nextRow++;
}
}
if (i + 1 == dataList.Count)
{
OrgSheet.Cells[firstRecordRow + 3, 1, firstRecordRow + 3, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]);
}
else
{
OrgSheet.Cells[LOCRow, 1, LOCRow, templateColumnCount].Copy(sht.Cells[nextRow, 1, nextRow, templateColumnCount]);
}
sht.Cells[nextRow, 1].Value = i + 1;
sht.Cells[nextRow, 2].Value = data.LOC_NAME;
sht.Cells[nextRow, 3].Value = data.STDLIGHT_NAME;
if (data.RESULT_DAY.HasValue)
{
sht.Cells[nextRow, 4].Value = data.RESULT_DAY_STR;
}
else
{
sht.Cells[nextRow, 4].Value = data.RESULT_NIGHT_STR;
}
if (HasResultDayAndNight)
{
sht.Cells[nextRow, 5].Value = data.RESULT_NIGHT_STR;
//sht.Cells[nextRow, 6].Value = data.STDLIGHT_STANDARD_STR;
sht.Cells[nextRow, 7].Value = data.CONDITION;
ExcelRichText rtDir2 = sht.Cells[nextRow, 6].RichText.Add(data.STDLIGHT_STANDARD.GetValueOrDefault().ToString("#,##0"));
rtDir2.UnderLine = false;
}
else
{
//sht.Cells[nextRow, 5].Value = data.STDLIGHT_STANDARD_STR;
sht.Cells[nextRow, 6].Value = data.CONDITION;
ExcelRichText rtDir2 = sht.Cells[nextRow, 5].RichText.Add(data.STDLIGHT_STANDARD.GetValueOrDefault().ToString("#,##0"));
rtDir2.UnderLine = false;
}
nextRow++;
}
//Open Excel
NewPck.Save();
Process.Start(tmpFileName);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}