public ByteArrayResult ExcelToPdf([FromBody] OfficeParam par)
{
var result = new ByteArrayResult();
try
{
if (par == null)
{
throw new ArgumentNullException(nameof(par));
}
if (par.Bytes == null)
{
throw new ArgumentNullException(nameof(par.Bytes));
}
if (par.Ext == null)
{
throw new ArgumentNullException(nameof(par.Ext));
}
SpreadsheetInfo.SetLicense("ERDD-TN5J-YKX9-H1KX");
ExcelFile excel;
using (var msExcel = new MemoryStream())
{
msExcel.Write(par.Bytes, 0, par.Bytes.Length);
msExcel.Position = 0;
if (par.Ext.ToLower() == ".xls")
{
excel = ExcelFile.Load(msExcel, GemBox.Spreadsheet.LoadOptions.XlsDefault);
}
else
{
excel = ExcelFile.Load(msExcel, GemBox.Spreadsheet.LoadOptions.XlsxDefault);
}
}
using (var smPdf = new MemoryStream())
{
excel.Save(smPdf, GemBox.Spreadsheet.SaveOptions.PdfDefault);
result.Result = smPdf.ToArray();
}
}
catch (Exception ex)
{
result.Error = ex.InmostMessage();
}
return(result);
}
private void btnExcel_Click(object sender, EventArgs e)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.Worksheets.Add("New worksheet");
/*
* using Excel = Microsoft.Office.Interop.Excel.Application;............................................
*
*
*
* Workbook workbook;
* Worksheet worksheet;
* Excel excel_the_Application = new Excel();
* workbook = excel_the_Application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
* // workbook = excel_the_Application.Workbooks.Add(Type.Missing);
* worksheet = workbook.Worksheets[1];
*/
try
{
for (int gridHeaderColumn = 0; gridHeaderColumn < dataGridView1.Columns.Count; gridHeaderColumn++)
{
worksheet.Cells[1, gridHeaderColumn + 1].Value = dataGridView1.Columns[gridHeaderColumn].HeaderText;
}
for (int gridRow = 0; gridRow < dataGridView1.Rows.Count; gridRow++)
{
for (int gridColumnElement = 0; gridColumnElement < dataGridView1.Columns.Count; gridColumnElement++)
{
worksheet.Cells[2 + gridRow, 1 + gridColumnElement].Value = dataGridView1.Rows[gridRow].Cells[gridColumnElement].Value.ToString();
}
}
// workbook.SaveAs(@"C:\Users\Femi Abitogun\Desktop\formApplication\ExcelReports");
workbook.Save(@"C:\Users\Femi Abitogun\Desktop\formApplication\ExcelReports.xls");
MessageBox.Show("successfully saved to excel report");
//workbook.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
} // funtion end private void btnExcel_Click(object sender, EventArgs e)
public ActionResult VatCertificateFile(int id)
{
var transaction = transactionDac.GetWithPartner(id);
var FileName = Path.Combine(Directory.GetCurrentDirectory(), "ReportSrc/vatcertificate.xlsx");
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load(FileName, LoadOptions.XlsxDefault);
var worksheet = workbook.Worksheets[0];
worksheet.Cells[2, Col.R].SetValue(1);
worksheet.Cells[3, Col.R].SetValue(transaction.Id.ToString());
worksheet.Cells[5, Col.D].SetValue(CurrentSchoolData.sc_name);
worksheet.Cells[5, Col.R].SetValue(CurrentSchoolData.VatId);
worksheet.Cells[7, Col.D].SetValue(CurrentSchoolData.Address);
worksheet.Cells[11, Col.D].SetValue(transaction.Partner.Name);
worksheet.Cells[13, Col.D].SetValue(transaction.Partner.Address);
if (transaction.Partner.PartnerType == PartnerType.Person)
{
worksheet.Cells[10, Col.R].SetValue(transaction.Partner.VatNumber);
worksheet.Cells[11, Col.R].SetValue(string.Empty);
worksheet.Cells[17, Col.Q].SetValue("☑ (4) ภ.ง.ด.3");
}
else
{
worksheet.Cells[10, 17].SetValue(string.Empty);
worksheet.Cells[11, 17].SetValue(transaction.Partner.VatNumber);
worksheet.Cells[17, Col.Q].SetValue("☑ (7) ภ.ง.ด.53");
}
worksheet.Cells[42, Col.G].SetValue(transaction.ProductType);
worksheet.Cells[42, Col.M].SetValue(transaction.IssueDate.ToString("d MMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[42, Col.O].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
worksheet.Cells[42, Col.Q].SetValue((double)transaction.VatInclude.Value);
worksheet.Cells[48, Col.O].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
worksheet.Cells[48, Col.Q].SetValue((double)transaction.VatInclude.Value);
worksheet.Cells[50, Col.I].SetValue(VatHelper.ThaiBaht(transaction.VatInclude.ToString()));
worksheet.Cells[58, Col.K].SetValue(transaction.IssueDate.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[62, Col.M].SetValue(transaction.IssueDate.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[64, Col.F].SetValue(transaction.Partner.Name);
worksheet.Cells[66, Col.G].SetValue(VatHelper.ThaiBaht((Math.Abs(transaction.Amount) - transaction.VatInclude).ToString()));
worksheet.Cells[67, Col.M].SetValue((double)(Math.Abs(transaction.Amount) - transaction.VatInclude.Value));
var contentStream = new MemoryStream();
worksheet.PrintOptions.PaperType = PaperType.A4;
workbook.Save(contentStream, SaveOptions.PdfDefault);
return(File(contentStream, "application/pdf", $"VatCertificate-{id}.pdf"));
}
public static void EditAnExistingExcelFile(string excelFilePath, string sheetName, string cellAddress, string value)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
// Load an Excel template.
var workbook = ExcelFile.Load(excelFilePath);
// Get template sheet.
var worksheet = workbook.Worksheets[sheetName];
// Write data to Excel cell.
worksheet.Cells[cellAddress].Value = value;
workbook.Save(excelFilePath);
}
public static void WriteAsNewExcelFile(string excelFilePath, string sheetName, string cellAddress, string value)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
// Create new empty workbook.
var workbook = new ExcelFile();
// Add new sheet.
var worksheet = workbook.Worksheets.Add(sheetName);
// Write data to Excel cell.
worksheet.Cells[cellAddress].Value = value;
workbook.Save(excelFilePath);
}
static void Main(string[] args)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var excel = new ExcelFile();
var worksheet = excel.Worksheets.Add("Default");
worksheet.Cells[0, 0].SetValue("1234");
worksheet.Cells[1, 0].SetValue("5678");
worksheet.Cells[0, 1].SetValue(DateTime.Now);
worksheet.IgnoredErrors.Add("A:A", IgnoredErrorTypes.NumberStoredAsText);
excel.Save(@"c:\temp\gemboxExcelTest.xlsx", SaveOptions.XlsxDefault);
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Private Fonts");
// Current directory contains a font file.
FontSettings.FontsBaseDirectory = ".";
worksheet.Parent.Styles.Normal.Font.Name = "Almonte Snow";
worksheet.Cells[0, 0].Value = "Hello World!";
workbook.Save("Private Fonts.pdf");
}
public string XlsxReadToSheets(string path)
{
try
{
string xlsxFile = HttpContext.Current.Server.MapPath(path);
string htmlFile = HttpContext.Current.Server.MapPath(path).Replace(".xlsx", ".html");
SpreadsheetInfo.SetLicense("ETZX-IT28-33Q6-1HA2");
ExcelFile ef = ExcelFile.Load(xlsxFile);
ef.Save(htmlFile);
return(File.ReadAllText(htmlFile));
}
catch
{
return("");
}
}
private void mtdExportarExcel(string ruta)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile archivoExcel = new ExcelFile();
ExcelWorksheet ws = archivoExcel.Worksheets.Add("Reporte");
ws.InsertDataTable(resultado,//toma los datos a exportar
new InsertDataTableOptions()
{
ColumnHeaders = true,
StartRow = 0
});
archivoExcel.Save(ruta);
}
public static IEnumerable <City> ReadExcel(string filePath)
{
if (string.IsNullOrEmpty(filePath))
{
throw new Exception("The file path given was empty.");
}
if (!File.Exists(filePath))
{
throw new Exception("The file do not exists.");
}
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load(filePath);
var worksheet = workbook.Worksheets["Sheet1"];
var result = new List <City>();
for (var rowIdx = 0; rowIdx <= worksheet.Rows.Count; rowIdx++)
{
//city name always on first column (column A)
var cityNameCell = worksheet.Cells[rowIdx, 0];
if (cityNameCell.ValueType != CellValueType.Null)
{
var city = new City();
city.Name = cityNameCell.Value.ToString();
var cityItems = new List <CityItem>();
for (var colIdx = 0; colIdx <= worksheet.Columns.Count; colIdx++)
{
var cityItemName = worksheet.Cells[CITY_ITEM_NAMES_ROW, colIdx];
if (cityItemName.ValueType != CellValueType.Null)
{
cityItems.Add(new CityItem()
{
Name = cityItemName.Value.ToString(),
Distance = Convert.ToInt32(worksheet.Cells[rowIdx, colIdx].Value)
});
}
}
city.CityItems = cityItems;
result.Add(city);
}
}
return(result);
}
private void ExportBtn_Click(object sender, EventArgs e)
{
switch (comboBoxDataType.SelectedIndex)
{
case 0: break;
case 1:
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Sheet");
ws.Cells[0, 0].Value = "";
// Insert DataTable into an Excel worksheet.
ws.InsertDataTable(dt,
new InsertDataTableOptions()
{
ColumnHeaders = true,
StartRow = 2
});
ef.Save(@"D:\Sheet.xlsx");
MessageBox.Show("file saved successfully at 'D:\\Sheet.xlsx'");
break;
case 2:
StringBuilder sb = new StringBuilder();
string[] columnNames = dt.Columns.Cast <DataColumn>().
Select(column => column.ColumnName).
ToArray();
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dt.Rows)
{
string[] fields = row.ItemArray.Select(field => field.ToString()).
ToArray();
sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText("D:\\datatable.csv", sb.ToString());
MessageBox.Show("file saved successfully at 'D:\\datatable.csv'");
break;
default: break;
}
}
protected void ExcelBtn_Click(object sender, EventArgs e)
{
DataTable dt = DBHelper.GetDataTable("select * from CRM_Customer", null);
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile xlsx = new ExcelFile();
ExcelWorksheet mySheet = xlsx.Worksheets.Add("Customers");
mySheet.Cells[1, 2].Value = "統編";
mySheet.Cells[1, 2].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 3].Value = "公司名稱";
mySheet.Cells[1, 3].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 4].Value = "公司地址";
mySheet.Cells[1, 4].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 5].Value = "公司電話";
mySheet.Cells[1, 5].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 6].Value = "公司官網";
mySheet.Cells[1, 6].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 7].Value = "負責窗口";
mySheet.Cells[1, 7].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 8].Value = "負責窗口電話";
mySheet.Cells[1, 8].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 9].Value = "負責窗口E-mail";
mySheet.Cells[1, 9].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 10].Value = "公司規模";
mySheet.Cells[1, 10].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.Cells[1, 11].Value = "產業類別";
mySheet.Cells[1, 11].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue));
mySheet.InsertDataTable(dt,
new InsertDataTableOptions()
{
StartColumn = 2,
StartRow = 2,
});
xlsx.Save(Server.MapPath(@"~\Output\CRM_CustomersList.xlsx"));
MsgLab.Text = "Excel檔案匯出成功";
Response.AddHeader("Content-Type", "application/octet-stream");
Response.AddHeader("Content-Transfer-Encoding", "Binary");
Response.AddHeader("Content-disposition", "attachment; filename=\"CRM_CustomersList.xlsx\"");
Response.WriteFile(
HttpRuntime.AppDomainAppPath + @"Output\CRM_CustomersList.xlsx");
Response.End();
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
DataTable dataTable = new DataTable();
// Depending on the format of the input file, you need to change this:
dataTable.Columns.Add("FirstColumn", typeof(string));
dataTable.Columns.Add("SecondColumn", typeof(string));
// Select the first worksheet from the file.
ExcelWorksheet ws = ef.Worksheets[0];
ExtractToDataTableOptions options = new ExtractToDataTableOptions(0, 0, 10);
options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow;
options.ExcelCellToDataTableCellConverting += (sender, e) =>
{
if (!e.IsDataTableValueValid)
{
// GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues;
// someone would expect the number 12.4 as "12.4" and someone else as "12,4".
e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString();
e.Action = ExtractDataEventAction.Continue;
}
};
// Extract the data from an Excel worksheet to the DataTable.
// Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
ws.ExtractToDataTable(dataTable, options);
// Write DataTable content
StringBuilder sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dataTable.Rows)
{
sb.AppendFormat("{0} {1}", row[0], row[1]);
sb.AppendLine();
}
Console.WriteLine(sb.ToString());
}
static void Main()
{
// Set license key to use GemBox.Spreadsheet in Free mode.
SpreadsheetInfo.SetLicense("SN-2020Apr27-MV0CW7jiIJlsiZzwfdRaklP7MfRc6QM05dKX3Hzjam7BONU/IFJNXBhExD2+nu6wQaJLlen0h1/29EuBwfDriVZY4yQ==A");
// Load Excel workbook from file's path.
ExcelFile book = ExcelFile.Load("D:/Apps and files/Для Макса/Для Макса.xlsx");
for (int sheetIndex = 0; sheetIndex < book.Worksheets.Count; sheetIndex++)
{
// Get Excel worksheet using zero-based index.
ExcelWorksheet worksheet = book.Worksheets[sheetIndex];
for (int rowIndex = 1; rowIndex < worksheet.Rows.Count; rowIndex++)
{
// Get Excel row using zero-based index.
ExcelRow row = worksheet.Rows[rowIndex];
string start = $"{row.Cells[0].Value}";
ExcelFile workbook = new ExcelFile();
if (File.Exists("D:/Apps and files/Для Макса/Сравнительная группа/" + start + ".xlsx"))
{
workbook = ExcelFile.Load("D:/Apps and files/Для Макса/Сравнительная группа/" + start + ".xlsx");
}
else
{
workbook.Worksheets.Add("Общие данные");
}
int length = workbook.Worksheets["Общие данные"].Rows.Count;
int len1 = -1;
bool doo = true;
while (doo)
{
len1 += 1;
if (workbook.Worksheets["Общие данные"].Rows[len1].Cells[0].Value == null)
{
doo = false;
}
}
for (int columnIndex = 0; columnIndex < 26; columnIndex++)
{
ExcelCell cell = row.Cells[columnIndex];
workbook.Worksheets["Общие данные"].Rows[len1].Cells[columnIndex].Value = cell.Value;
}
workbook.Save("D:/Apps and files/Для Макса/Сравнительная группа/" + start + ".xlsx");
}
}
}
public async Task <IActionResult> ExportByDate(CountByProductDateViewModel model)
{
model.Products = await GetProductCountsByDateAsync(model.Dau, model.Cuoi);
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var options = SaveOptions.XlsxDefault;
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");
worksheet.Cells[0, 0].Value = "Các sản phẩm đã bán từ " + DateToString(model.Dau) +
" đến " + DateToString(model.Cuoi);
worksheet.Cells[0, 0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Cells.GetSubrangeAbsolute(0, 0, 0, 3).Merged = true;
worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
worksheet.Rows[1].Style.Font.Weight = ExcelFont.BoldWeight;
worksheet.Columns[2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[1].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
worksheet.Columns[3].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
worksheet.Columns[0].SetWidth(500, LengthUnit.Pixel);
worksheet.Columns[1].SetWidth(150, LengthUnit.Pixel);
worksheet.Columns[2].SetWidth(80, LengthUnit.Pixel);
worksheet.Columns[3].SetWidth(150, LengthUnit.Pixel);
worksheet.Cells["A2"].Value = "Tên sản phẩm";
worksheet.Cells["B2"].Value = "Giá";
worksheet.Cells["C2"].Value = "Số lượng";
worksheet.Cells["D2"].Value = "Tạm tính";
var l = model.Products.Count;
for (int r = 1; r < l; r++)
{
var product = model.Products[r - 1];
worksheet.Cells[r + 1, 0].Value = product.Ten;
worksheet.Cells[r + 1, 1].Value = String.Format("{0:### ### ### ### VND}", product.Gia);
worksheet.Cells[r + 1, 2].Value = product.SoLuong;
worksheet.Cells[r + 1, 3].Value = String.Format("{0:### ### ### ### VND}", product.LayTamTinh());
}
worksheet.Cells[l + 2, 2].Value = "Tổng tiền:";
worksheet.Cells[l + 2, 3].Value = String.Format("{0:### ### ### ### VND}", model.LayTongTien());
return(File(GetBytes(workbook, options), options.ContentType, "Thống kê từ " +
DateToString(model.Dau) + " đến " + DateToString(model.Cuoi) + ".xlsx"));
}
// load the template to a var
public ExcelFile loadTemplate()
{
try
{
// Sets license to free limited, max 150 rows
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;
// Opens the template
return(new ExcelFile());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
private void ExportData(DataTable dataTable, Label resultLbl, string selectedFolder)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Activities");
string companyName = File.ReadAllText(@"C:\Generating Attendance Reports - Files\companyName.txt", Encoding.UTF8);
worksheet.Cells[0, 0].Value = "The attendance Report in " + companyName + "(- according to your filter selection):";
// Insert DataTable to an Excel worksheet.
worksheet.InsertDataTable(dataTable,
new InsertDataTableOptions()
{
ColumnHeaders = true,
StartRow = 2
});
string fullPath;
if (selectedFolder == null)
{
fullPath = @"C:\Users\User\Downloads\Attendance Report.xlsx";
}
else
{
fullPath = selectedFolder + @"\Attendance Report.xlsx";
}
if (File.Exists(fullPath))
{
if (ReportExporting.FileInUse(fullPath))
{
resultLbl.Text = "Please close the file.";
return;
}
}
resultLbl.Text = "true";
workbook.Save(fullPath);
resultLbl.Text = remark + "R=" + dataTable.Rows.Count.ToString() + "__C=" + dataTable.Columns.Count.ToString();
}
private void Form1_Load(object sender, EventArgs e)
{
string[] hours = new string[] { "0", "1", "2", "3", "4", "5", "6", "7", "8" };
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ef = new ExcelFile();
worksheet = ef.Worksheets.Add("Tables");
monthCalendar1.MaxSelectionCount = 1;
comboBox1.Items.AddRange(hours);
comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox1.SelectedItem = "0";
comboBox2.Items.AddRange(hours);
comboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox2.SelectedItem = "0";
comboBox3.Items.AddRange(hours);
comboBox3.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox3.SelectedItem = "0";
worksheet.Cells[0, 0].Value = "Data";
worksheet.Cells[0, 1].Value = "Ora incepere";
worksheet.Cells[0, 2].Value = "Ora sfarsit";
worksheet.Cells[0, 3].Value = "Curs alocat";
worksheet.Cells[0, 4].Value = "Pregatire alocat";
worksheet.Cells[0, 5].Value = "Recuperare alocat";
worksheet.Cells[0, 6].Value = "Ora total";
worksheet.Columns[0].SetWidth(90, LengthUnit.Pixel);
worksheet.Columns[1].SetWidth(110, LengthUnit.Pixel);
worksheet.Columns[2].SetWidth(100, LengthUnit.Pixel);
worksheet.Columns[3].SetWidth(100, LengthUnit.Pixel);
worksheet.Columns[4].SetWidth(120, LengthUnit.Pixel);
worksheet.Columns[5].SetWidth(140, LengthUnit.Pixel);
worksheet.Columns[6].SetWidth(90, LengthUnit.Pixel);
worksheet.Cells[60, 5].Value = "TOTAL:";
save_button.Enabled = false;
get_hours_normal.Enabled = false;
get_hours_custom.Enabled = false;
panel1.Hide();
elements = new List <WorkStuff>();
init = true;
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Private Fonts");
string pathToResources = "Resources";
FontSettings.FontsBaseDirectory = pathToResources;
ws.Parent.Styles.Normal.Font.Name = "Almonte Snow";
ws.Cells[0, 0].Value = "Hello World!";
ef.Save("Private Fonts.pdf");
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("TemplateUse.xlsx");
// Add Sheet
ExcelWorksheet ws = ef.Worksheets.ActiveWorksheet = ef.Worksheets.InsertEmpty(0, "Document Properties");
int rowIndex = 0;
// Read Built-in Document Properies
ws.Cells[rowIndex++, 0].Value = "Built-in document properties";
ws.Cells[rowIndex, 0].Value = "Property";
ws.Cells[rowIndex++, 1].Value = "Value";
foreach (KeyValuePair <BuiltInDocumentProperties, string> keyValue in ef.DocumentProperties.BuiltIn)
{
ws.Cells[rowIndex, 0].Value = keyValue.Key.ToString();
ws.Cells[rowIndex++, 1].Value = keyValue.Value;
}
// Read Custom Document Properties
ws.Cells[++rowIndex, 0].Value = "Custom Document Properties";
ws.Cells[++rowIndex, 0].Value = "Property";
ws.Cells[rowIndex++, 1].Value = "Value";
foreach (KeyValuePair <string, object> keyValue in ef.DocumentProperties.Custom)
{
ws.Cells[rowIndex, 0].Value = keyValue.Key;
ws.Cells[rowIndex++, 1].Value = keyValue.Value.ToString();
}
// Write/Modifiy Document Properties
ef.DocumentProperties.BuiltIn[BuiltInDocumentProperties.Author] = "John Doe";
ef.DocumentProperties.BuiltIn[BuiltInDocumentProperties.Title] = "Genrated title";
ws.Columns[0].AutoFit();
ws.Columns[1].AutoFit();
ef.Save("Document Properties.xlsx");
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("TemplateUse.xlsx");
// Add Sheet.
var worksheet = workbook.Worksheets.ActiveWorksheet = workbook.Worksheets.InsertEmpty(0, "Document Properties");
int rowIndex = 0;
// Read Built-in Document Properties.
worksheet.Cells[rowIndex++, 0].Value = "Built-in document properties";
worksheet.Cells[rowIndex, 0].Value = "Property";
worksheet.Cells[rowIndex++, 1].Value = "Value";
foreach (var keyValue in workbook.DocumentProperties.BuiltIn)
{
worksheet.Cells[rowIndex, 0].Value = keyValue.Key.ToString();
worksheet.Cells[rowIndex++, 1].Value = keyValue.Value;
}
// Read Custom Document Properties
worksheet.Cells[++rowIndex, 0].Value = "Custom Document Properties";
worksheet.Cells[++rowIndex, 0].Value = "Property";
worksheet.Cells[rowIndex++, 1].Value = "Value";
foreach (var keyValue in workbook.DocumentProperties.Custom)
{
worksheet.Cells[rowIndex, 0].Value = keyValue.Key;
worksheet.Cells[rowIndex++, 1].Value = keyValue.Value.ToString();
}
// Write/Modify Document Properties.
workbook.DocumentProperties.BuiltIn[BuiltInDocumentProperties.Author] = "John Doe";
workbook.DocumentProperties.BuiltIn[BuiltInDocumentProperties.Title] = "Generated title";
worksheet.Columns[0].SetWidth(192, LengthUnit.Pixel);
worksheet.Columns[1].SetWidth(217, LengthUnit.Pixel);
workbook.Save("Document Properties.xlsx");
}
public TeamDataParser(String s)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
teamDataReader = new FileInfo(s);
teams = new List <Team>();
sr = new StreamReader(teamDataReader.FullName, System.Text.Encoding.Default);
String line;
while ((line = sr.ReadLine()) != null)
{
if (!line.Contains("//"))
{
String[] data = line.Split(' ');
decimal y;
if (Decimal.TryParse(data[0], out y)) // If the first character is a decimal...
{
int pl = (int)Decimal.Parse(data[0]);
List <Employee> me = new List <Employee>();
Employee m = new Employee();
for (int i = 1; i <= data.Length; i++)
{
if (Decimal.TryParse(data[i], out y))
{
m.SetPoints((int)Decimal.Parse(data[i]));
me.Add(m);
m = new Employee();
}
else
{
m.SetName(data[i]);
}
}
teams.Add(new Team(me, pl));
}
}
}
teams.Sort();
int z = 1;
foreach (Team t in teams)
{
t.Place = z;
z++;
}
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var inputPassword = "******";
var outputPassword = "******";
var workbook = ExcelFile.Load("XlsxEncryption.xlsx", new XlsxLoadOptions()
{
Password = inputPassword
});
workbook.Save("XLSX Encryption.xlsx", new XlsxSaveOptions()
{
Password = outputPassword
});
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Workbook Protection");
// ProtectionSettings class is supported only for XLSX file format.
var protectionSettings = workbook.ProtectionSettings;
protectionSettings.ProtectStructure = true;
worksheet.Cells[0, 0].Value = "Workbook password is 123 (only supported for XLSX file format).";
protectionSettings.SetPassword("123");
workbook.Save("Workbook Protection.xlsx");
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("SimpleTemplate.xlsx");
var options = new PdfSaveOptions()
{
DigitalSignature =
{
CertificatePath = "GemBoxExampleExplorer.pfx",
CertificatePassword = "******"
}
};
workbook.Save("PDF Digital Signature.pdf", options);
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("RightToLeft.xlsx");
var worksheet = workbook.Worksheets[0];
// Show columns from the right side of the page.
worksheet.ViewOptions.ShowColumnsFromRightToLeft = true;
worksheet.Cells["A8"].Value = "200 جديدة";
// Set the reading order of the cell as right-to-left.
worksheet.Cells["A8"].Style.TextDirection = TextDirection.RightToLeft;
workbook.Save("RightToLeft.pdf");
}
public static void DtExport(System.Data.DataTable dt, string path)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("output");
// Insert DataTable to an Excel worksheet.
worksheet.InsertDataTable(dt,
new InsertDataTableOptions()
{
ColumnHeaders = true,
StartRow = 0
});
workbook.Save(path);
}
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("SimpleTemplate.xlsx");
var worksheet = workbook.Worksheets[0];
int columnCount = worksheet.CalculateMaxUsedColumns();
for (int i = 0; i < columnCount; i++)
{
worksheet.Columns[i].AutoFit(1, worksheet.Rows[1], worksheet.Rows[worksheet.Rows.Count - 1]);
}
workbook.Save("Row_Column AutoFit.xlsx");
}
static void Main(string[] args)
{
Data data = new Data();
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
Console.WriteLine("Welcome To the Reporter...\n\n");
string[] yearMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
getData(ref data, ref yearMonths);
//= ======================================================================================================== =
ExcelFile report = new ExcelFile();
ExcelWorksheet sheet = report.Worksheets.Add("Yearly Report");
zabatellogo(ref sheet, data.compName);
zabatHeader(ref sheet, ref data, yearMonths);
dataEntry(ref sheet, ref data, ref yearMonths);
ExcelWorksheet chartsh = report.Worksheets.Add(SheetType.Chart, "Chart");
ExcelChart chart = chartsh.Charts.Add(ChartType.Pie, 0, 0, 0, 0, LengthUnit.Centimeter);
chart.DataLabels.LabelPosition = DataLabelPosition.InsideEnd;
// chart data.
// period +1 for header.
chart.SelectData(sheet.Cells.GetSubrangeRelative(7, 1, 2, data._period + 1), true);
report.Save(Globals.savePath + data.compName + ".xlsx");
Console.Write("Succesfully Created... Do you want to make a pdf copy?");
char pdf = char.Parse(Console.ReadLine());
if (char.ToLower(pdf) == 'y')
{
sheet.PrintOptions.Portrait = true;
sheet.PrintOptions.PaperType = PaperType.A4;
report.Save(Globals.savePath + data.compName + ".pdf", new PdfSaveOptions()
{
SelectionType = SelectionType.EntireFile
});
}
System.Diagnostics.Process.Start("explorer.exe", Globals.savePath);
}
public static void plotGraph(List <String> coordinateX, List <String> coordinateY)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Chart");
// Add data which will be used by the Excel chart.
worksheet.Cells["A1"].Value = "X";
for (var i = 0; i < coordinateX.Count; i++)
{
worksheet.Cells["A2"].Value = int.Parse(coordinateX[0]);
worksheet.Cells["A3"].Value = int.Parse(coordinateX[1]);
worksheet.Cells["A4"].Value = int.Parse(coordinateX[2]);
worksheet.Cells["A5"].Value = int.Parse(coordinateX[3]);
}
worksheet.Cells["B1"].Value = "Y";
for (var i = 0; i < coordinateY.Count; i++)
{
worksheet.Cells["B2"].Value = int.Parse(coordinateY[0]);
worksheet.Cells["B3"].Value = int.Parse(coordinateY[1]);
worksheet.Cells["B4"].Value = int.Parse(coordinateY[2]);
worksheet.Cells["B5"].Value = int.Parse(coordinateY[3]);
}
// Set header row and formatting.
worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
worksheet.Columns[0].Width = (int)LengthUnitConverter.Convert(3, LengthUnit.Centimeter, LengthUnit.ZeroCharacterWidth256thPart);
//worksheet.Columns[1].Style.NumberFormat = "\"$\"#,##0";
// Make entire sheet print on a single page.
worksheet.PrintOptions.FitWorksheetWidthToPages = 1;
worksheet.PrintOptions.FitWorksheetHeightToPages = 1;
// Create Excel chart and select data for it.
var chart = worksheet.Charts.Add(ChartType.Line, "D2", "M25");
chart.SelectData(worksheet.Cells.GetSubrangeAbsolute(0, 0, 4, 1), true);
workbook.Save("Chart.xlsx");
}
