网页导出EXCEL,调用该方法 前台会下载EXCEL
/// <summary>
/// Excle导出数据
/// </summary>
/// <typeparam name="T">类对象<\typeparam>
/// <param name="list">对象数据</param>
/// <param name="column">类字段,字段对应列名</param>
/// <param name="filename">excel表名</param>
public void OutExcel<T>(List<T> list, Dictionary<string, string> column, string filename)
{
if (list == null || list.Count == 0 || column == null || column.Count == 0)
{
return;
}
StringWriter sw = new StringWriter();
//-------------------------------表头读取开始------------------------------------------------
string title = string.Empty;
foreach (KeyValuePair<string, string> kvp in column)
{
title += kvp.Value + "\t";
}
title = title.Substring(0, title.LastIndexOf("\t"));
sw.WriteLine(title);
//-------------------------------表头读取结束--------------------------------------------------------
//--------------------------------数据读取start----------------------------------------------------------------------------------
Type objType = typeof(T);
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识
PropertyInfo[] propInfoArr = objType.GetProperties(bf); //获取映射列表
foreach (T model in list)
{
System.Text.StringBuilder data = new System.Text.StringBuilder();
foreach (string key in column.Keys)
{
foreach (PropertyInfo propInfo in propInfoArr)
{
if (key == propInfo.Name)//判断头相对应的字段
{
PropertyInfo modelProperty = model.GetType().GetProperty(propInfo.Name);
if (modelProperty != null)
{
object objResult = modelProperty.GetValue(model, null);//获取值
data.Append(((objResult == null) ? string.Empty : objResult) + "\t");
}
}
}
}
var temp = data.ToString();
temp = temp.Substring(0, temp.LastIndexOf("\t"));
sw.WriteLine(temp);
}
//------------------------------------------end----------------------------------------------------------------------------------
sw.Close();//读取数据结束
//-----------------------------------输出excel-------------------------------------------------------------
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
//-------------------------------------------------------------------------------------------------------------
}
调用例子
List<easy_CX> es = a.ToObjectFromJson<List<easy_CX>>();
Dictionary<string, string> mydict = new Dictionary<string, string>();
OutExcel(es,mydict,"all_down");
DataGridview中的数据导出EXCEL
public bool ExportDataGridview(DataGridView gridView, bool isShowExcle)
{
if (gridView.Rows.Count == 0)
{
return false;
}
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//生成字段名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < gridView.RowCount - 1; i++) //循环行
{
for (int j = 0; j < gridView.ColumnCount; j++) //循环列
{
if (gridView[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + gridView.Rows[i].Cells[j].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
}
}
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Save("c:\\321.xls");
excel.Quit();
return true;
}
把DataTable导出为EXCEL
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Application xlApp = new Excel.Application();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = "'" + tmpDataTable.Rows[i][j].ToString();
}
}
//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
xlBook.SaveCopyAs(strFileName);
xlBook.Close(false);
}
将EXCEL转换为DataTable 之后存入数据库常用于批量导入
// <summary>
/// 将Excel文件导出至DataTable(第一行作为表头)
/// </summary>
/// <param name="ExcelFilePath">Excel文件路径</param>
/// <param name="TableName">数据表名,如果数据表名错误,默认为第一个数据表名</param>
public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
{
if (!File.Exists(ExcelFilePath))
{
throw new Exception("Excel文件不存在!");
}
//如果数据表名不存在,则数据表名为Excel文件的第一个数据表
ArrayList TableList = new ArrayList();
TableList = GetExcelTables(ExcelFilePath);
if (TableName.IndexOf(TableName) < 0)
{
TableName = TableList[0].ToString().Trim();
}
DataTable table = new DataTable();
OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
adapter.Fill(table);
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (dbcon.State == ConnectionState.Open)
{
dbcon.Close();
}
}
return table;
}
public static ArrayList GetExcelTables(string ExcelFileName)
{
DataTable dt = new DataTable();
ArrayList TablesList = new ArrayList();
if (File.Exists(ExcelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
{
try
{
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
}
catch (Exception exp)
{
throw exp;
}
//获取数据表个数
int tablecount = dt.Rows.Count;
for (int i = 0; i < tablecount; i++)
{
string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
if (TablesList.IndexOf(tablename) < 0)
{
TablesList.Add(tablename);
}
}
}
}
return TablesList;
}