Javascript | clear input file value = file url

Included javascript function for clearing INPUT FILE contents!
I did it for disable uploading NON excel file (.xls).
(a message should be include...)

The CODE for ASP.NET:

<asp:FileUpload ID="txtFile" runat="server"
Enabled="true"
onchange="if (this.value.endsWith('.xls') == false) { this.parentNode.innerHTML = this.parentNode.innerHTML; }" />

The CODE HTML:

<input type="file" onchange="if (this.value.endsWith('.xls') == false) { this.parentNode.innerHTML = this.parentNode.innerHTML; }" style="width:216px;" />

Enjoy...
:)

Excel - Copy table to one column

This article copy table to one column, i.e. convert multiple columns into one column!
(after the operation, it also allows to remove empty cells)
also allow to convert multiple columns into more than one column!

This tutorial tested and found working on:
* Microsoft Office Excel 2003 with help of: Microsoft Office Word 2003
* Microsoft Office Excel 2007 with help of: Microsoft Office Word 2007


Instructions (WORKING TUTORIAL):
1. we have an excel file that contains table with data.
2. select all table data and press COPY (Ctrl + C)
3. open new Word document (Tested on Microsoft Office Word 2003)
4. PASTE (Ctrl + V) the copied table into word document.
5. Select the table (in word document) and select MENU: Table > Convert > Table to Text
[Microsoft Office Word 2007: Data (or View, should be last one) Group > Convert to Text]
Table selection using     icon
6. Set a character that not exists in the table (for example |)
7. Keep the text selected and press on MENU: Table > Convert > Text to Table
[Microsoft Office Word 2007: Insert Group > Table > Convert Text to Table]
8. Set number of columns = 1
9. Notice that the character is still | (that we selected)
10. Press OK
11. Now we have table with 1 column (and it is selected!)
12. Select entire table and press COPY (Ctrl + C)
Table selection using     icon
13. Open new EXCEL file (or use the original file)
14. PASTE (Ctrl + V) the table.
15. NOW you have 1 Column table!!!

Hope it helped you...
:)

C# | Asp.Net | Convert DataTable to Excel File

C# | Asp.Net | Convert DataTable to Excel File

Here is an example, to convert fro existing DataTable to Excel file

Working fine to me...
Tested on Excel 2003 and partially on Excel 2007 :)

///

/// Converts excel file sheet content into DataTable.
///

///
///
///
public static DataTable ExcelFileToDataTable(string p_fileUrl, int p_sheetIndex)
{
DataTable dbSchema = new DataTable();
DataSet dataSet = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_fileUrl + ";" + "Extended Properties=Excel 8.0;Mode=Read;";

using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

// Get all sheetnames from an excel file into data table
dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string sheetName = dbSchema.Rows[p_sheetIndex]["TABLE_NAME"].ToString();
string selectCommandText = string.Format("SELECT * FROM [{0}]", sheetName);

using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommandText, conn))
{
adapter.Fill(dataSet);
}

return dataSet.Tables[0];
}
}

///

/// Converts DataTable to Excel-File
///

///
///
///
///
public static bool DataTableToExcelFile(DataTable p_dt, string p_filePath, EventHandler p_onRowHandled)
{
try
{
// fix table name
if (p_dt.TableName == "" || p_dt.TableName.Equals("Table", StringComparison.OrdinalIgnoreCase) == true)
{
p_dt.TableName = "NONAME";
}

// fix file path name
if (p_filePath.EndsWith(".xls") == false)
{
p_filePath += ".xls";
}

// insure file not exist!
System.IO.FileInfo fi = new System.IO.FileInfo(p_filePath);
if (fi.Name.Replace(".xls", "").Trim().Length <= 0)
{
p_filePath = p_filePath.Replace(".xls", p_dt.TableName + ".xls");
}
if (System.IO.File.Exists(p_filePath))
{
System.IO.File.Delete(p_filePath);
}

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_filePath + ";Extended Properties=Excel 8.0"))
{
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = con;
con.Open();

// create table
command.CommandText = GenerateSqlStatementCreateTable(p_dt);
command.ExecuteNonQuery();

DataRow dr;
OleDbParameter oleDbParameter;

// Create columns & values (as parameters) script
string columns, parameters;
GenerateColumnsString(p_dt, out columns, out parameters);

for (int i = 0; i < p_dt.Rows.Count; i++)
{
dr = p_dt.Rows[i];

// set insert statement parameters
command.Parameters.Clear();
for (int j = 0; j < p_dt.Columns.Count; j++)
{
oleDbParameter = new OleDbParameter();
oleDbParameter.ParameterName = "@p" + j;

if (dr.IsNull(j) == true)
{
oleDbParameter.Value = DBNull.Value;
}
else
{
oleDbParameter.Value = dr[j];
}
command.Parameters.Add(oleDbParameter);
}

command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", p_dt.TableName, columns, parameters);
command.ExecuteNonQuery();

// Perform step...
if (p_onRowHandled != null)
{
p_onRowHandled(p_dt, EventArgs.Empty);
}
}
}

con.Close();
}
return true;
}
catch (Exception ex)
{
throw (ex);
}
}


///

/// Generates columns names string delimited by commas
/// also supply parameters for the given columns, for example:
///
/// out string p_columns = [columnname0],[columnname1],[columnname2]
/// out string p_params = @p0, @p1, @p2
///
///

///
///
///
private static void GenerateColumnsString(DataTable p_dt, out string p_columns, out string p_params)
{
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
for (int i = 0; i < p_dt.Columns.Count; i++)
{
if (i != 0)
{
sbColumns.Append(',');
sbParams.Append(',');
}
sbColumns.AppendFormat("[{0}]", p_dt.Columns[i].ColumnName);
sbParams.AppendFormat("@p{0}", i);
}

p_columns = sbColumns.ToString();
p_params = sbParams.ToString();
}

///

/// Create SQL-Script for creating table which represent the given DataTable
///

///
///
private static string GenerateSqlStatementCreateTable(DataTable p_dt)
{
StringBuilder sbCreateTable = new StringBuilder();

DataColumn dc;
sbCreateTable.AppendFormat("CREATE TABLE {0} (", p_dt.TableName);
for (int i = 0; i < p_dt.Columns.Count; i++)
{
dc = p_dt.Columns[i];
if (i != 0)
{
sbCreateTable.Append(",");
}

string dataType = dc.DataType.Equals(typeof(double)) ? "DOUBLE" : "NVARCHAR";

sbCreateTable.AppendFormat("[{0}] {1}", dc.ColumnName, dataType);
}
sbCreateTable.Append(")");

return sbCreateTable.ToString();
}