You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
ExcelLibrary
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License
Here some example code for ExcelLibrary:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
Overview
There are three primary browser APIs for copying to the clipboard:
Async Clipboard API [navigator.clipboard.writeText]
- Text-focused portion available in Chrome 66 (March 2018)
- Access is asynchronous and uses JavaScript Promises, can be written so security user prompts (if displayed) don't interrupt the JavaScript in the page.
- Text can be copied to the clipboard directly from a variable.
- Only supported on pages served over HTTPS.
- In Chrome 66 pages inactive tabs can write to the clipboard without a permissions prompt.
document.execCommand('copy')
(deprecated) 👎
- Most browsers support this as of ~April 2015 (see Browser Support below).
- Access is synchronous, i.e. stops JavaScript in the page until complete including displaying and user interacting with any security prompts.
- Text is read from the DOM and placed on the clipboard.
- During testing ~April 2015 only Internet Explorer was noted as displaying permissions prompts whilst writing to the clipboard.
Overriding the copy event
- See Clipboard API documentation on Overriding the copy event.
- Allows you to modify what appears on the clipboard from any copy event, can include other formats of data other than plain text.
- Not covered here as it doesn't directly answer the question.
General development notes
Don't expect clipboard related commands to work whilst you are testing code in the console. Generally, the page is required to be active (Async Clipboard API) or requires user interaction (e.g. a user click) to allow (document.execCommand('copy')
) to access the clipboard see below for more detail.
IMPORTANT (noted here 2020/02/20)
Note that since this post was originally written deprecation of permissions in cross-origin IFRAMEs and other IFRAME "sandboxing" prevents the embedded demos "Run code snippet" buttons and "codepen.io example" from working in some browsers (including Chrome and Microsoft Edge).
To develop create your own web page, serve that page over an HTTPS connection to test and develop against.
Here is a test/demo page which demonstrates the code working:
https://deanmarktaylor.github.io/clipboard-test/
Async + Fallback
Due to the level of browser support for the new Async Clipboard API, you will likely want to fall back to the document.execCommand('copy')
method to get good browser coverage.
Here is a simple example (may not work embedded in this site, read "important" note above):
function fallbackCopyTextToClipboard(text) {
var textArea = document.createElement("textarea");
textArea.value = text;
// Avoid scrolling to bottom
textArea.style.top = "0";
textArea.style.left = "0";
textArea.style.position = "fixed";
document.body.appendChild(textArea);
textArea.focus();
textArea.select();
try {
var successful = document.execCommand('copy');
var msg = successful ? 'successful' : 'unsuccessful';
console.log('Fallback: Copying text command was ' + msg);
} catch (err) {
console.error('Fallback: Oops, unable to copy', err);
}
document.body.removeChild(textArea);
}
function copyTextToClipboard(text) {
if (!navigator.clipboard) {
fallbackCopyTextToClipboard(text);
return;
}
navigator.clipboard.writeText(text).then(function() {
console.log('Async: Copying to clipboard was successful!');
}, function(err) {
console.error('Async: Could not copy text: ', err);
});
}
var copyBobBtn = document.querySelector('.js-copy-bob-btn'),
copyJaneBtn = document.querySelector('.js-copy-jane-btn');
copyBobBtn.addEventListener('click', function(event) {
copyTextToClipboard('Bob');
});
copyJaneBtn.addEventListener('click', function(event) {
copyTextToClipboard('Jane');
});
<div style="display:inline-block; vertical-align:top;">
<button class="js-copy-bob-btn">Set clipboard to BOB</button><br /><br />
<button class="js-copy-jane-btn">Set clipboard to JANE</button>
</div>
<div style="display:inline-block;">
<textarea class="js-test-textarea" cols="35" rows="4">Try pasting into here to see what you have on your clipboard:
</textarea>
</div>
(codepen.io example may not work, read "important" note above)
Note that this snippet is not working well in Stack Overflow's embedded preview you can try it here: https://codepen.io/DeanMarkTaylor/pen/RMRaJX?editors=1011
Async Clipboard API
Note that there is an ability to "request permission" and test for access to the clipboard via the permissions API in Chrome 66.
var text = "Example text to appear on clipboard";
navigator.clipboard.writeText(text).then(function() {
console.log('Async: Copying to clipboard was successful!');
}, function(err) {
console.error('Async: Could not copy text: ', err);
});
document.execCommand('copy')
The rest of this post goes into the nuances and detail of the document.execCommand('copy')
API.
Browser Support
The JavaScript document.execCommand('copy')
support has grown, see the links below for browser updates: (deprecated) 👎
Simple Example
(may not work embedded in this site, read "important" note above)
var copyTextareaBtn = document.querySelector('.js-textareacopybtn');
copyTextareaBtn.addEventListener('click', function(event) {
var copyTextarea = document.querySelector('.js-copytextarea');
copyTextarea.focus();
copyTextarea.select();
try {
var successful = document.execCommand('copy');
var msg = successful ? 'successful' : 'unsuccessful';
console.log('Copying text command was ' + msg);
} catch (err) {
console.log('Oops, unable to copy');
}
});
<p>
<button class="js-textareacopybtn" style="vertical-align:top;">Copy Textarea</button>
<textarea class="js-copytextarea">Hello I'm some text</textarea>
</p>
Complex Example: Copy to clipboard without displaying input
The above simple example works great if there is a textarea
or input
element visible on the screen.
In some cases, you might wish to copy text to the clipboard without displaying an input
/ textarea
element. This is one example of a way to work around this (basically insert an element, copy to clipboard, remove element):
Tested with Google Chrome 44, Firefox 42.0a1, and Internet Explorer 11.0.8600.17814.
(may not work embedded in this site, read "important" note above)
function copyTextToClipboard(text) {
var textArea = document.createElement("textarea");
//
// *** This styling is an extra step which is likely not required. ***
//
// Why is it here? To ensure:
// 1. the element is able to have focus and selection.
// 2. if the element was to flash render it has minimal visual impact.
// 3. less flakyness with selection and copying which **might** occur if
// the textarea element is not visible.
//
// The likelihood is the element won't even render, not even a
// flash, so some of these are just precautions. However in
// Internet Explorer the element is visible whilst the popup
// box asking the user for permission for the web page to
// copy to the clipboard.
//
// Place in the top-left corner of screen regardless of scroll position.
textArea.style.position = 'fixed';
textArea.style.top = 0;
textArea.style.left = 0;
// Ensure it has a small width and height. Setting to 1px / 1em
// doesn't work as this gives a negative w/h on some browsers.
textArea.style.width = '2em';
textArea.style.height = '2em';
// We don't need padding, reducing the size if it does flash render.
textArea.style.padding = 0;
// Clean up any borders.
textArea.style.border = 'none';
textArea.style.outline = 'none';
textArea.style.boxShadow = 'none';
// Avoid flash of the white box if rendered for any reason.
textArea.style.background = 'transparent';
textArea.value = text;
document.body.appendChild(textArea);
textArea.focus();
textArea.select();
try {
var successful = document.execCommand('copy');
var msg = successful ? 'successful' : 'unsuccessful';
console.log('Copying text command was ' + msg);
} catch (err) {
console.log('Oops, unable to copy');
}
document.body.removeChild(textArea);
}
var copyBobBtn = document.querySelector('.js-copy-bob-btn'),
copyJaneBtn = document.querySelector('.js-copy-jane-btn');
copyBobBtn.addEventListener('click', function(event) {
copyTextToClipboard('Bob');
});
copyJaneBtn.addEventListener('click', function(event) {
copyTextToClipboard('Jane');
});
<div style="display:inline-block; vertical-align:top;">
<button class="js-copy-bob-btn">Set clipboard to BOB</button><br /><br />
<button class="js-copy-jane-btn">Set clipboard to JANE</button>
</div>
<div style="display:inline-block;">
<textarea class="js-test-textarea" cols="35" rows="4">Try pasting into here to see what you have on your clipboard:
</textarea>
</div>
Additional notes
Only works if the user takes an action
All document.execCommand('copy')
calls must take place as a direct result of a user action, e.g. click event handler. This is a measure to prevent messing with the user's clipboard when they don't expect it.
See the Google Developers post here for more info.
Clipboard API
Note the full Clipboard API draft specification can be found here:
https://w3c.github.io/clipboard-apis/
Is it supported?
document.queryCommandSupported('copy')
should return true
if the command "is supported by the browser".
- and
document.queryCommandEnabled('copy')
return true
if the document.execCommand('copy')
will succeed if called now. Checking to ensure the command was called from a user-initiated thread and other requirements are met.
However, as an example of browser compatibility issues, Google Chrome from ~April to ~October 2015 only returned true
from document.queryCommandSupported('copy')
if the command was called from a user-initiated thread.
Note compatibility detail below.
Browser Compatibility Detail
Whilst a simple call to document.execCommand('copy')
wrapped in a try
/catch
block called as a result of a user click will get you the most compatibility use the following has some provisos:
Any call to document.execCommand
, document.queryCommandSupported
or document.queryCommandEnabled
should be wrapped in a try
/catch
block.
Different browser implementations and browser versions throw differing types of exceptions when called instead of returning false
.
Different browser implementations are still in flux and the Clipboard API is still in draft, so remember to do your testing.
Best Answer
Basically every iTextSharp document is attached to a
System.IO.Stream
.Usually we save the document to a file, using
FileStream
. To use the same code to paste the document in the Clipboard, we use aMemoryStream
instead.I only had problems with Images: It seems that iTextSharp exports images saving the bytes of the image after the
\bin
tag. Some libraries put the binary content encoded as hex characters. When I paste (from memory) in Word, the images won't appear, but if I load from a file, everything is OK. Any suggestions?