private void btnExtractData_Click(object sender, System.EventArgs e)
{
#region Workbook Initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the input file path
string inputPath = GetFullTemplatePath("FindAndExtract.xls");
//Open an existing spreadsheet which will be used as a template for getting data from input file.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Find and Extract Data
IRange result;
#region Find and Extract Numbers
if (lstFormat.SelectedItem.ToString() == "Number with format 0.00")
{
result = worksheet.FindFirst(1000000.00075, ExcelFindType.Number);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Number with format $#,##0.00")
{
result = worksheet.FindFirst(3.2, ExcelFindType.Number);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#region Find and Extract DateTime
if (lstFormat.SelectedItem.ToString() == "DateTime with format m/d/yy h:mm")
{
result = worksheet.FindFirst(DateTime.Parse("12/1/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Time with format h:mm:ss AM/PM")
{
result = worksheet.FindFirst(DateTime.Parse("1/1/2007 2:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.DateTime.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Date with format d-mmm-yy")
{
result = worksheet.FindFirst(DateTime.Parse("12/4/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Date with format Saturday, December 01, 2007")
{
result = worksheet.FindFirst(DateTime.Parse("8/1/2007 3:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#region Find and Extract Text
if (lstFormat.SelectedItem.ToString() == "Text")
{
result = worksheet.FindFirst("Simple text", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Text With Styles and Patterns")
{
result = worksheet.FindFirst("Text with Styles and patterns", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Number with Text Format")
{
result = worksheet.FindFirst("$8.200", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#endregion
#region Workbook Close and Dispose
//close the workbook
workbook.Close();
excelEngine.Dispose();
#endregion
}
public ActionResult FindAndExtract(string selectoption)
{
if (selectoption == null)
{
FillData();
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath("FindAndExtract.xls"));
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
IRange result;
try
{
switch (selectoption)
{
#region Find and Extract Numbers
case "Number with format 0.00":
{
result = sheet.FindFirst(1000000.00075, ExcelFindType.Number);
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
case "Number with format $#,##0.00":
{
result = sheet.FindFirst(3.2, ExcelFindType.Number);
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
#endregion
#region Find and Extract DateTime
case "DateTime with format m/d/yy h:mm":
{
result = sheet.FindFirst(DateTime.Parse("12/1/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
case "Time with format h:mm:ss AM/PM":
{
result = sheet.FindFirst(DateTime.Parse("1/1/2007 2:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.DateTime.ToString());
break;
}
case "Date with format d-mmm-yy":
{
result = sheet.FindFirst(DateTime.Parse("12/4/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
case "Date with format Saturday, December 01, 2007":
{
result = sheet.FindFirst(DateTime.Parse("8/1/2007 3:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
#endregion
#region Find and Extract Text
case "Text":
{
result = sheet.FindFirst("Simple text", ExcelFindType.Text);
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
case "Text With Styles and Patterns":
{
result = sheet.FindFirst("Text with Styles and patterns", ExcelFindType.Text);
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
case "Number with Text Format":
{
result = sheet.FindFirst("$8.200", ExcelFindType.Text);
//Gets the cell display text
ViewData.Add("displayText", result.DisplayText.ToString());
//Gets the cell value
ViewData.Add("valueText", result.Value2.ToString());
break;
}
#endregion
default:
break;
}
workbook.Close();
excelEngine.Dispose();
}
catch (Exception)
{
}
FillData();
return(View());
}
/// <summary>
/// Outs the simple report.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataSource">The data source.</param>
/// <param name="replaceValues">The replace values.</param>
/// <param name="viewName">Name of the view.</param>
/// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param>
/// <param name="fileName">Name of the file.</param>
/// <returns></returns>
private bool OutSimpleReport <T>(List <T> dataSource, Dictionary <string, string> replaceValues, string viewName, bool isPrintPreview, ref string fileName)
{
string file = string.Empty;
bool result = false;
// Get template stream
MemoryStream stream = GetTemplateStream(viewName);
// Check if data is null
if (stream == null)
{
return(false);
}
// Create excel engine
ExcelEngine engine = new ExcelEngine();
IWorkbook workBook = engine.Excel.Workbooks.Open(stream);
IWorksheet workSheet = workBook.Worksheets[0];
ITemplateMarkersProcessor markProcessor = workSheet.CreateTemplateMarkersProcessor();
// Replace value
if (replaceValues != null && replaceValues.Count > 0)
{
// Find and replace values
foreach (KeyValuePair <string, string> replacer in replaceValues)
{
Replace(workSheet, replacer.Key, replacer.Value);
}
}
// Fill variables
markProcessor.AddVariable(viewName, dataSource);
// End template
markProcessor.ApplyMarkers(UnknownVariableAction.ReplaceBlank);
// Delete temporary row
IRange range = workSheet.FindFirst(TMP_ROW, ExcelFindType.Text);
// Delete
if (range != null)
{
workSheet.DeleteRow(range.Row);
}
file = Path.GetTempFileName() + Constants.FILE_EXT_XLS;
fileName = file;
// Output file
if (!FileCommon.IsFileOpenOrReadOnly(file))
{
workBook.SaveAs(file);
result = true;
}
// Close
workBook.Close();
engine.Dispose();
// Print preview
if (result && isPrintPreview)
{
PrintExcel(file);
File.Delete(file);
}
return(result);
}
private string Export(string saveOption, List <UserEventLogSearchResult> eventLogList, UserEventLogSearchCondition model)
{
string pathExport = string.Empty;
// Khỏi tạo bảng excel
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(HttpContext.Current.Server.MapPath("/Template/LichSuTruyCapSuDung.xlsx"));
IWorksheet sheet = workbook.Worksheets[0];
//Thay đổi title trong báo cáo
// Utiliti.FillTitleExport(db, sheet);
//DateTime dateNow = DateTime.Now;
//IRange rangeDay = sheet.FindFirst("<Day>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeDay.Text = rangeDay.Text.Replace("<Day>", dateNow.Day.ToString());
//IRange rangeMonth = sheet.FindFirst("<Month>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeMonth.Text = rangeMonth.Text.Replace("<Month>", dateNow.Month.ToString());
//IRange rangeYear = sheet.FindFirst("<Year>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
//rangeYear.Text = rangeYear.Text.Replace("<Year>", dateNow.Year.ToString());
IRange rangeDateFrom = sheet.FindFirst("<DateFrom>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
rangeDateFrom.Text = rangeDateFrom.Text.Replace("<DateFrom>", (model.LogDateFrom.HasValue ? model.LogDateFrom.Value.ToString("dd/MM/yyy") : "--/--/----"));
IRange rangeDateTo = sheet.FindFirst("<DateTo>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
rangeDateTo.Text = rangeDateTo.Text.Replace("<DateTo>", (model.LogDateTo.HasValue ? model.LogDateTo.Value.ToString("dd/MM/yyy") : "--/--/----"));
int total = eventLogList.Count;
int index = 1;
var listExport = (from a in eventLogList
select new
{
Index = index++,
a.LogTypeName,
// a.UserType,
a.Description,
CreateDate = a.CreateDate.HasValue ? a.CreateDate.Value.ToString("dd-MM-yyyy HH:mm:ss") : string.Empty,
a.UserName
}).ToList();
IRange iRangeData = sheet.FindFirst("<Data>", ExcelFindType.Text, ExcelFindOptions.MatchCase);
sheet.ImportData(listExport, iRangeData.Row, iRangeData.Column, false);
if (saveOption.Equals("PDF"))
{
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
sheet.Range[iRangeData.Row, 1, total + 4, 6].Borders.Color = ExcelKnownColors.Black;
pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.pdf";
//convert the sheet to pdf
ExcelToPdfConverter converter = new ExcelToPdfConverter(sheet);
PdfDocument pdfDocument = new PdfDocument();
pdfDocument = converter.Convert();
pdfDocument.Save(HttpContext.Current.Server.MapPath(pathExport));
pdfDocument.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
}
else
{
pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "LichSuTruyCapSuDung.xlsx";
workbook.SaveAs(HttpContext.Current.Server.MapPath(pathExport));
}
return(pathExport);
}
