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'"
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