Rectangle 27 4

+1 on J Ashley's comment. I ran into this problem also. It turns out that Excel requires:

I used notepad ++ to delimit each row properly and to only use newlines in the string. Discovered this by creating multiline entries in a blank excel doc and opening the csv in notepad ++.

it worked for me with only newline character as both a multiline item and a row separator, once i set the field separator according to my locale

Importing CSV with line breaks in Excel 2007 - Stack Overflow

csv excel-2007
Rectangle 27 2

If it is not a problem you can break the view results with a pager (for example of 1000 rows) and export the results for each page. This will reduce the memory requirements as also as the excel file size.

I want to export all the result to a single file. Is it possible?

Does the views query work if you display it on a page display?

php - Views Data Export for Larger Files fails - Stack Overflow

php excel drupal export
Rectangle 27 1

You can do it in Microsoft excel if you want, just put your list of cities into it, organize it the way you want it inserted and then save it as a csv file, here is a link on the matter: http://office.microsoft.com/en-us/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx . If you don't have Microsoft Excel you can download openoffice here: http://www.openoffice.org/download/ you can also do the same with their Excel.

How to create the CSV file so that its data can be imported to mysql d...

mysql csv mysqlimport
Rectangle 27 2

The issue is most likely not coming from mysql.

It is most likely coming from the way Excel displays and later saves the dates. to troublehsoot:

Open the file in a text editor such as notepad or notepad++ and check what the date looks like, if it's in ISO or not. It will probably be fine.

Now, if you open it in excel, it will be displayed in local format.

If you save the file now, you are likely to overwrite the ISO date format, with excel's local date format, making it not a valid importable mysql date anymore.

Moral: don't use excel when working with data, only use it to display charts. Excel makes assumptions about your data and messes with it in the most unexpected ways. Remember than 1.19 VAT tax rate? Excel seems to think it's the same as Jan 19. That integer ID? Excel thinks it's better off to write it in scientific notation and round it to first 4 digits. That Iso date? Excel thinks you are better off guessing which is the month and which is the date. That decimal point? surely you wanted comma as decimal, and dot as thousands separator instead. FTFY!

+1 Excel was overriding the date format hence opening it in notepad++ was also showing wrong format. Date format seems to work now but the "\n" is still causing problem?

the \N is a placeholder for null. If you import the data via an excel plugin, (mysql for excel) it will be displayed properly. Otherwise, you can kind of fixing it when writing to outfile by wrapping your columns in ifnull(intcolumn, 0) or ifnull(stringcol, '')

You can also fix it in excel after opening the file by doing a replace with nothing. Keep in mind that when loading the file with \N back in mysql, it will treat it properly as nulls - so you don't actually have to mess with it

mysql - select * INTO outfile generates date in wrong format - Stack O...

mysql sql date csv
Rectangle 27 1

CREATE DATABASE soa_36427533;
USE soa_36427533;

