How to format cells in .XLSB file in .net without Interop - c#

Is there anyway to create/open a XLSB file and format their cell and save them on a local folder. Sample code would help me much easier. People who are cliking the down arrow is just making my day worsen. Please help or at least stay calm.

Related

Populate excel from epplus on windows app

I have a windows app that opens excel. The user can then decide to save it.
This uses Interop and is very slow when we have over 100 rows. I'm experimenting with EPPlus and see how to save directly to a file. But I don't want to do that, I need to open Excel from the app. So I'm thinking to write out the worksheet using EPPlus (thus saving time) and then copying the whole worksheet into Excel and open it. Does that make sense? Are there examples of this?
First off, I've used EPPlus and it's a great tool.
I'm a little confused by your question, so let me ask some clarifying questions.
Why are you launching excel from your app? Is it to edit some kind of file your program generates, or just random files the user wants to edit?
Once the user is finished editing the file, do you need to read it back into your app? If so, that can also be done with EPPlus.
Once I know more about what exactly you're trying to do, I'll be glad to help you.
--John

Prompted to Save Changes on file created with EPPlus

I am creating a series of Excel Workbooks using EPPlus v3.1.3. When I open the newly created files, if I close it without touching anything it asks me if I want to save my changes. The only thing I've noticed changes if I say "yes" is that the app.xml file is slightly altered - there is no visible difference in the workbook, and the rest of the XML files are the same. I have tried both of these approaches:
ExcelPackage p = new ExcelPackage(new FileInfo(filename));
p.Save();
as well as
ExcelPackage p = new ExcelPackage();
p.SaveAs(new FileInfo(filename));
and both have the same problem. Is there a way to have the app.xml file output in its final form?
The reason this is an issue is because we use a SAS program to QC, and when the SAS program opens the files as they have been directly output from the EPPlus program it doesn't pick up the values from cells that have formulas in them. If it is opened and "yes" is chosen for "do you want to save changes", it works fine. However, as we are creating several hundred of these, that is not practical.
Also, I am using a template. The template appears normal.
What is particularly strange is that we have been using this system for well over a year, and this is the first time we have encountered this issue.
Is there any way around this? On either the C# or SAS side?
What you are seeing is not unusual actually. Epplus does not actually generate a full XLSX file - rather it creates the raw XML content (all office 2007 document formats are xml-based) and places it in the zip file which is renamed to XLSX. Since it has not been ran through the Excel engine it has not be fully formatted to excels liking.
If it is a simple data sheet then chances are Excel does not have to do much calculation - just basic formatting. So in that case it will not prompt you to save. But even then if you do you will see it change the XLSX file a little. If you really want to see what it is doing behind the scenes rename the file to .zip and look at the xml files inside before and after.
The problem you are running in to is because it is not just a simple table export Excel has to run calculations when opened for the first time. This could be many things - formulas, autofilters, auto column/row height adustments, outlining, etc. Basically, anything that will make the sheet look a little "different" after excel gets done with it.
Unfortunately, there is no easy fix for this. Running it through excel's DOM somehow would be simplest which of course defeats the purpose of using EPPlus. The other thing you could do is see the difference between the before and after of the xml files (and there are a bunch in there you would have to look at) and mimic what excel would change/add in the "after" file version by manually editing the XML content. This is not a very pretty option depending on how extensive the changes would be. You can see how I have done it in other situations here:
Create Pivot Table Filters With EPPLUS
Adding a specific autofilter on a column
Set Gridline Color Using EPPlus?
I ran into this same issue using EPPlus (version 4.1.0, fyi) and found adding the following code before closing fixed the problem:
p.Workbook.Calculate();
p.Workbook.FullCalcOnLoad = false;

C# File open / convert and save

I am very new to C# and hoping this is a simple question. Not finding what I need on google
I have a file C:\test\losses.csv
That I want to open up then convert to an xlsx file and save in a different directory.
Save to
C:\test\Losses.xlsx
The reason for opening the file is the move command does not convert it to xlsx, unfortunately it keeps the same structure as the csv and is unusable in that format.
File.Copy(#"C:\test\losses.csv", #"C:\test1\Losses.xlsx");
The above code works great but still is a csv file (well really a hybrid of one). That is another SAP story.
Any help will be greatly appreciated. Thanks
File.Copy only copies the file - similar to copying a file in DOS or in windows file explorer.
You'd need to translate your CSV to an XLSX file. The format should be pretty straight-forward, but you'll need to do more research:
Load the CSV as a data table
Use the Excel.XlFileFormat.xlOpenXMLWorkbook class to translate the file.
A different StackOverflow problem addresses how to use the xlOpenXMLWorkbook:
Exporting to .xlsx using Microsoft.Office.Interop.Excel SaveAs Error
Hope this helps. Good luck.

How to read file properties from many different files and save them to Excel [closed]

First, thanks for trying to answer my question.
Ok, so I have loads of files (somewhere in the excess of about 500) from which I need to grab some of their properties. I need the File Name, Date Created, and Author and I'd like them to be saved into an Excel sheet.
Any idea on how I'd go about doing this? I've got a little bit of C# and VB.Net programming skills but not a lot. Anything you can do to help will be greatly appreciated!
Thanks.
get a way of parsing through all the files (e.g. Loop through the return result of Directory.GetFiles)
create a FileInfo object for each file, and then check properties you are looking for.
find an API to create the Excel file (e.g Excel interop) to create an Excel and insert your information of each file into a row.

Strip Excel file of Macros with C#

I've been asked to strip an Excel file of macros, leaving only the data. I've been asked to do this by converting the Excel file to XML and then reading that file back into Excel using C#. This seems a bit inefficient to me and I was thinking that it would be easier to simply load the source Excel file into C# and then create a new target Excel file and add the sheets from the source back into the target.
I don't know where macros live inside an Excel file, so I'm not sure if this would accomplish the task or not. So, will this work? Will simply copying the sheets from one file to another strip it of it's macros or are they actually stored at the worksheet level?
As always, any and all suggestions are welcome, including alternate suggestions or even "why are you even doing this???". :)
Your best bet is to save the workbook as an xlsx, close it, open it, then save as a format of your choice.
This will strip the macros and is robust. It will also work if the VBA is locked for viewing.
Closing and reopening the workbook is necessary otherwise the macros are retained.
To do this programmatically, you can use the ZipFile class from the System.IO.Compression library in .NET from C#. (.NET Framework 4.5)
Rename the file to add a ".zip" extension, and then open the file as a ZIP archive. Look for an element in the resultant "xl" folder called "vbproject.bin", and delete it. Remove the .zip extension. Macros gone.
If you're needing to use C# to do this, I agree that it would be easier to load the source Excel file into C# and create a new target file only copying over the cells and sheets you need. Especially if you're doing this for a large amount of excel files I would recommend just creating a small console app that, when given an excel sheet, will automatically generate a new excel sheet with just the data for you.
One tool that I've found extremely useful and easy to use for such tasks is EPPlus.

Resources