#Conversion of worksheet to datatable

[code-life]

How to convert base64 string format of excel file into DataTable form of data type using C#

How to convert base64 string format of excel file into DataTable form of data type using C#

In this article, We learn how to convert base64 string format of excel file into Data Table form of data type using C#. I will show how to do it, and just follow the steps clearly. For more updates please do Subscribe via Email: Type your email… Subscribe I encounter this task and make me sleepless during night because I can’t see any reference in other blog resources. Basically the logic…

[ryadel]

ASP.NET - Generate Excel files programmatically with EPPlus

If you've stumbled upon this post it most likely means that you're looking for a way to generate MS Excel XLS / XLSX files within your ASP.NET web application. The typical scenario for such needis the following one: you have a DBMS from where you pull off some data - using a OleDBConnection, a SqlConnection, an EntityFramework model or anything database-fetching technique  - which you want to send to your users through a downloadable MS Excel file. In purely theoretical terms, you might think about doing this using the MS Office Interop libraries, using a technique similar to what we explained in a couple other articles: Programmatically convert MS Word DOC and DOCX files to PDF using Office Interop Programmatically Access, Read and Edit MS Word DOC and DOCX files using Office Interop However, if you are here (or if you already read those posts) you most likely already know that the Microsoft Office primary interop assemblies (PIAs), better known as Office Interop, are not something you should rely upon: although there is an ASP.NET library package specifically designed to handle MS Excel files, the whole Office.Interop set of libraries is not meant to be used in web applications and will definitely cause you some nasty setup and configuration issues, such as those we enumerated (and tried to deal with) there. Luckily enough, there's a good news for you: a NuGet library package that does basically the same tasks you might want to use the Microsoft.Office.Interop.Excel namespace for, while working way better than that in a typical web application scenario: and it's also completely free to use! We're talking of EPPlus, a great library developed by Jan Kallman and available either on GitHub and as a compiled, full-flegded NuGet package. The project is licensed under the GNU Library General Public License (LGPL), meaning that it can be used everywhere. In this post we'll briefly show how we can install this great tool on a typical Visual Studio environment and then use it to generate a MS Excel file from a standard ASP.NET DataTable which we (ideally) pulled off by a DBMS of some sort.

Installing EPPlus

The most practical way to install EPPlus from Visual Studio is to open the NuGet Package Manager Console (View > Other Windows > Package Manager Console) and type the following command: If you prefer to install it through the .NET CLI, you can use this command from an elevated command prompt or powershell prompt: Version notes It's worth noting that this test-drive was performed using EPPlus version 4.5.3.1, which was the latest available version at the time of writing: such version is fully compatible with all the most recent versionf of the .NET Framework, from 3.5 to 4.7.x (we tested it with 4.7.2).

EPPlus usage example

Without further ado, here's a full source code sample that will convert a standard DataTable to a XLSX Excel file and then send it to the user for the download: As we can see, this is an ActionResult method that could fit on any ASP.NET MVC Controller: if you're not using ASP.NET MVC you can still use it, just copy the method content wherever you need to (classic ASP.NET, Console Application, Windows Forms, and so on). The code is quite self-explanatory and full of comments  that should hopefully explain the various processing steps. However, let's briefly summarize what are we doing here: We fetch a DataTable object using a custom Data Provider method. We create a ExcelPackage object, which is the main container used by EPPlus for the XLSX file. We add a ExcelWorksheet inside the ExcelPackage, which is the worksheet we'll put the data in. We iterate the DataTable columns, adding them to the first row of our worksheet to build our header row. We iterate the DataTable rows, adding each one of them to our worksheet (starting from row 2), so that each DataTable row will result in a corresponding worksheet row. Once the conversion between the DataTable and the ExcelPackage is done, we create a MemoryStream to store the ExcelPackage binary data, then convert it to a byte array. We prepare the HTML response and send the XLSX file to the user with Content-Disposition attachment, so that the browser will force the download of the file. It's worth noting that we used a one-liner function to retrieve the proper mime-type for XLSX files (which is "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"): such function is available only since .NET Framework 4.5 and it's the preferred method to retrieve mime-types since then. If you need to retrieve the mime-type with older .NET Frameworks, you can use one of the various workaround described in this other post.

Conclusion

That's basically it, at least for now: I will definitely write again about the topic in the near future and share additional code samples. Meanwhile, feel free to use or modify the above code to your specific needs and enjoy the DataTable-to-XSLX conversion thanks to EPPlus!   Read the full article

[file-formats-programming]

Auto-Fit Columns, Rows While Importing HTML & Get List of Fonts Used in Spreadsheet using Java

What’s new in this release?