CREATE TABLE soa_ranges (
    ID UNSIGNED NOT NULL AUTO_INCREMENT,
    min FLOAT(10,2),
    max FLOAT(10,2),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_range_taxonomies (
    ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_category (
    ID UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_category_taxonomies (
    ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_range_category_relationships (
    rangeID BIGINT UNSIGNED NOT NULL,
    rangeTaxID BIGINT UNSIGNED NOT NULL,
    categoryID BIGINT UNSIGNED NOT NULL,
    categoryTaxID BIGINT UNSIGNED NOT NULL,
    value INTEGER,
    PRIMARY KEY (rangeID, rangeTaxID, categoryID, categoryTaxID),
    FOREIGN KEY (rangeID) REFERENCES soa_ranges (ID) ON DELETE CASCADE,
    FOREIGN KEY (rangeTaxID) REFERENCES soa_range_taxonomies (ID) ON DELETE CASCADE,
    FOREIGN KEY (categoryID) REFERENCES soa_categories (ID) ON DELETE CASCADE,
    FOREIGN KEY (categoryTaxID) REFERENCES soa_category_taxonomies (ID) ON DELETE CASCADE
) ENGINE=InnoDB;

SQL to get the valid ranges, categories, and taxonomies:

SELECT * FROM soa_ranges ORDER BY ID;
SELECT * FROM soa_range_taxonomies ORDER BY ID;
SELECT * FROM soa_categories ORDER BY ID;
SELECT * FROM soa_category_taxonomies ORDER BY ID;

PHP to display these results as select boxes:

<?php
$db = new mysqli(HOST, USER, PASSWORD, 'soa_36427533');
if ( $db->connect_errno ) die;

$range_select_vals = array();
$ranges = $db->query( "SELECT * FROM soa_ranges ORDER BY ID" );
while ( $range = $ranges->fetch_assoc() ) {
    $range_vals = array();
    for ( $i =  $range['min'] * 100; $i < ( $range['max'] * 100 ); $i++ ) {
        $range_vals[] = floatval( $i / 100 );
    }
    $range_select_vals[$range['ID']] = $range_vals;
}
$ranges->free();

$range_taxonomy_select_vals = array();
$range_taxonomies = $db->query( "SELECT * FROM soa_range_taxonomies ORDER BY ID" );
while ( $range_taxonomy = $range_taxonomies->fetch_assoc() ) {
    $range_taxonomy_select_vals[$range_taxonomy['ID']] = $range_taxonomy['name'];
}
$range_taxonomies->free();

$category_select_vals = array();
$categories = $db->query( "SELECT * FROM soa_categories ORDER BY ID" );
while ( $category = $categories->fetch_assoc() ) {
    $category_select_vals[$category['ID']] = $category['name'];
}
$categories->free();

$category_taxonomy_select_vals = array();
$category_taxonomies = $db->query( "SELECT * FROM soa_category_taxonomies ORDER BY ID" );
while ( $category_taxonomy = $category_taxonomies->fetch_assoc() ) {
    $category_taxonomy_select_vals[$category_taxonomy['ID']] = $category_taxonomy['name'];
}
$category_taxonomies->free();
?>
<select id="range" name="range"><?php

foreach ( $range_select_vals as $range_key => $range_values ) {
    foreach( $range_values AS $value ) {
    ?>    <option value="<?php echo $range_key; ?>"><?php echo $value; ?></option><?php
    }
}
?>
</select>
<select id="range_taxonomy" name="range_taxonomy"><?php

foreach ( $range_taxonomy_select_vals as $taxonomy_key => $taxonomy_name ) {
?>    <option value="<?php echo $taxonomy_key; ?>"><?php echo $taxonomy_name; ?></option><?php
}
?>
</select>
<select id="category" name="category"><?php

foreach ( $category_select_vals as $category_key => $category_name ) {
?>    <option value="<?php echo $category_key; ?>"><?php echo $category_name; ?></option><?php
}
?>
</select>
<select id="category_taxonomy" name="category_taxonomy"><?php

foreach ( $category_taxonomy_select_vals as $taxonomy_key => $taxonomy_name ) {
?>    <option value="<?php echo $taxonomy_key; ?>"><?php echo $taxonomy_name; ?></option><?php
}
?>
</select><?php

You should send the four values to a processing script, which can output the final result by querying the value column of the soa_range_category_relationships table.

Hi, thanks for reply , but as i told you that value 26,30,45..etc are dynamic and will changes as per need ...and that are as per combination of 4 POINTS which i have listed above ..hence i can make table of above 4 points ...but that values ( 23,2680 ..etc ) which are coming as per 4 point combination cant be store in database as may changed ..and also its in excel which are in excel ...

above its just 7-8 output combination i have shown ..but its has more then thousands of combination if u see ..so its impossible for me to store each combination in database... also can you comment your create table ..which belong to my data ...so it will be easy for me to think as per your logic

Hey i have link excel file in question please see

Excel to PHP - MYSQL - Stack Overflow

php mysql excel phpexcel phpexcelreader
Rectangle 27 3

This is a blog post published in November 2008 that includes C# code to provide a solution.

From the intro on the blog post:

As Json is easier to read and write then Xml. It follows that CSV (comma seperated values) is easier to read and write then Json. CSV also has tools such as Excel and others that make it easy to work with and create. So if you ever want to create a config or data file for your next app, here is some code to convert CSV to JSON to POCO objects

thanks. ill have to port this to java, but better then trying to reinvent the wheel

glad it works, c# -> java is a pretty good match anyway.

converting CSV/XLS to JSON? - Stack Overflow

json csv xls
Rectangle 27 3

This is a blog post published in November 2008 that includes C# code to provide a solution.

From the intro on the blog post:

As Json is easier to read and write then Xml. It follows that CSV (comma seperated values) is easier to read and write then Json. CSV also has tools such as Excel and others that make it easy to work with and create. So if you ever want to create a config or data file for your next app, here is some code to convert CSV to JSON to POCO objects

thanks. ill have to port this to java, but better then trying to reinvent the wheel

glad it works, c# -> java is a pretty good match anyway.

converting CSV/XLS to JSON? - Stack Overflow

json csv xls
Rectangle 27 3

This is a blog post published in November 2008 that includes C# code to provide a solution.

From the intro on the blog post:

As Json is easier to read and write then Xml. It follows that CSV (comma seperated values) is easier to read and write then Json. CSV also has tools such as Excel and others that make it easy to work with and create. So if you ever want to create a config or data file for your next app, here is some code to convert CSV to JSON to POCO objects

thanks. ill have to port this to java, but better then trying to reinvent the wheel

glad it works, c# -> java is a pretty good match anyway.

converting CSV/XLS to JSON? - Stack Overflow

json csv xls
Rectangle 27 8

tested that this implementation didn't temper existing formula in the ...

I had a similar question regarding the interaction between excel and python (in particular, pandas), and I was referred to this question.

Thanks to some pointers by stackoverflow community, I found a package called xlwings that seems to cover a lot of the functionalities HaPsantran required.

To use the OP's example:

Working with an existing excel file, you can drop an anchor in the data block (Sheet3) you want to import to pandas by naming it in excel and do:

# opened an existing excel file
wb = Workbook(Existing_file)
# Find in the excel file a named cell and reach the boundary of the cell block (boundary defined by empty column / row) and read the cell
df = Range(Anchor).table.value
# import pandas and manipulate the data block
df = pd.DataFrame(df) # into Pandas DataFrame
df['sum'] = df.sum(axis= 1)

# write back to Sheet3
Range(Anchor).value = df.values

Let me know if this solves your problem and if there's anything I can help.

Big kudos to the developer of xlwings, they made this possible.

import xlwings as xw
import pandas as pd
target_df = xw.Range('A7').options(pd.DataFrame, expand='table').value # only do this if the 'A7' cell (the cell within area of interest) is in active worksheet
#otherwise do:
#sht = xw.Book(r'path to your xlxs file\name_of_file.xlsx`).sheets['name of sheet']
#target_df = sht.Range('A7').options(pd.DataFrame, expand='table').value # you can also change 'A7' to any name that you've given to a cell like 'interest_table`

I've been messing with this for a while and I'm having trouble understanding it. What is the anchor object? Is that the sheet name in the workbook? When df is declared, how is it linked to the Workbook object? I've been on xlwings documentation and not seeing anything useful for editing a single sheet in an excel anywhere.....

Sorry for not being clear, Anchor means the named cell in the worksheet. It should be a string like "Hero". I just thought it may be easier to name a cell and then access it in python quickly, instead of locating it via the column, row coordinates. This way your business logic get clearer as well. And the scope of my answer is more focused on editing a particular area on a specific spreadsheet.

Thanks for that @PaulDong, looks like another issue I was having was my xlwings was version 0.7 for some reason, even when I updated with pip. Had to manually install, now everything works great. Thanks for the detailed help!

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

python - Can Pandas read and modify a single Excel file worksheet (tab...

python excel pandas
Rectangle 27 2

This works fine in excel for both Windows and also Mac OS.

//Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, Greek letter and currency symbols.

function writeCSV($fileprefix, $headings, $data) {   

    //Use tab as field separator
    $newTab  = "\t";
    $newLine  = "\n";

    $fputcsv  =  count($headings) ? '"'. implode('"'.$newTab.'"', $headings).'"'.$newLine : '';

    // Loop over the * to export
    if (! empty($data)) {
      foreach($data as $item) {
        $fputcsv .= '"'. implode('"'.$newTab.'"', $item).'"'.$newLine;
      }
    }

    //Convert CSV to UTF-16
    $encoded_csv = mb_convert_encoding($fputcsv, 'UTF-16LE', 'UTF-8');

    // Output CSV-specific headers
    header('Set-Cookie: fileDownload=true; path=/'); //This cookie is needed in order to trigger the success window.
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename.csv\";" );
    header("Content-Transfer-Encoding: binary");
    header('Content-Length: '. strlen($encoded_csv));
    echo chr(255) . chr(254) . $encoded_csv; //php array convert to csv/excel

    exit;
}

How can I output a UTF-8 CSV in PHP that Excel will read properly? - S...

php csv utf-8 byte-order-mark
Rectangle 27 24

You can also use the TEXT() function quite easily (Note: source data must be an excel date value)

TEXT(value, format_text)

where value is the reference cell and format_text is how you want to format the text- in your case dd/mm/yyyy.

A1 = 3/17/2013
A2 = 12/27/2013
B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")

and the result should be

A              B
3/17/2013      17/03/2013
12/27/2013     27/12/2013

Split the string using mid(), left() and right() functions then check to see if the month mm is 1 or 2 characters long using the LEN() function. Finally concatenatr the string together using the & and / operators.

Try pasting this in B1, it should work fine:

This for some reason refuses to work on the "m/dd/yyyy" format as it isn't recognising it as a date at all. Therefore in your example "A1" remains 3/17/2013 whilst "A2" becomes 27/12/2013

for the TEXT() function to work the source data must be a date. Are you able to import the data using Import Data from Text (csv) and set the dates column as MDY? you can then use the TEXT() function

I tried this and it did not work. I think I need a code that will pull apart the date or string and put it back together as a date

=MID(A1,FIND("/",A1,1)+1,2)&"/"&IF(LEN(LEFT(A1,FIND("/",A1)-1))=1,0&LEFT(A1,FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))&"/"&RIGHT(A1,4)

The code in the main body of your answer works now once I convert the column into a string using David's macro. I don't know why this is. Crazy that I have to do this two steps to get the date as I want it! Thanks again

Excel Date Formatting - Stack Overflow

excel date
Rectangle 27 50

If anyone needs to do this in Excel without VBA, here is a way:

=SUBSTITUTE(ADDRESS(1;colNum;4);"1";"")

where colNum is the column number

And in VBA:

Function GetColumnName(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

+1 for providing a VBA alternative.

Yes, I use Excel in a locale where ; is used in place of , to separate function arguments in Excel. Thanks for pointing this out.

I did this without the TEXT function. What is the purpose of the TEXT function?

@dayuloli Long time since this answer was written, you are correct, the TEXT function does not serve a purpose here. Will update the answer.

c# - How to convert a column number (eg. 127) into an excel column (eg...

c# excel
Rectangle 27 35

I've created a class that exports a DataGridView or DataTable to an Excel file. You can probably change it a bit to make it use your DataSet instead (iterating through the DataTables in it). It also does some basic formatting which you could also extend.

To use it, simply call ExcelExport, and specify a filename and whether to open the file automatically or not after exporting. I also could have made them extension methods, but I didn't. Feel free to.

Note that Excel files can be saved as a glorified XML document and this makes use of that.

EDIT: This used to use a vanilla StreamWriter, but as pointed out, things would not be escaped correctly in many cases. Now it uses a XmlWriter, which will do the escaping for you.

The ExcelWriter class wraps an XmlWriter. I haven't bothered, but you might want to do a bit more error checking to make sure you can't write cell data before starting a row, and such. The code is below.

public class ExcelWriter : IDisposable
{
    private XmlWriter _writer;

    public enum CellStyle { General, Number, Currency, DateTime, ShortDate };

    public void WriteStartDocument()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
        _writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
         WriteExcelStyles();
   }

    public void WriteEndDocument()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
    }

    private void WriteExcelStyleElement(CellStyle style)
    {
        _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteEndElement();
    }

    private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
    {
        _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");

        _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
        _writer.WriteEndElement();

        _writer.WriteEndElement();

    }

    private void WriteExcelStyles()
    {
        _writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");

        WriteExcelStyleElement(CellStyle.General);
        WriteExcelStyleElement(CellStyle.Number, "General Number");
        WriteExcelStyleElement(CellStyle.DateTime, "General Date");
        WriteExcelStyleElement(CellStyle.Currency, "Currency");
        WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");

        _writer.WriteEndElement();
    }

    public void WriteStartWorksheet(string name)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
        _writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
    }

    public void WriteEndWorksheet()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public ExcelWriter(string outputFileName)
    {
        XmlWriterSettings settings = new XmlWriterSettings();
        settings.Indent = true;
        _writer = XmlWriter.Create(outputFileName, settings);
    }

    public void Close()
    {
        if (_writer == null) throw new InvalidOperationException("Already closed.");

        _writer.Close();
        _writer = null;
    }

    public void WriteExcelColumnDefinition(int columnWidth)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteValue(columnWidth);
        _writer.WriteEndAttribute();
        _writer.WriteEndElement();
    }

    public void WriteExcelUnstyledCell(string value)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
        _writer.WriteValue(value);
        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public void WriteStartRow()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
    }

    public void WriteEndRow()
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteEndElement();
    }

    public void WriteExcelStyledCell(object value, CellStyle style)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
        _writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
        _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
        switch (style)
        {
            case CellStyle.General:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
                break;
            case CellStyle.Number:
            case CellStyle.Currency:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
                break;
            case CellStyle.ShortDate:
            case CellStyle.DateTime:
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
                break;
        }
        _writer.WriteValue(value);
        //  tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\\-MM\\-dd\\THH\\:mm\\:ss\\.fff}</ss:Data>", value,

        _writer.WriteEndElement();
        _writer.WriteEndElement();
    }

    public void WriteExcelAutoStyledCell(object value)
    {
        if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");

        //write the <ss:Cell> and <ss:Data> tags for something
        if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
            value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
        {
            WriteExcelStyledCell(value, CellStyle.Number);
        }
        else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
        {
            WriteExcelStyledCell(value, CellStyle.Currency);
        }
        else if (value is DateTime)
        {
            //check if there's no time information and use the appropriate style
            WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
        }
        else
        {
            WriteExcelStyledCell(value, CellStyle.General);
        }
    }

    #region IDisposable Members

    public void Dispose()
    {
        if (_writer == null)
            return;

        _writer.Close();
        _writer = null;
    }

    #endregion
}

