About Me

My photo
Chennai, TamilNadu, India
Thank you.. Viewers wishing you all the best
Powered By Blogger

Import and Export Excel files

How to Import and Export Excel, Csv and Txt files.
1)   We cannot import excel file directly from uploaded control. Used to store external like Application folder (“App_Data”) or Database.
2)  But we can import Csv and Txt files directly from uploaded control. Using Stream Reader.

1)      Using OleDbConnection to import excel file.

Getting Server Path:
   string Server Paths = Path.Combine(Server.MapPath("~/UploadFile/"));

Get Up loader control Objects :
            HttpPostedFileBase postedFile = Request.Files[file] as HttpPostedFileBase;
Get FileName from Uploader controls :
 string fileName = Path.GetFileName(postedFile.FileName);

Saving App folder path :
  SaveFiles = ServerPaths + "\\" + fileName;
     Request.Files[file].SaveAs(SaveFiles);
Connection String:
          @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + SaveFiles + "';
             Extended Properties= 'Excel 12.0;HDR=Yes;IMEX=1'"

OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [Sheet1$]", con);
          DataTable dt = new DataTable();
          con.Open();
          OleDbDataAdapter Ada = new OleDbDataAdapter(cmd);
          Ada.Fill(dt);
          con.Close();

   Result:
      We get a value from datatable.
      
2)
        DataTable dtTemp = new DataTable();
        StreamReader reader = new StreamReader(postedFile.InputStream);
        string text = reader.ReadToEnd();
        var getHeader = text.Split('\r');
        var getColumnsHeader = (getHeader[0]).Split(',');
        foreach (var header in getColumnsHeader)
          dtTemp.Columns.Add(header.ToString());
         for (int index = 1; index < getHeader.Length; index++)
                     {
                        var getVs = (getHeader[index]).Split(',');
                        dtTemp.Rows.Add(getVs);
                     }
                     dtGetUploadedfile = GetTable(dtTemp);


Export Excel File.

MemoryStream stream = ExcelUtility.GetExcel(dt);
var contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.Clear();
                    Response.ContentType = contenttype;
                    Response.AddHeader("content-disposition", "attachment;filename=" + "CheckList_Questions.xlsx");
                    Response.Cache.SetCacheability(HttpCacheability.NoCache);
                    Response.BinaryWrite(stream.ToArray());
                    Response.End();
                byte[] buffer = System.Text.Encoding.UTF8.GetBytes(stream.ToString());
                    return Json(buffer, "text/html", JsonRequestBehavior.AllowGet);


Create Separate class file.
public class ExcelUtility
{
        // Get the excel column letter by index
        public static string ColumnLetter(int intCol)
        {
            int intFirstLetter = ((intCol) / 676) + 64;
            int intSecondLetter = ((intCol % 676) / 26) + 64;
            int intThirdLetter = (intCol % 26) + 65;

            char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
            char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
            char ThirdLetter = (char)intThirdLetter;

            return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
        }

        // Create a text cell
        private static Cell CreateTextCell(string header, UInt32 index, string text)
        {
            var cell = new Cell
            {
                DataType = CellValues.InlineString,
                CellReference = header + index
            };
            var istring = new InlineString();
            var t = new Text { Text = text };
            istring.Append(t);
            cell.Append(istring);
            return cell;
        }

        public static MemoryStream GetExcel(DataTable data)
        {
            string[] fieldsToExpose = new string[data.Columns.Count];
            for (int i = 0; i < data.Columns.Count; i++)
            {
                fieldsToExpose[i] = data.Columns[i].ColumnName;
            }

            return GetExcel(fieldsToExpose, data);
        }

        public static MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            UInt32 rowcount = 0;

            // Create the Excel document
     var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            var workbookPart = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var relId = workbookPart.GetIdOfPart(worksheetPart);

            var workbook = new Workbook();
      var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();

            // Add header to the sheet
            var row = new Row { RowIndex = ++rowcount };
            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();

            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row { RowIndex = ++rowcount };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount,
                        dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();

            document.Close();
            return stream;
        }
    }



No comments:

Post a Comment