1.把List集合转成DataTable
当没有实体对象时
//列名集合
string[] strs = obj["selectStr"].ToString().Split(',');
//ToDataTableTow(集合,列名);
DataTable dt = iDataTableExtensions.ToDataTableTow(DocumentList, strs);
string path = iExcel.ExportExecl(cols, dt);
iExcel.ExportExcel(path);
/// 将List转换成DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
///
public static class iDataTableExtensions
{
/// <summary>
/// list转dt
/// </summary>
/// <param name="list">list数据</param>
/// <param name="strs">需要的列名</param>
/// <returns></returns>
public static DataTable ToDataTableTow(IList list, string[] strs)
{
DataTable result = new DataTable("Datas");
if (list.Count > 0)
{
//循环创建列头
foreach (var str in strs)
{
result.Columns.Add(str, Type.GetType("System.String"));
}
//循环创建内容
foreach (object item in list)
{
JObject jobj = JObject.FromObject(item);
ArrayList tempList = new ArrayList();
//循环需要的列名
foreach (var str in strs)
{
object obj = jobj[str];
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
}
}
2.导出帮助类
public class excelCell
{
public string location;
public object value;
public excelCell(string _location, object _value)
{
location = _location;
value = _value;
}
}
public class Cols
{
public Cols() { }
public Cols(string Caption, string ColumnName,int Width)
{
this.Caption = Caption;
this.ColumnName = ColumnName;
this.Width = Width;
}
public string Caption { set; get; }
public string ColumnName { set; get; }
public int Width { set; get; }
}
public class iExcel
{
public static string ExportExecl(Cols[] cols, DataTable dt)
{
string dir = iPublic.ProjectPath(string.Format("/Upload/{0}/", "xlsx"));
if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);
string path = string.Format("/Upload/{0}/{0}", "xlsx") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//匹配标题
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < cols.Length; j++)
{
if (dt.Columns[i].ColumnName == cols[j].ColumnName)
{
dt.Columns[i].Caption = cols[j].Caption;
sheet.SetColumnWidthInPixels(i + 1, cols[j].Width);
break;
}
}
}
sheet.InsertDataTable(dt, true, 1, 1);
sheet.Rows[0].Style.Color = System.Drawing.Color.LightBlue;
sheet.Rows[0].RowHeight = 18;
sheet.Rows[0].Style.Font.Size = 12;
using (MemoryStream ms = new MemoryStream())
{
workbook.SaveToStream(ms, FileFormat.Version2010);
}
workbook.SaveToFile(iPublic.ProjectPath(path), ExcelVersion.Version2007);
return dir.Replace("Upload\\xlsx\\", "") + path;
}
/// <summary>
/// 生成Excel
/// </summary>
public static void CreateExcel(DataTable dt, string path)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);//创建工作表
#region 标题
IRow row = sheet.CreateRow(0);//在工作表中添加一行
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);//在行中添加一列
cell.SetCellValue(dt.Columns[i].ColumnName);//设置列的内容
}
#endregion
#region 填充数据
for (int i = 1; i <= dt.Rows.Count; i++)//遍历DataTable行
{
DataRow dataRow = dt.Rows[i - 1];
row = sheet.CreateRow(i);//在工作表中添加一行
for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列
{
ICell cell = row.CreateCell(j);//在行中添加一列
cell.SetCellValue(dataRow[j].ToString());//设置列的内容
}
}
#endregion
#region 输出到Excel
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
byte[] bArr = ms.ToArray();
fs.Write(bArr, 0, bArr.Length);
fs.Flush();
}
#endregion
}
public static void ExportXml(DataTable dtData, string path)
{
XmlElement xeItem;
XmlDocument xmlDoc = new XmlDocument();
XmlElement xeRoot = xmlDoc.CreateElement("root");
foreach (DataRow dr in dtData.Rows)
{
xeItem = xmlDoc.CreateElement("item");
for (int i = 0; i < dtData.Columns.Count; i++)
{
DataColumn dc = dtData.Columns[i];
xeItem.SetAttribute(dc.ColumnName.Trim().Replace(" ", "_").Replace("(", "-").Replace(")", "").Replace("(", "-").Replace(")", ""), dr[i].ToString().Trim());
}
xeRoot.AppendChild(xeItem);
}
xmlDoc.AppendChild(xeRoot);
xmlDoc.Save(path);
}
public static void ExportExcel(string path)
{
System.IO.FileInfo filet = new System.IO.FileInfo(path);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filet.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
HttpContext.Current.Response.AddHeader("Content-Length", filet.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
HttpContext.Current.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
HttpContext.Current.Response.WriteFile(filet.FullName);
// 停止页面的执行
HttpContext.Current.Response.End();
}
public static void ExportExcel(Page xpage, DataTable dtData, string ExcelTitle)
{
xpage.Response.Clear();
xpage.Response.Buffer = true;
xpage.Response.ContentEncoding = System.Text.Encoding.UTF8;
xpage.Response.HeaderEncoding = System.Text.Encoding.UTF8;
xpage.Response.Charset = "UTF-8";
xpage.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(ExcelTitle)) + ".xls");
xpage.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");//设置输出流为简体中文
xpage.Response.ContentType = "application/ms-excel";
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
GridView gv = new GridView();
gv.DataSource = dtData;
gv.DataBind();
gv.RenderControl(oHtmlTextWriter);
xpage.Response.Write(oStringWriter.ToString());
xpage.Response.End();
}
public static DataTable ReadExcelSheets(string PathExcel)
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
{
conn.Open();
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
conn.Close();
return dtSheetName;
}
}
public static DataTable ExcelGet(string PathExcel, string strSQL)
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
{
DataSet ds = new DataSet();
OleDbDataAdapter oleAdMaster = new OleDbDataAdapter(strSQL, conn);
oleAdMaster.Fill(ds, "dsExcel");
oleAdMaster.Dispose();
conn.Close();
return ds.Tables[0];
}
}
public static void ExcelExec(string PathExcel, string strSQL)
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + PathExcel))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
public static DataSet ExportCsvIn(string PathCsv)
{
string filePath = PathCsv.Substring(0, PathCsv.LastIndexOf(@"\"));
string fileName = PathCsv.Substring(PathCsv.LastIndexOf(@"\") + 1);
DataSet ds = new DataSet();
string strConnCSV = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;'";
System.Data.OleDb.OleDbConnection objConn = new OleDbConnection(strConnCSV);
objConn.Open();
string strSql = "SELECT * FROM " + fileName;
System.Data.OleDb.OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, strConnCSV);
odbcCSVDataAdapter.Fill(ds);
return ds;
}
public static DataSet ExportExcelIn(string PathExcel)
{
return ExportExcelIn(PathExcel, "Sheet1$");
}
public static DataSet ExportExcelIn(string PathExcel, string SheetName)
{
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", PathExcel);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel = "select * from [" + SheetName + "]";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
private static DataTable ExcelToDataTable(ISheet sheet, bool isFirstRowColumn)
{
var data = new DataTable(); int startRow = 0;
try
{
if (sheet != null)
{
var firstRow = sheet.GetRow(0);
if (firstRow == null)
return data;
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
//.StringCellValue;
var column = new DataColumn(Convert.ToChar(((int)'A') + i).ToString());
if (isFirstRowColumn)
{
var columnName = firstRow.GetCell(i).StringCellValue;
column = new DataColumn(columnName);
}
data.Columns.Add(column);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
}
data.Rows.Add(dataRow);
}
}
else throw new Exception("Don not have This Sheet");
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 读取excel的数据(第一个工作表
/// </summary>
/// <param name="PathExcel">excel文件地址</param>
/// <returns></returns>
public static DataTable ExportExcelIn_ByNPOI(string PathExcel)
{
return ExportExcelIn_ByNPOI(PathExcel, 0);
}
/// <summary>
/// 读取excel的数据
/// </summary>
/// <param name="PathExcel">excel文件地址</param>
/// <param name="SheetIndex">工作表序号,从第0开始</param>
/// <returns></returns>
public static DataTable ExportExcelIn_ByNPOI(string PathExcel, int SheetIndex)
{
using (FileStream fs = File.OpenRead(PathExcel))
{
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
if (SheetIndex >= wk.NumberOfSheets)
{
throw new Exception("读取的excel表不对");
}
ISheet sheet = wk.GetSheetAt(SheetIndex); //读取当前表数据
DataTable dt = ExcelToDataTable(sheet, true);
return dt;
}
}
/// <summary>
/// 读取excel的数据,并替换数据,再生成excel
/// </summary>
/// <param name="PathExcel">excel文件地址</param>
/// <param name="SheetIndex">工作表序号,从第0开始</param>
/// <returns></returns>
public static string ExportExcel_ByNPOI(string PathExcel, string PathExcelTo, int SheetIndex, List<excelCell> ReplaceData)
{
try
{
using (FileStream fs = File.OpenRead(PathExcel))
{
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
if (SheetIndex >= wk.NumberOfSheets)
{
throw new Exception("读取的excel表不对");
}
ISheet sheet = wk.GetSheetAt(SheetIndex); //读取当前表数据
foreach (excelCell cells in ReplaceData)
{
string[] cell = cells.location.Split(',');
if (sheet.GetRow(cell[0].ConvertToIntSafe()) == null) sheet.CreateRow(cell[0].ConvertToIntSafe());
if (sheet.GetRow(cell[0].ConvertToIntSafe()).GetCell(cell[1].ConvertToIntSafe()) == null)
sheet.GetRow(cell[0].ConvertToIntSafe()).CreateCell(cell[1].ConvertToIntSafe());
sheet.GetRow(cell[0].ConvertToIntSafe()).GetCell(cell[1].ConvertToIntSafe()).SetCellValue(cells.value.ToStringSafe());
}
FileStream file = new FileStream(PathExcelTo, FileMode.OpenOrCreate);
wk.Write(file);
file.Flush();
file.Close();
return string.Empty;
}
}
catch (Exception ex)
{
return "error:" + ex.Message;
}
}
}
3.已经有Excel模板怎么导出
1.把Excel文档保存在web项目中一个文件夹里面
/// <summary>
/// 文件下载
/// </summary>
/// <param name="type">下载文件类型 1.工人离场模板 2.基本信息模板</param>
/// <returns></returns>
[HttpPost]
[Route("FileDownload")]
[Description("文件下载")]
public HttpResponseMessage FileDownload(int type)
{
string path = null;
switch (type)
{
case 1:
path = "/ExportFile/批量导入工人离场模板文件 .xls";
break;
case 2:
path = "/ExportFile/批量导入模板文件.xls";
break;
default:
break;
}
string fileDownloadPath = HostingEnvironment.MapPath(path);
iExcel.ExportExcel(fileDownloadPath);
return ReturnMessageSuccess();
}