Then you can export your DataTable using the following:

public static void ExcelExport(DataTable data, String fileName, bool openAfter)
{
    //export a DataTable to Excel
    DialogResult retry = DialogResult.Retry;

    while (retry == DialogResult.Retry)
    {
        try
        {
            using (ExcelWriter writer = new ExcelWriter(fileName))
            {
                writer.WriteStartDocument();

                // Write the worksheet contents
                writer.WriteStartWorksheet("Sheet1");

                //Write header row
                writer.WriteStartRow();
                foreach (DataColumn col in data.Columns)
                    writer.WriteExcelUnstyledCell(col.Caption);
                writer.WriteEndRow();

                //write data
                foreach (DataRow row in data.Rows)
                {
                    writer.WriteStartRow();
                    foreach (object o in row.ItemArray)
                    {
                        writer.WriteExcelAutoStyledCell(o);
                    }
                    writer.WriteEndRow();
                }

                // Close up the document
                writer.WriteEndWorksheet();
                writer.WriteEndDocument();
                writer.Close();
                if (openAfter)
                    OpenFile(fileName);
                retry = DialogResult.Cancel;
            }
        }
        catch (Exception myException)
        {
            retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
        }
    }
}

-1: Don't attempt to generate XML using a TextWriter in this way. I'll just point out one obvious problem: if your DataTable contains a string value with angle brackets, the above code won't escape them properly.

