public async Task <int> DeleteEmploye(int id)
{
IWorksheet worksheet = await Excel.get();
int length = worksheet.Rows.Count();
worksheet.DeleteRow(id);
Excel._workbook.Close(true);
return(id);
}
//
// GET: /RowColumnManipulation/
public ActionResult RowColumnManipulation(string Saveoption, string button)
{
if (Saveoption == null)
{
return(View());
}
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"monthly_sales.xlsx"));
return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
//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;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook;
if (Saveoption == "Xlsx")
{
application.DefaultVersion = ExcelVersion.Excel2016;
workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xlsx"));
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath("monthly_sales.xls"));
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#region Grouping and ungrouping
// Grouping by Rows
sheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows);
// Grouping by Columns
sheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns);
#endregion
#region Hiding unhiding
// Hiding fifth and sixth Column
sheet.ShowColumn(5, false);
sheet.ShowColumn(6, false);
//Showing the 28th row
sheet.ShowRow(28, true);
#endregion
#region Insert and delete
//Deleting Row
sheet.DeleteRow(25);
//Inserting Column
sheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore);
sheet.Range["G4"].Text = "Loss/Gain";
//Deleting Column
sheet.DeleteColumn(8);
#endregion
#region ColumnWidth and RowHeight
// Changing the Column Width
sheet.Range["D5"].ColumnWidth = 15;
// Changing the Row Height
sheet.Range["D29"].RowHeight = 25;
#endregion
try
{
// Save the file
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "RowColumnManipulation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
/// <summary>
/// Create the Excel document with form controls
/// </summary>
/// <returns>Return the created excel document as stream</returns>
public MemoryStream FormControlsXlsIO(string version)
{
onlinePayments = new string[] { "Credit Card", "Net Banking" };
//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
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
//Check if user opts for XLSX
if (version == "XLSX")
{
application.DefaultVersion = ExcelVersion.Excel2016;
color1 = Color.FromArgb(255, 255, 230);
}
//Check if user opts for XLS
else
{
color1 = Color.FromArgb(255, 255, 204);
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//Workbook created with two worksheets
IWorkbook workbook = application.Workbooks.Create(2);
//The first worksheet object in the worksheets collection is accessed.
//(0 based index)
IWorksheet sheet2 = workbook.Worksheets[1];
//Assigning the array content to cells
//by passing row and column position
for (int i = 0; i < onlinePayments.Length; i++)
{
sheet2.SetValue(i + 1, 1, onlinePayments[i]);
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
FileStream imageStream = new FileStream(ResolveApplicationImagePath("contact_sales.gif"), FileMode.Open, FileAccess.Read);
sheet.Pictures.AddPicture(2, 3, imageStream);
sheet[4, 3].Text = "Phone";
sheet[4, 3].CellStyle.Font.Bold = true;
sheet[5, 3].Text = "Toll Free";
sheet[5, 5].Text = "1-888-9DOTNET";
sheet[6, 5].Text = "1-888-936-8638";
sheet[7, 5].Text = "1-919-481-1974";
sheet[8, 3].Text = "Fax";
sheet[8, 5].Text = "1-919-573-0306";
sheet[9, 3].Text = "Email";
sheet[10, 3].Text = "Sales";
//Creating the hyperlink in the 10th column and
//5th row of the sheet
IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
sheet[12, 3].Text = "Please fill out all required fields.";
sheet[14, 5].Text = "First Name*";
sheet[14, 5].CellStyle.Font.Bold = true;
sheet[14, 8].Text = "Last Name*";
sheet[14, 8].CellStyle.Font.Bold = true;
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[17, 3].Text = "Company*";
textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[19, 3].Text = "Phone*";
textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[21, 3].Text = "Email*";
textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[23, 3].Text = "Website";
textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385);
ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
chkBoxProducts.Text = "";
sheet[25, 3].Text = "Multiple products?";
sheet[27, 3, 28, 3].Merge();
sheet[27, 3].Text = "Product(s)*";
sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
sheet[30, 3].Text = "Selected Products Count";
//counts the selected product
sheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
//create the textbox for additional information
sheet[35, 3].Text = "Additional Information";
textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385);
if (!(version == "XLS"))
{
sheet[43, 3].Text = "Online Payment";
//Create combobox
IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100);
//Assign range to display in dropdown list
comboBox1.ListFillRange = sheet2["A1:A2"];
//select 1st item from the list
comboBox1.SelectedIndex = 1;
sheet[46, 3].Text = "Card Type";
IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5);
optionButton1.Text = "American Express";
optionButton1.CheckState = ExcelCheckState.Checked;
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7);
optionButton1.Text = "Master Card";
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9);
optionButton1.Text = "Visa";
}
//column alignment
sheet.Columns[0].AutofitColumns();
sheet.Columns[3].ColumnWidth = 12;
sheet.Columns[4].ColumnWidth = 10;
sheet.Columns[5].ColumnWidth = 10;
sheet.IsGridLinesVisible = false;
sheet.DeleteRow(40);
sheet.DeleteRow(41);
sheet.DeleteRow(42);
sheet.DeleteRow(45);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
public int convertExceltoEMF(string path)
{
double sWidth = Command.thisCommand.getSheetWidth();
setSheetParameters(sWidth);
string sheetSize = Command.thisCommand.getSheetSize();
if (sheetSize == "24 x 36")
{
formatHeight = 22.25;
centerLine = 12.0;
imagePerSheet = 4;
}
else if (sheetSize == "30 x 42")
{
formatHeight = 28.25;
centerLine = 15.0;
imagePerSheet = 5;
}
else if (sheetSize == "36 x 48")
{
formatHeight = 34.25;
centerLine = 18.0;
imagePerSheet = 6;
}
else
{
}
double cellB;
double cellC;
double cellD;
int resolution = 150;
int pWidth;
var savePath = Path.GetDirectoryName(path) + @"\Master Schedule (Images)\";
if (Directory.Exists(savePath))
{
removeExistingImages(savePath);
}
else
{
Directory.CreateDirectory(savePath);
}
var saveName = "Master Schedule";
Command.thisCommand.dialog.ConvertingWord();
//Load the document.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = excelEngine.Excel.Workbooks.OpenReadOnly(path);
IWorksheet sheet = workbook.Worksheets[1];
//read widths of the cells we care about, B, C, and D
cellB = sheet.GetColumnWidthInPixels(2);
cellC = sheet.GetColumnWidthInPixels(3);
cellD = sheet.GetColumnWidthInPixels(4);
double cellWidth = cellB + cellC + cellD;
float cellInches = Convert.ToSingle(cellWidth) / 72.0f;
//store document width in pixels @ 150 DPI
pWidth = Convert.ToInt32((cellInches * (float)resolution));
double multiplier = 994.0 / pWidth;
sheet.SetColumnWidthInPixels(2, Convert.ToInt32(cellB * multiplier));
sheet.SetColumnWidthInPixels(3, Convert.ToInt32(cellC * multiplier));
sheet.SetColumnWidthInPixels(4, Convert.ToInt32(cellD * multiplier));
cellB = sheet.GetColumnWidthInPixels(2);
cellC = sheet.GetColumnWidthInPixels(3);
cellD = sheet.GetColumnWidthInPixels(4);
cellWidth = cellB + cellC + cellD;
cellInches = Convert.ToSingle(cellWidth) / 72.0f;
//store document width in pixels @ 150 DPI
pWidth = Convert.ToInt32((cellInches * (float)resolution));
//Read the last used row in the doc
int lastRow = sheet.UsedRange.LastRow;
//Resize fonts in every cell
for (int x = 1; x <= lastRow; x++)
{
string searchRow = "A" + x.ToString();
double fntSize = sheet.Range[searchRow].CellStyle.Font.Size;
var fgColor = sheet.Range[searchRow].CellStyle.PatternColorIndex;
var bgColor = sheet.Range[searchRow].CellStyle.ColorIndex;
//ExcelKnownColors checkColor = (ExcelKnownColors)65;
fixCellSize(sheet, x);
/*if (fntSize != 18)
* {
* if (bgColor == checkColor)
* {
* string searchRowB = "B" + x.ToString();
* double fontSize = sheet.Range[searchRowB].CellStyle.Font.Size;
* if (fontSize != 10)
* {
* fixCellSize(sheet, x);
* }
* }
* }*/
}
string fullRange = "B1:D" + lastRow.ToString();
string lastRange = "D1:D" + lastRow.ToString();
// do some setup on the sheet
sheet.IsGridLinesVisible = false;
sheet.Range[lastRange].WrapText = true;
//sheet.Range[lastRange].AutofitRows();
//sheet.Range[fullRange].AutofitRows();
//sheet.Range[lastRange].AutofitRows();
//delete hidden cells
for (int rows = 1; rows <= lastRow; rows++)
{
bool row = sheet.IsRowVisible(rows);
if (row == false)
{
sheet.DeleteRow(rows);
rows--;
}
}
lastRow = sheet.UsedRange.LastRow;
//setup to figure out pages to publish
double runningHeight = 0.0;
ArrayList page = new ArrayList();
ArrayList startCell = new ArrayList();
ArrayList endCell = new ArrayList();
int sPage = 1;
int sCell = 1;
//seperate rows into pages
for (int rows = 1; rows <= lastRow; rows++)
{
string content = sheet.Range["B1"].Text;
double tempRow1 = sheet.GetRowHeight(1);
double tempRow = sheet.GetRowHeightInPixels(rows) / 72.0;
runningHeight = runningHeight + tempRow;
if (runningHeight > formatHeight)
{
page.Add(sPage);
startCell.Add(sCell);
endCell.Add(rows - 1);
sPage++;
sCell = rows;
runningHeight = tempRow;
}
if (rows == lastRow)
{
page.Add(sPage);
startCell.Add(sCell);
endCell.Add(rows);
}
}
Command.thisCommand.dialog.setPageCount(page.Count);
Command.thisCommand.dialog.SetupProgress(page.Count, "Task: Converting Excel Document to Images");
//save document by row ranges as images
for (int i = 0; i <= page.Count - 1; i++)
{
int pageValue = i + 1;
runningHeight = 0.0;
int startingCell = int.Parse(startCell[i].ToString());
int endingCell = int.Parse(endCell[i].ToString());
for (int x = startingCell; x < endingCell; x++)
{
double tempRow = sheet.GetRowHeightInPixels(x) / 72.0;
runningHeight = runningHeight + tempRow;
}
float pageHeight = Convert.ToSingle(runningHeight);
pageHeight = pageHeight * 150;
Image image = sheet.ConvertToImage(startingCell, 2, endingCell, 4, ImageType.Metafile, null);
Bitmap bitmap = null;
bitmap = new Bitmap(pWidth, Convert.ToInt32(pageHeight));
bitmap.SetResolution((float)resolution, (float)resolution);
using (Graphics g = Graphics.FromImage(bitmap))
{
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
g.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;
g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
g.DrawImage(image, 0, 0, pWidth, Convert.ToInt32(pageHeight));
g.Dispose();
}
bitmap.Save(savePath + saveName + pageValue.ToString("D2") + ".png", ImageFormat.Png);
bitmap.Dispose();
//setup progress for export
//int percentage = (int)Math.Round(((double)i / (double)page.Count) * 100.0);
//if (OnProgressUpdate != null)
//{
// OnProgressUpdate(Convert.ToInt32(percentage));
//}
//i++;
Command.thisCommand.dialog.IncrementProgress();
bitmap = null;
}
//close document without saving
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
return(page.Count);
}
/// <summary>
/// Export the List of Type
/// </summary>
/// <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 OutReport <T>(List <IGrouping <string, T> > groupData, Dictionary <string, string> replaceValues,
string groupBox, string viewName, bool isPrintPreview, 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);
// Get sheets
IWorksheet workSheet = workBook.Worksheets[0];
IWorksheet tmpSheet = workBook.Worksheets.Create(TMP_SHEET);
// Copy template of group to temporary sheet
IRange range = workSheet.Range[groupBox];
int rowCount = range.Rows.Count();
IRange tmpRange = tmpSheet.Range[groupBox];
range.CopyTo(tmpRange, ExcelCopyRangeOptions.All);
// 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);
}
}
// Loop data
for (int i = groupData.Count - 1; i >= 0; i--)
{
IGrouping <string, T> group = groupData[i];
List <T> listMember = group.ToList();
// Create template maker
ITemplateMarkersProcessor markProcess = workSheet.CreateTemplateMarkersProcessor();
// Fill data into templates
if (listMember.Count > 0)
{
markProcess.AddVariable(viewName, listMember);
markProcess.ApplyMarkers();
}
else
{
markProcess.ApplyMarkers(UnknownVariableAction.Skip);
}
// Insert template rows
if (i > 0)
{
workSheet.InsertRow(range.Row, rowCount);
tmpRange.CopyTo(workSheet.Range[groupBox], ExcelCopyRangeOptions.All);
}
}
// Find row
IRange[] rowSet = workSheet.FindAll(TMP_ROW, ExcelFindType.Text);
// Delete row
for (int i = rowSet.Count() - 1; i >= 0; i--)
{
range = rowSet[i];
// Delete
if (range != null)
{
workSheet.DeleteRow(range.Row);
}
}
// Get file name
if (isPrintPreview)
{
file = Path.GetTempFileName() + Constants.FILE_EXT_XLS;
}
else
{
file = fileName;
}
// Remove temporary sheet
workBook.Worksheets.Remove(tmpSheet);
// 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);
}
/// <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 void btnCreate_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 file path of input file
string inputPath = GetFullTemplatePath("monthly_sales.xls");
//Open an existing spreadsheet which will be used as a template for creating new worksheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
//IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorkbook workbook = application.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Row and Column Manipulations
#region Grouping and ungrouping
// Grouping by Rows
worksheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows);
// Grouping by Columns
worksheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns);
#endregion
#region Hiding unhiding
// Hiding fifth and sixth Column
worksheet.ShowColumn(5, false);
worksheet.ShowColumn(6, false);
//Showing the 28th row
worksheet.ShowRow(28, true);
#endregion
#region Insert and delete
//Deleting Row
worksheet.DeleteRow(25);
//Inserting Column
worksheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore);
worksheet.Range["G4"].Text = "Loss/Gain";
//Deleting Column
worksheet.DeleteColumn(8);
#endregion
#region ColumnWidth and RowHeight
// Changing the Column Width
worksheet.Range["D5"].ColumnWidth = 15;
// Changing the Row Height
worksheet.Range["D29"].RowHeight = 25;
#endregion
#endregion
#region Workbook Save
//Set the default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "Sample.xls";
}
//Set the default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "Sample.xlsx";
}
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private int ProcessWorkSheetList(
IProgressControl pc, IWorksheet sheet, T query,
params string[] localParameterValues)
{
pc.SetStatus("Инициализация запроса...");
List <DbResult> results = query.SelectSimple(conn, pc, localParameterValues);
pc.SetStatus("Идет обработка данных...");
int firstColNum = 0;
int firstRowNum = 0;
foreach (var cell in sheet.UsedCells)
{
string value = cell.Value;
if (string.IsNullOrEmpty(value))
{
continue;
}
if (value.Equals(string.Format("#{0}", query.Name)) ||
value.IndexOf(string.Format("#{0}(", query.Name)) >= 0)
{
firstRowNum = cell.LastRow;
break;
}
else if (cell.Value.StartsWith("#"))
{
// Установка параметров в отчете
ReplaceWithParameterValues(cell);
}
}
string xlFieldValue;
Dictionary <int, string> fieldPositions = new Dictionary <int, string>();
int j = 0;
while (j < sheet.Rows[firstRowNum - 1].Columns.Length &&
sheet.Rows[firstRowNum - 1].Columns[j].Value != string.Empty)
{
fieldPositions[j] = sheet.Rows[firstRowNum - 1].Columns[j].Value;
j++;
}
firstColNum = j;
sheet.DeleteRow(firstRowNum);
if (results.Count != 0)
{
sheet.InsertRow(firstRowNum, results.Count);
}
Func <DbResult, int, int, object> GetValue = (dbResult, xlRowNum, xlColNum) =>
{
fieldPositions.TryGetValue(xlColNum, out xlFieldValue);
// В ячейке формула
if (xlFieldValue.StartsWith("="))
{
return(EvaluateFormula(xlFieldValue, xlRowNum));
}
if (xlFieldValue.StartsWith("#") && !xlFieldValue.Equals(string.Format("#{0}", query.Name)))
{
// В ячейке находится скалярная функция (запрос)
var funcQuery = FindQuery(xlFieldValue);
if (funcQuery != null)
{
return(funcQuery.ExecuteScalarSQL(conn,
query.FieldNames,
dbResult.Fields,
ExtractParameterValues(xlFieldValue)));
}
}
string searchFormat = xlColNum == 0 ? "#{0}" : "#:{0}:";
int index = query.FieldNames.FindIndex(fn =>
xlFieldValue.Equals(string.Format(searchFormat, fn)));
if (index < 0)
{
return(xlFieldValue);
}
if (dbResult.Fields[index].Equals("$id"))
{
return(xlRowNum - firstRowNum + 2);
}
return(dbResult.Fields[index]);
};
pc.SetProgress(0);
int i = firstRowNum - 1;
int progress = 0;
foreach (DbResult result in results)
{
for (j = 0; j < firstColNum; j++)
{
sheet.Rows[i].Columns[j].Value2 = GetValue(result, i, j);
sheet.Rows[i].Columns[j].BorderAround(ExcelLineStyle.Thin);
}
pc.SetProgress(progress++);
i++;
}
for (j = 0; j < firstColNum; j++)
{
if (sheet.Rows[i].Columns[j].Value.StartsWith("=SUM", StringComparison.InvariantCultureIgnoreCase))
{
string addressLocal = sheet.Rows[i].Columns[j].AddressLocal;
string column = GetColumn(addressLocal);
sheet.Rows[i].Columns[j].Formula = string.Format("=SUM({0}{1}:{2}{3})",
column, firstRowNum, column, i);
}
}
// Количество новых ячеек
return(results.Count * query.FieldNames.Count);
}
public static bool WriteCalculationsToExporterTemplate(ObservableCollection <Calculation3d> calcs,
string path, ExporterTemplate template, bool asPdf = false
)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
bool targetIsFolder = false;
application.DefaultVersion = ExcelVersion.Excel2013;
string ext = Path.GetExtension(path);
targetIsFolder = string.IsNullOrEmpty(ext);
string root = Path.GetDirectoryName(path);
string filename = string.Format(@"{0}_{1}{2}",
Regex.Replace(Strings.MyCalculation, "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
Regex.Replace("1", "[^a-zA-Z0-9_]+", "_", RegexOptions.Compiled),
string.IsNullOrEmpty(ext) ? asPdf ? ".pdf" : ".xlsx" : ext
);
filename = getDuplicatedFileName(targetIsFolder ? path : root, filename);
//Create a workbook
IWorkbook workbook = application.Workbooks.Open(template.TemplatePath, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
foreach (ExporterSettings setting in template.Settings)
{
worksheet = workbook.Worksheets[setting.WorkSheetName];
switch (setting.Attribute.Property)
{
#region List
case ExporterProperty.CalculationList:
int insertRow = Convert.ToInt32(setting.Coordinates.Row);
IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle;
if (calcs.Count != 1)
{
worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter);
}
//Go to list
for (int i = 0; i < calcs.Count; i++)
{
foreach (Printer3d printer in calcs[i].Printers)
{
foreach (Material3d material in calcs[i].Materials)
{
int Row = Convert.ToInt32(insertRow) + i;
string Col = setting.Coordinates.Column;
// Pos
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1;
calcs[i].Material = material;
calcs[i].Printer = printer;
// Description
StringBuilder sb = new StringBuilder();
sb.Append(calcs[i].Name);
/*
* sb.AppendLine(string.Format("Volume: {0} | Time: {1}",
* Convert.ToDouble(calcs[i].Volume),
* Convert.ToDouble(calcs[i].CalculatedPrintTime))
* );
*/
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString();
// Quantity
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0";
// Single
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity)));
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
// Total
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
if (i < calcs.Count - 1)
{
}
}
}
}
// remove the empty rows
if (calcs.Count != 1)
{
worksheet.DeleteRow(insertRow + calcs.Count, calcs.Count - 1);
}
break;
#endregion
}
}
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert();
//Save the PDF file
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
pdfDocument.Save(savePath);
}
else
{
string savePath = targetIsFolder ? Path.Combine(path, filename) : Path.Combine(root, filename);
workbook.SaveAs(savePath);
}
workbook.Close();
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
public static bool WriteCalculationsToTemplate(ObservableCollection <Calculation3d> calcs,
string template, string path, ExcelWriteTemplateSettings Settings, bool asPdf = false
)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook
IWorkbook workbook = application.Workbooks.Open(template, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Accessing first table in the sheet
IListObject table = worksheet.ListObjects[0];
string currencySymbol = CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;
int insertRow = Convert.ToInt32(Settings.StartRow);
IStyle rowStyle = worksheet.Rows.ElementAt(insertRow).CellStyle;
worksheet.InsertRow(insertRow, calcs.Count - 1, ExcelInsertOptions.FormatAsAfter);
//Go to list
for (int i = 0; i < calcs.Count; i++)
{
int Row = Convert.ToInt32(Settings.StartRow) + i;
//worksheet.Rows.ElementAt(Row).CellStyle = rowStyle;
string Col = Settings.StartColumn;
// Pos
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = i + 1;
// Description
StringBuilder sb = new StringBuilder();
sb.Append(calcs[i].Name);
/*
* sb.AppendLine(string.Format("Volume: {0} | Time: {1}",
* Convert.ToDouble(calcs[i].Volume),
* Convert.ToDouble(calcs[i].CalculatedPrintTime))
* );
*/
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Text = sb.ToString();
// Quantity
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].Quantity);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = "0";
// Single
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = (Convert.ToDouble(calcs[i].TotalCosts) / (Convert.ToDouble(calcs[i].Quantity)));
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
// Total
Col = GetNextColumn(Col, 1);
worksheet.Range[string.Format("{0}{1}", Col, Row)].Number = Convert.ToDouble(calcs[i].TotalCosts);
worksheet.Range[string.Format("{0}{1}", Col, Row)].NumberFormat = currencySymbol + "#,##0.00";
if (i < calcs.Count - 1)
{
var loc = table.Location;
//worksheet.InsertRow(Row +1, 1, ExcelInsertOptions.FormatAsBefore);
}
}
// remove the empty rows
worksheet.DeleteRow(insertRow + calcs.Count - 1, calcs.Count - 1);
if (asPdf)
{
//Open the Excel document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
//Convert Excel document into PDF document
pdfDocument = converter.Convert();
//Save the PDF file
pdfDocument.Save(path);
}
else
{
workbook.SaveAs(path);
}
return(true);
}
}
catch (Exception exc)
{
logger.Error(string.Format(Strings.EventExceptionOccurredFormated, exc.TargetSite, exc.Message));
return(false);
}
}
