Download "DocumentFormat.OpenXml.dll" from website and write following code
protected void btnimport_Click(object sender, EventArgs e)
{
try
DataTable dt = new DataTable();
if (fu_File.PostedFile != null)
{
string strtimeStamp = string.Empty;
strtimeStamp = System.DateTime.Now.Ticks.ToString();
string fileName = Server.MapPath("~/UploadFile/") + strtimeStamp + "_" + fu_File.FileName;
fu_File.SaveAs(fileName);
string result = obj.GetXML(fileName);
// Check pre-PGI Dt
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlNode root = doc.FirstChild;
//Display the contents of the child nodes.
if (root.HasChildNodes)
{
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SaveOrderList";
cmd.Parameters.Add("@xmlData", SqlDbType.NVarChar).Value = result;
cmd.Parameters.Add("@UploadedBy", SqlDbType.NVarChar).Value = 1;
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
lblMessage.Text = "Record has been successfully inserted";
}
catch (Exception ex)
{
throw ex;
}
}
else
{
lblMessage.Text = "No Record has been available for insert";
}
}
}
catch (Exception ex)
{
lblMessage.Text = "! Error:" + ex.Message;
}
}
protected void btnexport_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("GetOrderList", con);
cmd.CommandType = CommandType.StoredProcedure;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
DataTable table = new DataTable();
table = ds.Tables[0];
obj.DataTableToExcel(table);
}
catch (Exception ex)
{
lblMessage.Text = "Error: " + ex.Message;
}
}
Add class
public class ConvertExcelToXml
{
/// <summary>
/// Read Data from selected excel file into DataTable
/// </summary>
/// <param name="filename">Excel File Path</param>
/// <returns></returns>
public DataTable ReadExcelFile(string filename)
{
// Initialize an instance of DataTable
DataTable dt = new DataTable();
try
{
// Use SpreadSheetDocument class of Open XML SDK to open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Get all sheets in spread sheet document
IEnumerable<Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
// Get relationship Id
string relationshipId = sheetcollection.First().Id.Value;
// Get sheet1 Part of Spread Sheet Document
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
// Get Data in Excel file
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
IEnumerable<Row> rowcollection = sheetData.Descendants<Row>();
if (rowcollection.Count() == 0)
{
return dt;
}
// Add columns
foreach (Cell cell in rowcollection.ElementAt(0))
{
string CellText = GetValueOfCell(spreadsheetDocument, cell);
CellText = CellText.Replace(" ", "O"); // All space of excel sheet header replaced by 'O'
while (true)
{
if (dt.Columns.IndexOf(CellText) >= 0)
{
CellText += "_1";
continue;
}
break;
}
dt.Columns.Add(CellText);
}
dt.Columns.Add("Last");
//foreach (Cell cell in rowcollection.ElementAt(0))
//{
// dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
//}
// Add rows into DataTable
foreach (Row row in rowcollection)
{
DataRow temprow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
// Get Cell Column Index
int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
temprow[columnIndex] = string.Empty;
columnIndex++;
}
while (columnIndex < cellColumnIndex);
}
temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell);
columnIndex++;
}
// Add the row to DataTable
// the rows include header row
dt.Rows.Add(temprow);
}
}
// Here remove header row
dt.Rows.RemoveAt(0);
return dt;
}
catch (IOException ex)
{
throw new IOException(ex.Message);
}
}
/// <summary>
/// Get Value of Cell
/// </summary>
/// <param name="spreadsheetdocument">SpreadSheet Document Object</param>
/// <param name="cell">Cell Object</param>
/// <returns>The Value in Cell</returns>
private static string GetValueOfCell(SpreadsheetDocument spreadsheetdocument, Cell cell)
{
// Get value in Cell
SharedStringTablePart sharedString = spreadsheetdocument.WorkbookPart.SharedStringTablePart;
if (cell.CellValue == null)
{
return string.Empty;
}
string cellValue = cell.CellValue.InnerText;
// The condition that the Cell DataType is SharedString
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return sharedString.SharedStringTable.ChildElements[int.Parse(cellValue)].InnerText;
}
else
{
return cellValue;
}
}
/// <summary>
/// Get Column Name From given cell name
/// </summary>
/// <param name="cellReference">Cell Name(For example,A1)</param>
/// <returns>Column Name(For example, A)</returns>
private string GetColumnName(string cellReference)
{
// Create a regular expression to match the column name of cell
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
/// <summary>
/// Get Index of Column from given column name
/// </summary>
/// <param name="columnName">Column Name(For Example,A or AA)</param>
/// <returns>Column Index</returns>
private int GetColumnIndex(string colName)
{
string columnName = colName.ToLowerInvariant();
int columnIndex = 0;
//int factor = 1;
//// From right to left
//for (int position = columnName.Length - 1; position >= 0; position--)
//{
// // For letters
// if (Char.IsLetter(columnName[position]))
// {
// columnIndex += factor * ((columnName[position] - 'A') + 1) - 1;
// factor *= 26;
// }
//}
string s = "abcdefghijklmnopqrstuvwxyz";
if (columnName.Length == 1)
{
columnIndex = s.IndexOf(columnName);
}
else
{
char[] parts = new char[] { columnName[0], columnName[1] };
columnIndex = s.IndexOf(parts[0]);
columnIndex += (26 * (s.IndexOf(parts[0]) + 1) + s.IndexOf(parts[1]));
}
return columnIndex;
}
/// <summary>
/// Convert DataTable to Xml format
/// </summary>
/// <param name="filename">Excel File Path</param>
/// <returns>Xml format string</returns>
public string GetXML(string filename)
{
try
{
DataTable dt = new DataTable();
dt = this.ReadExcelFile(filename);
int ColIndex = dt.Columns.IndexOf("pre-PGIODt");
dt.Rows.Cast<DataRow>().Where(r => Convert.ToString(r.ItemArray[ColIndex]).Length == 0).ToList().ForEach(r => r.Delete());// Delete Blank Rows (QC is empty);
DataTable dtMain = dt.DefaultView.ToTable(false, "Plnt", "SalesODoc.", "NameO1", "CustomerOpurchaseOorderOno", "Description", "OOOrderOqty", "Delivery", "Ac.GIOdate", "pre-PGIODt", "OOOOOOWBSOElement");
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dtMain);
return ds.GetXml();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// convert datatable into excel file.
/// </summary>
/// <param name="dt"></param>
public void DataTableToExcel(DataTable dt)
{
DataView dataView = ConvertToDataView(dt);
string theString = string.Empty;
ASCIIEncoding objEncoding = null;
HttpResponse objHttpResponse = null;
int i = 0;
objHttpResponse = HttpContext.Current.Response;
objHttpResponse.Clear();
objHttpResponse.AddHeader("Content-Disposition", ("attachment;filename=" + "SLIRequest.xls"));
objHttpResponse.ContentType = "application/ms-excel";
if ((dataView != null) && (dataView.Table.Rows.Count > 0))
{
theString = ConvertDataViewToString(dataView, "", "\t");
theString = theString.Replace('"', ' ');
}
if (theString.Length <= 0)
{
theString = "Data not found.";
}
objEncoding = new ASCIIEncoding();
i = objEncoding.GetByteCount(theString);
objHttpResponse.AddHeader("Content-Length", i.ToString());
objHttpResponse.BinaryWrite(objEncoding.GetBytes(theString));
objHttpResponse.Charset = "";
objHttpResponse.End();
}
/// <summary>
/// convert datatable to dataview
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
DataView ConvertToDataView(DataTable dt)
{
DataView dv = new DataView(dt);
return dv;
}
/// <summary>
/// convert dataview to string
/// </summary>
/// <param name="srcDataView"></param>
/// <param name="Delimiter"></param>
/// <param name="Separator"></param>
/// <returns></returns>
public static string ConvertDataViewToString(DataView srcDataView, string Delimiter, string Separator)
{
StringBuilder objStringBuilder = new StringBuilder();
objStringBuilder.Length = 0;
foreach (DataColumn column in srcDataView.Table.Columns)
{
if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
{
objStringBuilder.Append(Delimiter);
}
objStringBuilder.Append(column.ColumnName);
if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
{
objStringBuilder.Append(Delimiter);
}
objStringBuilder.Append(Separator);
}
if (objStringBuilder.Length > Separator.Trim().Length)
{
objStringBuilder.Length -= Separator.Trim().Length;
}
objStringBuilder.Append(Environment.NewLine);
foreach (DataRowView theDataRowView in srcDataView)
{
foreach (DataColumn theDataColumn2 in srcDataView.Table.Columns)
{
if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
{
objStringBuilder.Append(Delimiter);
}
objStringBuilder.Append(RuntimeHelpers.GetObjectValue(theDataRowView[theDataColumn2.ColumnName]));
if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
{
objStringBuilder.Append(Delimiter);
}
objStringBuilder.Append(Separator);
}
objStringBuilder.Length--;
objStringBuilder.Append("\r\n");
}
if (objStringBuilder != null)
{
return objStringBuilder.ToString();
}
else
{
return string.Empty;
}
}
}