Good point. Just goes to show I should stop to think a bit more. I've reworked it to use an XmlWriter. Theoretically, one should do a bit more error checking before releasing it in a public library, but if you are sure you'll call things in the right order you're "safe".

There is a mistake in the second part of the code. Instead of 'foreach (object o in dataTable.Rows)' should be 'foreach (object o in row.ItemArray)'.

Yes, yes it should - I just fixed it. Thanks for that.

lc, I've added some code to accept a DataSet and do a foreach on the tables to add worksheets. I also plan on organizing the code a little differently and fully documenting each method in MSDN XML format. Want me to send it to you?

c# (WinForms-App) export DataSet to Excel - Stack Overflow

c# winforms excel dataset export
Rectangle 27 1

A 'Table' inserted into an Excel Spreadsheet is not visible as a table...

The short version is:

You need a named range, or a named worksheet, or the full address of the 'table' data range and headers; and the apparent existence of a named range associated with an Excel table is misleading.

It's not a named range, it's not a sheet, and it cannot be resolved to an Excel Range object by your SQL. To recap that:

  • If you had renamed the worksheet to 'Table1', it would be visible to your SQL query as Table1$.
  • If you had created a named range, local to the scope of a worksheet named 'Sheet1', and named it 'Table1', it would be visible to your SQL as Sheet1$Table1.