Aspose team is pleased to announce the new release of Aspose.Cells for Java 17.2.0. This release includes many new features and enhancements along with some critical bug fixes that further improve the overall stability of the API. The release notes also list any changes made to the public API such as added, renamed, removed or deprecated members as well as any non-backward compatible change made to Aspose.Cells for Java. This release offers the metered licensing mechanism in parallel to its traditional licensing. In order to provide the usage based licensing, Aspose.Cells for Java has exposed the Metered class that allows the developers to set public and private keys by calling its setMeteredKey method. The metered licensing system monitors the regular usage of Aspose.Cells API and keep track of the Aspose API licenses. It has exposed the getFonts method for the Workbook class. The Workbook.getFonts method returns the list of individual fonts used to format the cell contents for a given spreadsheet. The list returned by the Workbook.getFonts method is in the form of an array of type com.aspose.cells.Font. The newly exposed method is useful in scenarios where the developers require to extract the list of the fonts used in a particular spreadsheet. The extracted list can further be used to match the available fonts on the machine before rendering the spreadsheet. This is because, if Aspose.Cells APIs are not able to find the required fonts, they try to replace the required font with some other suitable font which is present on the system. By comparing the system’s font list against the fonts used in the spreadsheet, the developers can devise some mechanism to either pick the required fonts from a custom location or substitute a particular font with a list of available fonts. Aspose.Cells for Java has now added the HTMLLoadOptions.AutoFitColsAndRows property which indicates if the API should auto-fit columns and rows while importing the HTML in its object mode. The Boolean type property has the default value as false which means that the cell heights & widths will be imported as they are, however, when the aforementioned property is set to true, the API tries to adjust the column widths and row heights according to the contents. Latest revision of Aspose.Cells for Java has exposed the Cells.textToColumns method in order to mimic the Excel’s Text to Columns feature. Excel provides this feature from the command group Data Tools under the Data tab as highlighted in the snapshot on blog announcement page.This release has exposed the WarningCallback property for the LoadOptions class in order to get or set the warning callbacks. Developers have to implement the IWarningCallback interface in order to get custom warnings in their applications. This revision of Aspose.Cells for Java has exposed Boolean type TrimLeadingBlankRowAndColumn property for the TxtSaveOptions class that indicates whether leading blank rows and columns should be trimmed like Excel does while exporting data to CSV or Tab-delimited formats. The default value of aforementioned property is false. In case the data on the worksheet does not start from the first cell, that is: A1, the Excel application removes the leading blank rows and columns while exporting the data to CSV or Tab-delimited formats, however, Aspose.Cells APIs by default, retain the blank rows & columns for the same sample in order to keep the data location retained if the exported CSV or Tab-delimited files have to be imported back using Aspose.Cells APIs. It has enhanced its core for more stability as well as fixed a few critical bugs. Moreover, there are numerous minor enhancements in order to improve the overall usage of the API.  It enhanced Support to load Excel 2016 chart types, Enhanced HTML rendering engine for shapes & Handled a few exceptions. Below are some important new features, enhancement and bug fixes part of this release.

Support MS Excel 2016 TreeMap Chart

Support MS Excel 2016 Waterfall Chart

Support Converting Text to Columns MS Excel feature

Data loss while copying range with hidden rows & columns to new workbook and converting it to HTML

Data loss while copying range with hidden rows & columns to new workbook and converting it to HTML - II

Data loss while copying range with hidden rows & columns to new workbook and converting it to HTML - III

Save as PDF does not preserve formatting

Excel formula is not working and shown as "#DIV/0!"

Concurrent save issue

Top and bottom borders of cells are gone while converting to HTML

Excel formula is not working properly

Recalculating a number of formulas using Aspose Cells APIs results in "#NUM!" error

Demo page of math does not load correctly in GridWeb (Java) demo project

List data validation drop down does not close when reloading data

PageSetup.BlackAndWhite does not seem to work

Bug in try catch and re-throw exception where ExceptionType is changed

Waterfall chart, when calculate() is called, chart series colors are reverted.

Logarithmic scale bug in Excel causes Aspose Cells to hang

Vertical Axis bound values changed while rendering spreadsheet to PDF

Horizontal & vertical axis bound values changed while rendering Chart to EMF

Hebrew - Space character is missing in PDF

Chart is vertically suppressed while rendering to image

DataTable series are missing while exporting chart to image

Missing underline in title when chart is converted into image

Background image in chart is wrong

The space b/w chart's axis (hebrew) labels/legend is missing in the output PDF file

Contents of the rectangle shape do not render while converting spreadsheet to HTML

Chart has been changed while converting spreadsheet to HTML

Formula in chart has changed position while converting spreadsheet to HTML

Excel to PDF conversion - Arabic text is reversed

Sheet name gets Upper-cased on inserting formula

Call to updateSelectedValue changes activeSheetIndex

Protected view after re-saving an XLS file

Copying a workbook changes the standardHeight

Formula in custom validation gets missing when workbook is saved in XLS format

Excel needs to recover file after simple save through Aspose.Cells

Spreadsheet becomes corrupted after changing the font for the shapes

Not able to change the font for a few shapes in the collection

Password protected Excel file is throwing exception on loading

The size of the destination workbook is almost double the size of the source workbook

Copying sheets across workbooks changes the formula

Unable to read the CheckBox's text value

GetNames() method is not returning all the names

The pages of the PDF generated by Aspose.Cells are all black

Other most recent bug fixes are also included in this release

Newly added documentation pages and articles

Some new tips and articles have now been added into Aspose.Cells for Java documentation that may guide users briefly how to use Aspose.Cells for performing different tasks like the followings.

Convert Text to Columns using Aspose.Cells

AutoFit Columns and Rows while loading HTML in Workbook

Overview: Aspose.Cells for Java

Aspose.Cells is a Java component for spreadsheet reporting without using Microsoft Excel. Other features include creating spreadsheets, opening encrypted excel files, macros, VBA, unicode, formula settings, pivot tables, importing data from JDBC ResultSet and support of CSV, SpreadsheetML, PDF, ODS and all file formats from Excel 97 to Excel 2007. It is compatible with Windows, Linux & Unix and supports all advanced features of data management, formatting, worksheet, charting and graphics

More about Aspose.Cells for Java

Homepage of Aspose.Cells for Java

Download Aspose.Cells for Java