static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("1");
worksheet.Cells["A2"].PutValue("2");
worksheet.Cells["B1"].PutValue(11);
//Hiding the 1st row of the worksheet
worksheet.Cells.HideRow(0);
//Hiding the 1st column of the worksheet
worksheet.Cells.HideColumn(0);
//Saving the modified Excel file
workbook.Save("Output-HideRowsandColumns.xls");
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Auto-fitting the 1st column of the worksheet
//Adding a string value to the cell
worksheet.Cells["A1"].PutValue("This is a test input");
//Adding a double value to the cell
worksheet.Cells["B1"].PutValue(20.5);
//Adding an integer value to the cell
worksheet.Cells["C1"].PutValue(15);
//Adding a boolean value to the cell
worksheet.Cells["D1"].PutValue(true);
worksheet.AutoFitColumn(0);
worksheet.AutoFitColumn(1);
worksheet.AutoFitColumn(2);
worksheet.AutoFitColumn(3);
//Saving the modified Excel file in bin/debug folder
workbook.Save("AutoFiltRowsandColumns.xls");
//Closing the file stream to free all resources
}
/// <summary>
/// 注册
/// </summary>
static Test()
{
Aspose.Cells.License li = new Aspose.Cells.License();
string path = @"C:\Users\zr644\Desktop\Test" + "\\" + @"Aspose.Cells.lic";
li.SetLicense(path);
}
static void Main(string[] args)
{
Console.Clear();
Console.BackgroundColor = ConsoleColor.Black;
Console.ForegroundColor = ConsoleColor.Green;
//Console.WriteLine("开始读取文件夹中的文件...........");
Console.WriteLine("Begin reading files from the folder...........");
//注册序列号
string license = "Aspose.Total.NET.lic";
Aspose.Words.License license_word = new Aspose.Words.License();
Aspose.Cells.License license_cells = new Aspose.Cells.License();
license_word.SetLicense(license);
license_cells.SetLicense(license);
Import();
Clear();
TaskCase1();
TaskCase2();
TaskCase3();
TaskCase4();
//Console.WriteLine("程序运行结束...........");
Console.WriteLine("End of program...........");
Console.ReadKey();
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Auto-fitting the 1st column of the worksheet
//Adding a string value to the cell
worksheet.Cells["A1"].PutValue("This is a test input");
//Adding a double value to the cell
worksheet.Cells["B1"].PutValue(20.5);
//Adding an integer value to the cell
worksheet.Cells["C1"].PutValue(15);
//Adding a boolean value to the cell
worksheet.Cells["D1"].PutValue(true);
worksheet.AutoFitColumn(0);
worksheet.AutoFitColumn(1);
worksheet.AutoFitColumn(2);
worksheet.AutoFitColumn(3);
//Saving the modified Excel file in bin/debug folder
workbook.Save("AutoFiltRowsandColumns.xls");
//Closing the file stream to free all resources
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("1");
worksheet.Cells["A2"].PutValue("2");
worksheet.Cells["B1"].PutValue(11);
//Hiding the 1st row of the worksheet
worksheet.Cells.HideRow(0);
//Hiding the 1st column of the worksheet
worksheet.Cells.HideColumn(0);
//Saving the modified Excel file
workbook.Save("Output-HideRowsandColumns.xls");
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Applying freeze panes settings The FreezePanes method takes the following parameters
//Row, the row index of the cell that the freeze will start from.
//Column, the column index of the cell that the freeze will start from.
//Frozen rows, the number of visible rows in the top pane.
//Frozen columns, the number of visible columns in the left pane
worksheet.FreezePanes(2, 2, 2, 0);
workbook.Save("output-FreezeFile-Aspose.Cells.xls");
}
static PDFCevirici()
{
if (AsposeExcelLisans != null)
return;
//LisansExcelAdres = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data"),
// "\\Aspose.Cells.lic");
LisansExcelAdres = HttpContext.Current.Server.MapPath("~/App_Data/Aspose.Cells.lic");
if (!File.Exists(LisansExcelAdres))
throw new FileNotFoundException("Lisans dosyası bulunamadı! Aranan Dosya : " + LisansExcelAdres);
AsposeExcelLisans = new Aspose.Cells.License();
AsposeExcelLisans.SetLicense(LisansExcelAdres);
if (AsposeWordLisans != null)
return;
/*
LisansWordAdres = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data"),
"\\Aspose.Words.lic");
*/
LisansWordAdres = HttpContext.Current.Server.MapPath("~/App_Data/Aspose.Words.lic");
AsposeWordLisans = new Aspose.Words.License();
AsposeWordLisans.SetLicense(LisansWordAdres);
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Applying freeze panes settings The FreezePanes method takes the following parameters
//Row, the row index of the cell that the freeze will start from.
//Column, the column index of the cell that the freeze will start from.
//Frozen rows, the number of visible rows in the top pane.
//Frozen columns, the number of visible columns in the left pane
worksheet.FreezePanes(2, 2, 2, 0);
workbook.Save("output-FreezeFile-Aspose.Cells.xls");
}
//string filepath = @"C:\Users\han\Desktop\报表\ExcelToday.xlsx";
private CreateExcelTest()
{
//导入破解证书
try
{
Excel.License el = new Excel.License();
el.SetLicense("Aid/License.lic");
}
catch (Exception)
{
//...
}
workBook_excel = File.Exists(filepath) ? new Workbook(filepath) : new Workbook();
workSheet_excel = workBook_excel.Worksheets["Sheet1"];
try
{
Workbook wb = new Workbook(@"..\..\modelFile\表头模板.xlsx");//营盘壕煤矿2101工作面矿压综合分析日报表
Worksheet ws = wb.Worksheets[0];
workSheet_excel.Copy(ws);
workBook_excel.Save(filepath, SaveFormat.Xlsx);
}
catch
{
System.Windows.Forms.MessageBox.Show("文件已打开，请关闭文件后再试");
}
}
static XmlConverter()
{
var c = new Aspose.Cells.License();
c.SetLicense("Aspose.Total.lic");
var w = new Aspose.Words.License();
w.SetLicense("Aspose.Total.lic");
}
static void Main(string[] args)
{
string filePath = @".\Resources\Aspose.Total.lic";
Aspose.Cells.License wordLicense = new Aspose.Cells.License();
FileStream filestream = new FileStream(filePath, FileMode.Open);
wordLicense.SetLicense(filestream);
StartCrawler();
Console.ReadLine();
}
/// <summary>
/// 引入破解证书
/// </summary>
public ExcelGenerator()
{
try
{
Excel.License l = new Excel.License();
l.SetLicense("Aid/License.lic");
}
catch (Exception)
{
}
}
public ExcelEngine(string AsposePath)
{
//注册Excel组件 ，这是为了给的服务用的构造函数
if (_license == null)
{
string licenseFile = Assembly.GetExecutingAssembly().Location.Substring(0, Assembly.GetExecutingAssembly().Location.LastIndexOf('\\')) + ConfigurationManager.AppSettings["AsposePath"];
_license = new License();
_license.SetLicense(licenseFile);
}
}
/// <summary>
/// 缺省构造方法
/// </summary>
public ExcelHelper()
{
//注册Excel组件
if (_license == null)
{
string licenseFile = HttpContext.Current.Server.MapPath("/Aspose.Total.lic");
_license = new License();
_license.SetLicense(licenseFile);
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
// Load image help
picHelp.ImageLocation = @"Icons\help.png";
// Regristry aspose.cell
Aspose.Cells.License ACL = new Aspose.Cells.License();
ACL.SetLicense(LicenseAspose());
// Load image radio button
rbView.Image = Image.FromFile(@"Icons\grid.png");
rbView.Text = "";
rbView.Checked = true;
rbExcel.Image = Image.FromFile(@"Icons\excel.png");
rbExcel.Text = "";
rbExcel.Checked = false;
// Load connection string & log directory
string[] lines = System.IO.File.ReadAllLines("Config.ini");
var machinename = string.Empty;
if (lines.Length >= 1)
{
txtDirLogSQL.Text = lines[0];
}
if (lines.Length >= 2)
{
txtConnection.Text = clsCryptography.Decrypt(lines[1]);
}
if (lines.Length >= 3)
{
machinename = lines[2];
}
// Load info screen
if (txtConnection.Text != "" && ValidateConnectionSQL())
{
if (machinename.ToUpper() == System.Environment.MachineName)
{
btnRefresh_Click(null, null);
}
}
// Load report list
lbReport.DataSource = GetListReportByLoginActive(1);
}
catch (Exception ex)
{
Log.ErrorFormat("Exception: {0}", ex.Message);
MessageBox.Show(ex.Message, "EXCEPTION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
}
static void SetLicense()
{
string licPath = @"Aspose.Cells.lic";
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
Console.WriteLine(CellsHelper.GetVersion());
System.Diagnostics.Debug.WriteLine(CellsHelper.GetVersion());
Environment.CurrentDirectory = dirPath;
}
static void SetLicense()
{
string licPath = @"Aspose.Cells.lic";
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
Console.WriteLine(CellsHelper.GetVersion());
System.Diagnostics.Debug.WriteLine(CellsHelper.GetVersion());
Environment.CurrentDirectory = dirPath;
}
public string invoice_download_excel(string DateFrom, string DateTo)
{
string rootPath = Directory.GetParent(HttpContext.Current.Server.MapPath("")).FullName;
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + "";
string to_file = Path.Combine(rootPath, "files", "Excel_DailyTrips", "inv_" + fileName + ".csv");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@DateFrom", DateFrom, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@DateTo", DateTo, SqlDbType.NVarChar, 10));
string sql = string.Format(@"select dense_rank()over(order by det.DocNo)-1 as rowId,
convert(nvarchar,inv.DocDate,103) as DocDate,inv.DocType,inv.DocNo,inv.PartyTo,inv.CurrencyId,det.GstType,det.GstAmt,0 as zero,det.DocAmt,det.DocAmt+det.GstAmt as Total,det.ChgDes1,det.AcCode
from XAArInvoiceDet as det
left outer join XAArInvoice as inv on det.DocNo=inv.DocNo
where IsNull(inv.cancelind,'N')='N' and inv.DocDate>=@DateFrom and inv.DocDate<@DateTo");
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string DataList = Common.DataTableToJson(dt);
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(HttpContext.Current.Server.MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
//wb.Open(file);
Worksheet ws = wb.Worksheets[0];
int baseRow = 0;
int i = 0;
for (; i < dt.Rows.Count;)
{
ws.Cells[baseRow + i, 0].PutValue(dt.Rows[i]["rowId"]);
ws.Cells[baseRow + i, 1].PutValue(dt.Rows[i]["DocDate"]);
ws.Cells[baseRow + i, 2].PutValue(invoice_download_excel_DocType(dt.Rows[i]["DocType"].ToString()));
ws.Cells[baseRow + i, 3].PutValue(dt.Rows[i]["DocNo"]);
ws.Cells[baseRow + i, 4].PutValue(dt.Rows[i]["PartyTo"]);
ws.Cells[baseRow + i, 5].PutValue(dt.Rows[i]["CurrencyId"]);
ws.Cells[baseRow + i, 6].PutValue(invoice_download_excel_GSTTYpe(dt.Rows[i]["GstType"].ToString()));
ws.Cells[baseRow + i, 7].PutValue(dt.Rows[i]["GstAmt"]);
ws.Cells[baseRow + i, 8].PutValue(dt.Rows[i]["zero"]);
ws.Cells[baseRow + i, 9].PutValue(dt.Rows[i]["DocAmt"]);
ws.Cells[baseRow + i, 10].PutValue(dt.Rows[i]["Total"]);
ws.Cells[baseRow + i, 11].PutValue(dt.Rows[i]["ChgDes1"]);
ws.Cells[baseRow + i, 12].PutValue(dt.Rows[i]["AcCode"]);
i++;
}
wb.Save(to_file);
//string context = Common.StringToJson(Path.Combine("files", "Excel_DailyTrips", "inv_" + fileName + ".csv"));
//Common.WriteJson(true, context);
string context = "../files/Excel_DailyTrips/inv_" + fileName + ".csv";// Path.Combine("../files", "Excel_DailyTrips", "inv_" + fileName + ".csv");
return(context);
}
static void Main(string[] args)
{
Aspose.Cells.License li = new Aspose.Cells.License();
string path = Application.StartupPath + "\\" + @"Aspose.Cells.lic";
li.SetLicense(path);
//Q1.Run();
//Q13.Run1();
//Q13.Run2();
Q17.Run();
}
public static byte[] Excel(DataSet inputDataSet, ExcelExportFileType exportType)
{
if (inputDataSet != null && inputDataSet.Tables.Count > 0)
{
int dataSheetCounter = 0;
//Applying a license
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
Workbook excelToExport = new Workbook();
excelToExport.Worksheets.Clear();
Worksheet dataSheet = null;
foreach (DataTable inputTable in inputDataSet.Tables)
{
if (inputTable != null && inputTable.Columns.Count > 0)
{
dataSheet = excelToExport.Worksheets.Add(!string.IsNullOrWhiteSpace(inputTable.TableName) ? inputTable.TableName : String.Format("Data Sheet {0}", ++dataSheetCounter));
WriteDataToWorkSheet(ref dataSheet, inputTable);
ApplyStyleToWorkSheet(ref excelToExport, ref dataSheet, dataSheet.Cells.MaxDataRow + 1, dataSheet.Cells.MaxDataColumn + 1);
if (exportType == ExcelExportFileType.SingleWorkSheet)
{
break;
}
}
}
if (excelToExport.Worksheets.Count > 0)
{
//MemoryStream memoryStream = excelToExport.SaveToStream();
MemoryStream memoryStream = new MemoryStream();
excelToExport.Save(memoryStream, SaveFormat.Xlsx);
if (memoryStream != null && memoryStream.Length > 0)
{
memoryStream.Position = 0;
byte[] bytesInMemory = memoryStream.ToArray();
memoryStream.Flush();
memoryStream.Close();
return(bytesInMemory);
}
}
}
return(null);
}
static void SetLicense()
{
string licPath = @"Aspose.Cells.lic";
licPath = "F:/Download/Misc/Aspose/Licenses/Aspose.Total.lic";//<<<<<<<You can also give your own license path
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
//Console.WriteLine(CellsHelper.GetVersion());
//System.Diagnostics.Debug.WriteLine(CellsHelper.GetVersion());
//Environment.CurrentDirectory = dirPath;
}
public static void Crack()
{
try
{
//XlsExport.InitializeAsposeCells();
Aspose.Cells.License l = new Aspose.Cells.License();
//l.SetLicense(Path.Combine(HttpRuntime.AppDomainAppPath, "lib/Aid/License.lic"));
l.SetLicense(Path.Combine(PFDataHelper.BaseDirectory, "lib/Aid/License.lic"));
}
catch (Exception e)
{
PFDataHelper.WriteError(e);
}
}
private CreateExcelTest()
{
//导入破解证书
try
{
Excel.License el = new Excel.License();
el.SetLicense("Aid/License.lic");
}
catch (Exception)
{
//...
}
workBook_excel = File.Exists(excelFilePath) ? new Workbook(excelFilePath) : new Workbook();
workSheet_excel = workBook_excel.Worksheets[0];
workBook_excel.Save(excelFilePath, SaveFormat.Xlsx);
}
public void InitializeAsposeLicensing()
{
var stopwatch = MegaExcel.StartStopwatch();
try
{
var excellicense = new Cells.License();
var stream = new MemoryStream(Resources.Resources.Aspose_Total);
excellicense.SetLicense(stream);
this.TraceSource.TraceEvent(TraceEventType.Information, "COMPLETE: Aspose Excel Licensed", stopwatch.Elapsed, TraceStatus.Success);
}
catch (Exception ex)
{
this.TraceSource.TraceException(ex, TraceEventType.Error, "FAILED: Could not get License for Aspose Excel.", stopwatch.Elapsed, TraceStatus.Failed);
}
}
protected static void CheckLicense(LicenseProvider provider)
{
try
{
switch (provider)
{
case LicenseProvider.Cells:
var license1 = new Aspose.Cells.License();
license1.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Diagram:
var license2 = new Aspose.Diagram.License();
license2.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Pdf:
var license3 = new Aspose.Pdf.License();
license3.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Slides:
var license4 = new Aspose.Slides.License();
license4.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Words:
var license5 = new Aspose.Words.License();
license5.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Tasks:
var license6 = new Aspose.Tasks.License();
license6.SetLicense(LICENSEPATH);
break;
case LicenseProvider.Imaging:
var license7 = new Aspose.Imaging.License();
license7.SetLicense(LICENSEPATH);
break;
}
}
catch (Exception ex)
{
WriteLicenseException(ex);
}
}
public string invoice_download_excel(string name, string DateFrom, string DateTo)
{
string rootPath = Directory.GetParent(HttpContext.Current.Server.MapPath("")).Parent.Parent.FullName;
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond + "";
string to_file = Path.Combine(rootPath, "files", "Excel_DailyTrips", "cpf_" + fileName + ".csv");
string where = "";
if (name.Length > 0)
{
where = String.Format("Person='{0}'", name);
}
where = GetWhere(where, string.Format("ToDate >= '{0}' and FromDate <= '{1}'", DateFrom, DateTo));
string sql = string.Format(@"select p.Id,p.Name,p.IcNo
from Hr_Payroll as pl
left outer join Hr_Person as p on pl.Person=p.Id
where " + where);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
string DataList = Common.DataTableToJson(dt);
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(HttpContext.Current.Server.MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
//wb.Open(file);
Worksheet ws = wb.Worksheets[0];
ws.Cells[0, 0].PutValue("#");
ws.Cells[0, 1].PutValue("Name");
ws.Cells[0, 2].PutValue("IcNo");
int baseRow = 1;
int i = 0;
for (; i < dt.Rows.Count;)
{
ws.Cells[baseRow + i, 0].PutValue(i + 1);
ws.Cells[baseRow + i, 1].PutValue(dt.Rows[i]["Name"]);
ws.Cells[baseRow + i, 2].PutValue(dt.Rows[i]["IcNo"]);
i++;
}
wb.Save(to_file);
string context = "../../../files/Excel_DailyTrips/cpf_" + fileName + ".csv";
return(context);
}
public void GenerateReport(DataTable reportData, ReportFormat reportFormat, ReportType reportType)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(LicensePath);
string XLFileName = string.Concat(ReportName, DateTime.Now);
_workbook = new Workbook();
_workbook.Worksheets.Clear();
_workbook.Worksheets.Add(this.ReportName);
ReportDefinition reportDefinition = ReportDefinitionParser.ParseXmlToObject(this.ReportName, reportType);
SetReportColumnHeader(reportData, reportDefinition);
Cells cells = _workbook.Worksheets[0].Cells;
SetStyleByType(cells, reportDefinition, StyleType.Header);
if (reportDefinition.ReportData.ShowDescription)
{
SetStyleByType(cells, reportDefinition, StyleType.Description);
}
if (reportDefinition.ReportData.ShowHeader && reportDefinition.ReportData.ShowDescription)
{
startCell = string.Concat("A", reportDefinition.ReportStyle.Description.RowPosition.End + 2);
startRow = Convert.ToString(reportDefinition.ReportStyle.Description.RowPosition.End + 1);
_workbook.Worksheets[0].Cells.ImportDataTable(reportData, true, startCell);
}
else
{
startCell = string.Concat("A", reportDefinition.ReportStyle.Header.RowPosition.End + 2);
startRow = Convert.ToString(reportDefinition.ReportStyle.Header.RowPosition.End + 1);
_workbook.Worksheets[0].Cells.ImportDataTable(reportData, true, startCell);
}
// _workbook.Worksheets[0].Cells.ImportDataTable(reportData, true, "A5");
_workbook.Worksheets[0].AutoFitColumns();
SetworkBookReportStyle(_workbook);
string fileExtension = Convert.ToString(reportFormat).GetEnumAttributeFromValue <ReportFormat>(EnumAttributeTypes.Description);
XLFileName = string.Format("{0}.{1}", XLFileName, fileExtension);
_workbook.Save(System.Web.HttpContext.Current.Response, XLFileName, ContentDisposition.Attachment, new OoxmlSaveOptions((SaveFormat)reportFormat));
}
/// <summary>
/// 缺省构造方法
/// </summary>
public ExcelEngine()
{
//注册Excel组件
if (_license == null)
{
string licenseFile = "";
if (HttpContext.Current == null)
{
licenseFile = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Aspose.Total.lic";
}
else
{
licenseFile = HttpContext.Current.Server.MapPath("~/Aspose.Total.lic");
}
_license = new License();
_license.SetLicense(licenseFile);
}
}
static void Main(string[] args)
{
//Instantiate an instance of license and set the license file
//through its path
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
//Specify the template excel file path.
string myPath = "Book1.xls";
//Instantiate a new Workbook.
//Open the excel file.
Workbook workbook = new Workbook(myPath);
//Declare a Worksheet object.
Worksheet newWorksheet;
//Add 5 new worksheets to the workbook and fill some data
//into the cells.
for (int i = 0; i < 5; i++)
{
//Add a worksheet to the workbook.
newWorksheet = workbook.Worksheets[workbook.Worksheets.Add()];
//Name the sheet.
newWorksheet.Name = "New_Sheet" + (i + 1).ToString();
//Get the Cells collection.
Cells cells = newWorksheet.Cells;
//Input a string value to a cell of the sheet.
cells[i, i].PutValue("New_Sheet" + (i + 1).ToString());
}
//Activate the first worksheet by default.
workbook.Worksheets.ActiveSheetIndex = 0;
//Save As the excel file.
workbook.Save("out_My_Book1.xls");
}
public MainForm()
{
InitializeComponent();
string wordsLicenseFile = Path.Combine(Application.StartupPath, "Aspose.Words.lic");
if (File.Exists(wordsLicenseFile))
{
//This shows how to license Aspose.Words.
//If you don't specify a license, Aspose.Words works in evaluation mode.
Aspose.Words.License license = new Aspose.Words.License();
license.SetLicense(wordsLicenseFile);
}
string cellsLicenseFile = Path.Combine(Application.StartupPath, "Aspose.Cells.lic");
if (File.Exists(cellsLicenseFile))
{
//This shows how to license Aspose.Cells.
//If you don't specify a license, Aspose.Cells works in evaluation mode.
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(cellsLicenseFile);
}
}
static void Main(string[] args)
{
//Instantiate an instance of license and set the license file
//through its path
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
//Specify the template excel file path.
string myPath = "Book1.xls";
//Instantiate a new Workbook.
//Open the excel file.
Workbook workbook = new Workbook(myPath);
//Declare a Worksheet object.
Worksheet newWorksheet;
//Add 5 new worksheets to the workbook and fill some data
//into the cells.
for (int i = 0; i < 5; i++)
{
//Add a worksheet to the workbook.
newWorksheet = workbook.Worksheets[workbook.Worksheets.Add()];
//Name the sheet.
newWorksheet.Name = "New_Sheet" + (i + 1).ToString();
//Get the Cells collection.
Cells cells = newWorksheet.Cells;
//Input a string value to a cell of the sheet.
cells[i, i].PutValue("New_Sheet" + (i + 1).ToString());
}
//Activate the first worksheet by default.
workbook.Worksheets.ActiveSheetIndex = 0;
//Save As the excel file.
workbook.Save("out_My_Book1.xls");
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Setting the name of the newly added worksheet
worksheet.Name = "Adding Comment in Execl Cell";
//Adding a comment to "F5" cell
int commentIndex = worksheet.Comments.Add("F5");
//Adding a string value to the cell
worksheet.Cells["F5"].PutValue("Hello World");
//Accessing the newly added comment
Comment comment = worksheet.Comments[commentIndex];
//Setting the comment note
comment.Note = "Hello Aspose!";
//Saving the Excel file
workbook.Save("AddingCommentInCell.xls");
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Setting the name of the newly added worksheet
worksheet.Name = "Adding Comment in Execl Cell";
//Adding a comment to "F5" cell
int commentIndex = worksheet.Comments.Add("F5");
//Adding a string value to the cell
worksheet.Cells["F5"].PutValue("Hello World");
//Accessing the newly added comment
Comment comment = worksheet.Comments[commentIndex];
//Setting the comment note
comment.Note = "Hello Aspose!";
//Saving the Excel file
workbook.Save("AddingCommentInCell.xls");
}
private static void CreateSalesReport(string filename)
{
/*
* Uncomment the code below when you have purchased license
* for Aspose.Cells. You need to deploy the license in the
* same folder as your executable, alternatively you can add
* the license file as an embedded resource to your project.
*/
Aspose.Cells.License cellsLicense = new
Aspose.Cells.License();
cellsLicense.SetLicense("Aspose.Cells.lic");
// Create a new Workbook.
Workbook workbook = new Workbook();
/*
* Note: Since Excel color palette has 56 colors on it.
* The colors are indexed 0-55.
* Please check: http:// Www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html
* If a color is not present on the palette, we have to add it
* To the palette, so that we may use.
* Add a few custom colors to the palette.
*/
workbook.ChangePalette(Color.FromArgb(155, 204, 255), 55);
workbook.ChangePalette(Color.FromArgb(0, 51, 105), 54);
workbook.ChangePalette(Color.FromArgb(250, 250, 200), 53);
workbook.ChangePalette(Color.FromArgb(124, 199, 72), 52);
CreateReportData(workbook);
CreateCellsFormatting(workbook);
// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
// Name the worksheet.
worksheet.Name = "Sales Report";
// Save the excel file.
workbook.Save(filename);
}
public string ConvertExcelToPdf(string excelFilePath, Board board)
{
Aspose.Cells.License cellsLicense = new Aspose.Cells.License();
//cellsLicense.SetLicense("Aspose.Cells.lic");
//open excel file
Workbook workBook = new Workbook(excelFilePath);
var fileName = board.Group.GroupName + "_" + board.BoardId + "_result" + @".pdf";
var uploadFolderPath = Path.Combine(host.ContentRootPath, "exports/pdf");
if (!System.IO.Directory.Exists(uploadFolderPath))
{
System.IO.Directory.CreateDirectory(uploadFolderPath);
}
var filePath = Path.Combine(uploadFolderPath, fileName);
//save workbook in PDF format
workBook.Save(filePath, SaveFormat.Pdf);
return(filePath);
}
private void InitialLicense(string path)
{
//設定License
string fileName = path.Split('\\').Last().Split('.')[1];
string extension = string.Empty;
extension = fileName.Split('.').Last().ToLower();
switch (extension)
{
case "xls":
case "xlsx":
Aspose.Cells.License Excellic = new Aspose.Cells.License();
Excellic.SetLicense("Aspose.Total.lic");
break;
case "doc":
case "docx":
// Apply Aspose.Words API License
Aspose.Words.License Wordlic = new Aspose.Words.License();
// Place license file in Bin/Debug/Folder
Wordlic.SetLicense("Aspose.Total.lic");
break;
case "tiff":
case "tif":
case "png":
case "gif":
case "jpeg":
case "jpg":
case "xpm":
Aspose.Imaging.License Imglic = new Aspose.Imaging.License();
Imglic.SetLicense("Aspose.Total.lic");
break;
default:
break;
}
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Set the active cell
workbook.Worksheets[0].ActiveCell = "A10";
//Split the worksheet window
workbook.Worksheets[0].Split();
workbook.Save("output-Split.xls");
}
private static void CreateSalesReport(string filename)
{
/*
* Uncomment the code below when you have purchased license
* for Aspose.Cells. You need to deploy the license in the
* same folder as your executable, alternatively you can add
* the license file as an embedded resource to your project.
*/
Aspose.Cells.License cellsLicense = new
Aspose.Cells.License();
cellsLicense.SetLicense("Aspose.Cells.lic");
// Create a new Workbook.
Workbook workbook = new Workbook();
/*
* Note: Since Excel color palette has 56 colors on it.
* The colors are indexed 0-55.
* Please check: http:// Www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html
* If a color is not present on the palette, we have to add it
* To the palette, so that we may use.
* Add a few custom colors to the palette.
*/
workbook.ChangePalette(Color.FromArgb(155, 204, 255), 55);
workbook.ChangePalette(Color.FromArgb(0, 51, 105), 54);
workbook.ChangePalette(Color.FromArgb(250, 250, 200), 53);
workbook.ChangePalette(Color.FromArgb(124, 199, 72), 52);
CreateReportData(workbook);
CreateCellsFormatting(workbook);
// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
// Name the worksheet.
worksheet.Name = "Sales Report";
// Save the excel file.
workbook.Save(filename);
}
static void Main(string[] args)
{
// Check for license and apply if exists
string licenseFile = AppDomain.CurrentDomain.BaseDirectory + "Aspose.Cells.lic";
if (File.Exists(licenseFile))
{
// Apply Aspose.Words API License
Aspose.Cells.License license = new Aspose.Cells.License();
// Place license file in Bin/Debug/ Folder
license.SetLicense("Aspose.Cells.lic");
}
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Set the active cell
workbook.Worksheets[0].ActiveCell = "A10";
//Split the worksheet window
workbook.Worksheets[0].Split();
workbook.Save("output-Split.xls");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) //&& !rcbDistribution.IsCallBack)
{
//SecurityCheck();
//Instantiate an instance of license and set the license file through its path
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
//PopulateCurrency();
//PopulateReportTypes();
PopulateDistributeBy();
PopulateLocation();
//PopulateDistribution(rcbDistributeBy.SelectedValue.ToString().Trim());
EnableButtons(false);
}
else
{
this.RadScriptManager1.RegisterPostBackControl(btnExportExcel);
this.RadScriptManager1.RegisterPostBackControl(btnViewReport);
}
}
/// <summary>
/// Initializes static members of the <see cref="FileManager"/> class.
/// </summary>
static FileManager()
{
var byteArray = Encoding.ASCII.GetBytes(AsposeLicense);
using (var licenseStream = new MemoryStream(byteArray))
{
var pdfLicense = new Aspose.Pdf.License();
pdfLicense.SetLicense(licenseStream);
licenseStream.Position = 0;
var cellLicense = new Aspose.Cells.License();
cellLicense.SetLicense(licenseStream);
licenseStream.Position = 0;
var wordsLicense = new Aspose.Words.License();
wordsLicense.SetLicense(licenseStream);
licenseStream.Position = 0;
var slidesLicense = new Aspose.Slides.License();
slidesLicense.SetLicense(licenseStream);
licenseStream.Position = 0;
var tasksLicense = new Aspose.Tasks.License();
tasksLicense.SetLicense(licenseStream);
licenseStream.Position = 0;
var diagramLicense = new Aspose.Diagram.License();
diagramLicense.SetLicense(licenseStream);
}
}
public ActionResult GenerateExcel([DataSourceRequest]DataSourceRequest request, string colsDef, string gridData,
string client, string matter, string currency, string rateGrp, string start, string duration)
{
// A column dataType based on the first record in the result set.
//DataTable dt = (DataTable)JsonConvert.DeserializeObject(gridData, (typeof(DataTable)));
var dtData = JsonConvert.DeserializeObject<DataTable>(gridData);
/*
var dtData = JsonConvert.DeserializeObject<DataTable>(gridData, new JsonSerializerSettings {
// http://stackoverflow.com/questions/26484176/jobject-parse-modifies-end-of-floating-point-values
FloatParseHandling = FloatParseHandling.Decimal,
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore,
Formatting = Formatting.None,
DateFormatHandling = DateFormatHandling.IsoDateFormat,
Converters = new List<JsonConverter> { new DecimalConverter() }
});
*/
var dtCols = JsonConvert.DeserializeObject<DataTable>(colsDef);
Dictionary<string, string> dicCols = new Dictionary<string, string>();
#region Need to get dtData to match dtCols: Columns order & title
foreach (DataRow dr in dtCols.Rows)
dicCols.Add((string)dr["field"], (string)dr["title"]);
dtData.SetColumns(dicCols);
// Remove extraneous columns
int cnt = dtData.Columns.Count;
for (int j = dtCols.Rows.Count; j < cnt; cnt--)
{
dtData.Columns.RemoveAt(j);
}
#endregion
//Create a new worksheet
var license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
var cells = worksheet.Cells;
//Import data
int firstRow = 9; // zero-based
cells.ImportDataTable(dtData, true, firstRow, 0);
TimeZoneInfo localZone = TimeZoneInfo.Local;
string sRunDate = String.Format("{0:MM/dd/yyyy hh:mm tt}", DateTime.Now) +
" (" + ((localZone.IsDaylightSavingTime(DateTime.Now)) ? localZone.DaylightName : localZone.StandardName) + ")";
cells["A1"].PutValue("Report: LW1020 Rate Modeler");
cells["A2"].PutValue("Run Date: " + sRunDate);
cells["A3"].PutValue(client);
cells["A4"].PutValue(matter);
cells["A5"].PutValue(currency);
cells["A6"].PutValue(rateGrp);
cells["A7"].PutValue(start);
cells["A8"].PutValue(duration);
Style stl1 = workbook.Styles[workbook.Styles.Add()];
stl1.Custom = "#,##0.00";
StyleFlag flg1 = new StyleFlag();
flg1.NumberFormat = true;
Style stl2 = workbook.Styles[workbook.Styles.Add()];
stl2.HorizontalAlignment = TextAlignmentType.Center;
stl2.Font.Color = Color.Blue;
stl2.Font.IsBold = true;
stl2.Font.Name = "Arial";
stl2.Font.Size = 10;
StyleFlag flgTit = new StyleFlag();
flgTit.Font = true;
StyleFlag flgHdr = new StyleFlag();
flgHdr.Font = true;
flgHdr.HorizontalAlignment = true;
Range rngTit = cells.CreateRange("A1", "A8");
Range rngHdr = cells.CreateRange("A10", "BB10");
rngTit.ApplyStyle(stl2, flgTit);
rngHdr.ApplyStyle(stl2, flgHdr);
#region *** Set up subtotal formulas ***
string xlCol = string.Empty;
int firstDataRow = firstRow + 2;
int lastDataRow = firstDataRow + dtData.Rows.Count - 1;
int subRow = lastDataRow + 2;
for (int i = 6; i <= dtData.Columns.Count; i++)
{
xlCol = GetExcelColumnName(i);
switch (i)
{
case 6: // Hours Billed
cells[xlCol + subRow].Formula = "sum(" + xlCol + firstDataRow + ":" + xlCol + lastDataRow + ")";
break;
default: // Amount Billed $,Prop Hours,Prop Calc,Total Proj Calc 1,...
if (i % 2 == 1) cells[xlCol + subRow].Formula = "sum(" + xlCol + firstDataRow + ":" + xlCol + lastDataRow + ")";
break;
}
}
#endregion
//Format the columns
worksheet.AutoFitColumns();
for (int i = 0; i < dtData.Columns.Count; i++)
{
switch (i)
{
case 0:
cells.SetColumnWidth(i, 14);
break;
case 1:
case 2:
case 3:
case 4:
// Do nothing
break;
default:
cells.Columns[i].ApplyStyle(stl1, flg1);
break;
}
}
//Save file
worksheet.Name = Global.REPORT_ID;
string fileName = Global.REPORT_ID + "_" + String.Format("{0:MM_dd_yyyy_hh_mm_ss_tt}", DateTime.Now) + ".xlsx";
// http://www.aspose.com/docs/display/cellsnet/Saving+Files
var ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
ms.Position = 0;
Session[fileName] = ms;
return Json(new { fileName }, JsonRequestBehavior.AllowGet);
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
//After page set idle for a while, the license object lost.
//To avoid the evaluation copy issue, we have to set license on every click
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
DataTable dtMain;
using (var ctx = new DBContext())
{
dtMain = ctx.getExportAttys();
//ctx.LogUser(ntID, Global.REPORT_ID);
}
//Create a new worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Import data
cells.ImportDataTable(dtMain, true, 5, 0);
//Create range and styles for the headings
Aspose.Cells.Style stl1 = workbook.Styles[workbook.Styles.Add()];
stl1.VerticalAlignment = TextAlignmentType.Center;
stl1.Font.Color = Color.Blue;
stl1.Font.IsBold = true;
stl1.Font.Name = "Arial";
stl1.Font.Size = 10;
StyleFlag flg = new StyleFlag();
flg.Font = true;
Range rngHd = cells.CreateRange("A1", "Z6");
rngHd.ApplyStyle(stl1, flg);
//Format the columns
worksheet.AutoFitColumns();
/*** TESTING ***
// http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers+and+Dates
Aspose.Cells.Style stl2 = workbook.Styles[workbook.Styles.Add()];
StyleFlag flg2 = new StyleFlag();
stl2.Number = 15;
cells.Columns[6].ApplyStyle(stl2, flg2);
*** END TESTING ***/
cells.SetColumnWidth(10, 45); //Primary Practice Group
cells.SetColumnWidth(11, 45); //Bar
// Populate header
cells[0, 0].PutValue("Report:");
cells[0, 1].PutValue("LW1190 Brown Book Maintenance");
TimeZoneInfo localZone = TimeZoneInfo.Local;
string strRunDate = String.Format("{0:MM/dd/yyyy hh:mm tt}", DateTime.Now) +
" (" + ((localZone.IsDaylightSavingTime(DateTime.Now)) ? localZone.DaylightName : localZone.StandardName) + ")";
cells[1, 0].PutValue("Run Date:");
cells[1, 1].PutValue(strRunDate);
cells[2, 0].PutValue("Report Type:");
cells[2, 1].PutValue("Associates");
//Save file to the client
string sExcel = "LW1190";
worksheet.Name = sExcel;
sExcel += "_" + String.Format("{0:MM_dd_yyyy_hh_mm_ss_tt}", DateTime.Now) + ".xls";
workbook.Save(this.Response, sExcel, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