An Excel 'Table' is none of those things, so it isn't visible as a table to the database engine.

Yes, it shows up in the 'Name Manager' dialog for named ranges: but 'Table1' is not the name of a named range, nor a worksheet, nor does it specify a range with an address recognisable to SQL: and it needs to be one of those three things in order to be visible as a database table to the OLEDB and ODBC database drivers available for Excel.

The object you've created as 'Table1' is a ListObject, and you can interrogate it in the Excel VBA IDE's debug window as:

Thisworkbook.Worksheets("Sheet1").ListObjects("Table1").Name

So what you need is the address of the ListObject's range, and that's a simple matter of...

...The ListObject has a DataBodyRange property, which covers the rows of data below the header; and a HeaderRowRange which refers to the headers. Both, being ranges, have an address; and you'd need to UNION them to get a single address that your SQL can read as a named table.

Or use Sheet1.ListObjects("Table1").Sort.Rng.address because every ListObject has a Sort object property, whether it's sorted or not; and the Rng property is the entire range, with a cavalier disregard for consistent object names and no hint as to the column or columns' address being sorted.

...And that information is useless for your question and your coding: there's nothing you can put into an SQL query that will parse the 'Table' object as a valid table.

You have to extract and parse the address of the full 'table' range before you run the SQL. The ADO and ODBC Excel drivers will work on a contiguous range address of the form 'SheetName$A1:Z1024' but they have no ability to extract that address from the parent workbook of the Excel 'Table' object.

By the way, you do know that querying Excel with any of the available ODBC and OLEDB SQL drivers gives you a memory leak? At least, it does if you do it from VBA: I would advise you not to run this repeatedly unless you run Purify or a similar tool on your code and isolate the problem.

oledb - ADO: Excel: Is it possible to open recordset on table name? - ...

excel-vba oledb ado recordset adoconnection
Rectangle 27 1

This works fine in excel for both Windows and also Mac OS.

//Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, Greek letter and currency symbols.

function writeCSV($fileprefix, $headings, $data) {   

    //Use tab as field separator
    $newTab  = "\t";
    $newLine  = "\n";

    $fputcsv  =  count($headings) ? '"'. implode('"'.$newTab.'"', $headings).'"'.$newLine : '';

    // Loop over the * to export
    if (! empty($data)) {
      foreach($data as $item) {
        $fputcsv .= '"'. implode('"'.$newTab.'"', $item).'"'.$newLine;
      }
    }

    //Convert CSV to UTF-16
    $encoded_csv = mb_convert_encoding($fputcsv, 'UTF-16LE', 'UTF-8');

    // Output CSV-specific headers
    header('Set-Cookie: fileDownload=true; path=/'); //This cookie is needed in order to trigger the success window.
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename.csv\";" );
    header("Content-Transfer-Encoding: binary");
    header('Content-Length: '. strlen($encoded_csv));
    echo chr(255) . chr(254) . $encoded_csv; //php array convert to csv/excel

    exit;
}

How can I output a UTF-8 CSV in PHP that Excel will read properly? - S...

php csv utf-8 byte-order-mark
Rectangle 27 1

This works fine in excel for both Windows and also Mac OS.

//Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, Greek letter and currency symbols.

function writeCSV($fileprefix, $headings, $data) {   

    //Use tab as field separator
    $newTab  = "\t";
    $newLine  = "\n";

    $fputcsv  =  count($headings) ? '"'. implode('"'.$newTab.'"', $headings).'"'.$newLine : '';

    // Loop over the * to export
    if (! empty($data)) {
      foreach($data as $item) {
        $fputcsv .= '"'. implode('"'.$newTab.'"', $item).'"'.$newLine;
      }
    }

    //Convert CSV to UTF-16
    $encoded_csv = mb_convert_encoding($fputcsv, 'UTF-16LE', 'UTF-8');

    // Output CSV-specific headers
    header('Set-Cookie: fileDownload=true; path=/'); //This cookie is needed in order to trigger the success window.
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename.csv\";" );
    header("Content-Transfer-Encoding: binary");
    header('Content-Length: '. strlen($encoded_csv));
    echo chr(255) . chr(254) . $encoded_csv; //php array convert to csv/excel

    exit;
}

How can I output a UTF-8 CSV in PHP that Excel will read properly? - S...

php csv utf-8 byte-order-mark
Rectangle 27 1

This works fine in excel for both Windows and also Mac OS.

//Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, Greek letter and currency symbols.

function writeCSV($fileprefix, $headings, $data) {   

    //Use tab as field separator
    $newTab  = "\t";
    $newLine  = "\n";

    $fputcsv  =  count($headings) ? '"'. implode('"'.$newTab.'"', $headings).'"'.$newLine : '';

    // Loop over the * to export
    if (! empty($data)) {
      foreach($data as $item) {
        $fputcsv .= '"'. implode('"'.$newTab.'"', $item).'"'.$newLine;
      }
    }

    //Convert CSV to UTF-16
    $encoded_csv = mb_convert_encoding($fputcsv, 'UTF-16LE', 'UTF-8');

    // Output CSV-specific headers
    header('Set-Cookie: fileDownload=true; path=/'); //This cookie is needed in order to trigger the success window.
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename.csv\";" );
    header("Content-Transfer-Encoding: binary");
    header('Content-Length: '. strlen($encoded_csv));
    echo chr(255) . chr(254) . $encoded_csv; //php array convert to csv/excel

    exit;
}

How can I output a UTF-8 CSV in PHP that Excel will read properly? - S...

php csv utf-8 byte-order-mark
Rectangle 27 37

You can output the data as html table cells, stick a .xls or .xlsx extension on it, and Excel will open it as if it were a native document. You can even do some limited formatting and formula calculations this way, so it's much more powerful than CSV. Also, outputting an html table ought to be pretty easy to do from a web platform like ASP.Net ;)

If you need multiple worksheets or named worksheets within your Excel Workbook, you can do something similar via an XML schema called SpreadSheetML. This is not the new format that shipped with Office 2007, but something completely different that works as far back as Excel 2000. The easiest way to explain how it works is with an example:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?> 
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>Your_name_here</Author>
      <LastAuthor>Your_name_here</LastAuthor>
      <Created>20080625</Created>
      <Company>ABC Inc</Company>
      <Version>10.2625</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<Styles>
      <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Bottom" />
            <Borders />
            <Font />
            <Interior />
            <NumberFormat />
            <Protection />
      </Style>
</Styles>

<Worksheet ss:Name="Sample Sheet 1">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table1">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
      <Cell><Data ss:Type="Number">1</Data></Cell>
      <Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">3</Data></Cell>
      <Cell><Data ss:Type="Number">4</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">5</Data></Cell>
      <Cell><Data ss:Type="Number">6</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="Number">7</Data></Cell>
      <Cell><Data ss:Type="Number">8</Data></Cell>
</Row>
</Table>
</Worksheet>

<Worksheet ss:Name="Sample Sheet 2">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table2">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
      <Cell><Data ss:Type="String">A</Data></Cell>
      <Cell><Data ss:Type="String">B</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">C</Data></Cell>
      <Cell><Data ss:Type="String">D</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">E</Data></Cell>
      <Cell><Data ss:Type="String">F</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type="String">G</Data></Cell>
      <Cell><Data ss:Type="String">H</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

you could rename the file with an .xml extensio and add this: <?mso-application progid="Excel.Sheet"?> just after the <?xml version="1.0"?> this way windows recognize that the file is an Excel file, will give it the right icon, will open excel when you click the file and Excel will not complain that the file format and contents do not match. bye.

@pomarc The downside to that is that other programs that import excel files won't recognize it. But then, they probably wouldn't parse the xml anyway.

I have used this technique quite successfully. This would be my recommendation - dirt simple and very effective.

Two potential issues (YMMV) with HTML tabular data masked as XLS file: (1) Microsoft Excel will trim leading spaces and zeroes automatically; and (2) Microsoft Excel 2010 warns the user when opening an XLS file containing HTML tabular data. The solution to #1 seems to be creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel (if leading spaces/zeroes are significant and need to be retained).

Just for clarification, while I did mention HTML tables, the main point of this answer is SpreadsheetML, which is more than just HTML tabular data. Excel sees it as native.

vb.net - Generating an Excel file in ASP.NET - Stack Overflow

asp.net vb.net export-to-excel
Rectangle 27 31

It's worth pointing out that if you have an Excel 2007 (xlsm) file, then you can simply save it as an Excel 2003 (xls) file and use the methods outlined in other answers.

that is not true, I've worked with files for which conversion to xls/xla from xlsm was impossible, Excel 2007 and 2010 crashed each time, I've tried various instances, from one erros message - Kod wyjtku: c0000005 Przesunicie wyjtku: 005d211d

YES you can do it. I've done it many times. If there is something on sheets which is necessary and what is not transferred to the older version I do this: 1. convert .xlsm to .xls 2. crack the code of .xls 3. convert .xlsm to .xlsx 4. Put the code from modules in .xls to .xlsx and save that as .xlsm

It works after converting xlsm to xls as in the answer.

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords
Rectangle 27 31

It's worth pointing out that if you have an Excel 2007 (xlsm) file, then you can simply save it as an Excel 2003 (xls) file and use the methods outlined in other answers.

that is not true, I've worked with files for which conversion to xls/xla from xlsm was impossible, Excel 2007 and 2010 crashed each time, I've tried various instances, from one erros message - Kod wyjtku: c0000005 Przesunicie wyjtku: 005d211d

YES you can do it. I've done it many times. If there is something on sheets which is necessary and what is not transferred to the older version I do this: 1. convert .xlsm to .xls 2. crack the code of .xls 3. convert .xlsm to .xlsx 4. Put the code from modules in .xls to .xlsx and save that as .xlsm

It works after converting xlsm to xls as in the answer.

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords