public class ExcelHelper
{
/// <summary>
/// NPOI Excel转DataTable
/// </summary>
/// <param name="excelServerPath"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string excelServerPath, bool hasTitle = true)
{
FileStream fs = System.IO.File.OpenRead(excelServerPath);
IWorkbook workBook = WorkbookFactory.Create(fs, ImportOption.All);
ISheet sheet = workBook.GetSheetAt(0);
DataTable dt = null;
if (hasTitle)
{
dt = SheetToDataTableHasTitle(sheet);
}
else
{
dt = SheetToDataTable(sheet);
}
fs.Close();
fs.Dispose();
workBook.Close();
return dt;
}
/// <summary>
/// NPOI Excel转DataTable
/// </summary>
/// <param name="excelServerPath"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(Stream stream)
{
IWorkbook workBook = WorkbookFactory.Create(stream, ImportOption.All);
ISheet sheet = workBook.GetSheetAt(0);
DataTable dt = SheetToDataTable(sheet);
workBook.Close();
return dt;
}
/// <summary>
/// NPOI Excel转DataSet
/// </summary>
/// <param name="excelServerPath"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string excelServerPath, bool hasTitle = true)
{
FileStream fs = System.IO.File.OpenRead(excelServerPath);
IWorkbook workBook = WorkbookFactory.Create(fs, ImportOption.All);
fs.Close();
fs.Dispose();
DataSet ds = new DataSet();
for (int i = 0; i < workBook.NumberOfSheets; i++)
{
ISheet sheet = workBook.GetSheetAt(i);
DataTable dt = null;
if (hasTitle)
{
dt = SheetToDataTableHasTitle(sheet);
}
else
{
dt = SheetToDataTable(sheet);
}
ds.Tables.Add(dt);
}
return ds;
}
/// <summary>
/// NPOI DataTable转Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="contentEncode"></param>
public static void Export(DataTable dt, string fileName, Dictionary<int, List<string>> dic = null)
{
if (dt == null || dt.Columns.Count <= 0)
{
return;
}
IWorkbook workBook = new HSSFWorkbook();
DataTableFillWorkBook(dt, workBook, dic);
Export(workBook, fileName);
}
/// <summary>
/// NPOI DataSet转Excel
/// </summary>
public static void Export(DataSet ds, string fileName, Dictionary<int, Dictionary<int, List<string>>> dic = null)
{
if (ds == null || ds.Tables.Count <= 0)
{
return;
}
IWorkbook workBook = new HSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
var dt = ds.Tables[i];
Dictionary<int, List<string>> itemDic = null;
if (dic != null && dic.ContainsKey(i))
{
itemDic = dic[i];
}
DataTableFillWorkBook(dt, workBook, itemDic);
}
Export(workBook, fileName);
}
/// <summary>
/// 根据DataTable导出csv文件
/// </summary>
/// <param name="dataTable">DataTable数据</param>
/// <param name="fileName">文件名（不带后缀）</param>
/// <param name="encodeName">编码方式 如 utf-8</param>
/// <param name="isCloseHttpResponse">是否关闭HttpResponse</param>
public static void ExportCsv(DataTable dataTable, string fileName, string encodeName, bool isCloseHttpResponse)
{
HttpResponse httpResponse = HttpContext.Current.Response;
if (null == dataTable)
{
return;
}
StringBuilder strBuilder = new StringBuilder();
//组合信息
int columnCount = dataTable.Columns.Count;
for (int i = 0; i < columnCount; i++)
{
string colName = dataTable.Columns[i].ColumnName + "";
if (colName.Contains(","))
{
colName = colName.Replace(",", "，");
}
if (i == columnCount - 1)
{
strBuilder.Append(colName + "\r\n");
}
else
{
strBuilder.Append(colName + ",");
}
}
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < columnCount; i++)
{
string rowValue = row[i].ToString() + "";
if (rowValue.Contains(","))
{
rowValue = rowValue.Replace(",", "，");
}
//如果是行尾
if (i == columnCount - 1)
{
strBuilder.Append(rowValue + "\r\n");
}
else
{
strBuilder.Append(rowValue + ",");
}
}
}
#region 客户端导出文件
//内容编码格式设定
Encoding contentEncode = Encoding.GetEncoding(encodeName);
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".csv");
httpResponse.ContentEncoding = contentEncode;
httpResponse.ContentType = ".csv";
httpResponse.Clear();
httpResponse.Write(strBuilder.ToString());
httpResponse.End();
#endregion
}
/// <summary>
/// 根据DataTable导出csv文件
/// </summary>
/// <param name="csvServerPath">csv路径</param>
public static DataTable CsvToDataTable(string csvServerPath)
{
Regex reg = new Regex("\",\"");
int intColCount = 0;
DataTable dt = new DataTable("myTableName");
string columnLine;
string[] columnsArray;
using (StreamReader streamReader = new StreamReader(csvServerPath, System.Text.Encoding.Default))
{
columnLine = streamReader.ReadLine();
columnsArray = columnLine.Split(',');
intColCount = columnsArray.Length;
for (int i = 0; i < columnsArray.Length; i++)
{
DataColumn mydc = new DataColumn(columnsArray[i].Replace("\"", ""), typeof(string));
dt.Columns.Add(mydc);
}
while ((columnLine = streamReader.ReadLine()) != null)
{
columnsArray = columnLine.Split(',');
DataRow mydr = dt.NewRow();
for (int i = 0; i < intColCount; i++)
{
mydr[i] = columnsArray[i].Replace("\"", "");
}
dt.Rows.Add(mydr);
}
}
return dt;
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="strContent">html</param>
/// <param name="fileName"></param>
/// <param name="contentEncode"></param>
public static void HtmlExcel(string strContent, string fileName, Encoding contentEncode)
{
ExportExcel(strContent, fileName, contentEncode);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="strContent">内容</param>
/// <param name="fileName">文件名称</param>
/// <param name="contentEncode">内容编码</param>
private static void ExportExcel(string strContent, string fileName, Encoding contentEncode)
{
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append("<html xmlns=\"http://www.w3.org/1999/xhtml\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
strBuilder.Append("<head>");
strBuilder.Append("<xml>");
strBuilder.Append("<x:ExcelWorkbook>");
strBuilder.Append("<x:ExcelWorksheets>");
strBuilder.Append("<x:ExcelWorksheet>");
strBuilder.Append("<x:Name>Sheet1</x:Name>");
strBuilder.Append("<x:WorksheetOptions>");
strBuilder.Append("<x:Print>");
strBuilder.Append("<x:ValidPrinterInfo/>");
strBuilder.Append("</x:Print>");
strBuilder.Append("</x:WorksheetOptions>");
strBuilder.Append("</x:ExcelWorksheet>");
strBuilder.Append("</x:ExcelWorksheets>");
strBuilder.Append("</x:ExcelWorkbook>");
strBuilder.Append("</xml>");
strBuilder.Append("</head>");
strBuilder.Append("<body><table>");
strBuilder.Append(strContent);
strBuilder.Append("</table></body>");
strBuilder.Append("</html>");
#region 客户端导出文件
HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.AddHeader("Pragma", "public");
httpResponse.AddHeader("Cache-Control", "max-age=0");
httpResponse.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
httpResponse.ContentEncoding = contentEncode;
httpResponse.ContentType = "application/vnd.ms-excel";
httpResponse.Clear();
httpResponse.Write(strBuilder.ToString());
httpResponse.End();
#endregion
}
/// <summary>
/// 输出Excel
/// </summary>
/// <param name="workBook"></param>
/// <param name="fileName"></param>
/// <param name="contentEncode"></param>
public static void Export(IWorkbook workBook, string fileName)
{
MemoryStream ms = new MemoryStream();
workBook.Write(ms);
HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.AddHeader("Pragma", "public");
httpResponse.AddHeader("Cache-Control", "max-age=0");
String userAgent = HttpContext.Current.Request.UserAgent;
//IE
if (userAgent.ToUpper().IndexOf("MSIE") > 0)
{
fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
httpResponse.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
//httpResponse.AddHeader("content-type", "application/x-msdownload");
//httpResponse.ContentEncoding = Encoding.UTF8;
httpResponse.ContentType = "application/vnd.ms-excel";
httpResponse.BinaryWrite(ms.ToArray());
workBook = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 构造Excel下拉
/// </summary>
/// <param name="sheet"></param>
/// <param name="dic"></param>
public static void StructWorkbookDropdown(ISheet sheet, Dictionary<int, List<string>> dic = null)
{
foreach (var item in dic.Keys)
{
if (dic[item] != null && dic[item].Count > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, item, item);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(dic[item].ToArray());
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
}
}
/// <summary>
/// 清空单元格内容
/// </summary>
public static void RemoveSheetContent(ISheet sheet, bool isRemoveRow, CellRangeAddress address)
{
for (int i = address.FirstRow; i <= address.LastRow; i++)
{
//移除全部合并
for (int n = sheet.NumMergedRegions - 1; n >= 0; n--)
{
sheet.RemoveMergedRegion(n);
}
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (isRemoveRow == true)
{
sheet.RemoveRow(row);
continue;
}
for (int c = address.FirstColumn; c <= address.LastColumn; c++)
{
ICell cell = row.GetCell(c);
if (cell == null)
{
continue;
}
cell.SetCellValue("");
}
}
}
#region 私有方法
/// <summary>
/// NPOI Sheet转Datatable
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static DataTable SheetToDataTable(ISheet sheet)
{
if (sheet.LastRowNum <= 0)
{
return null;
}
DataTable dt = new DataTable(sheet.SheetName);
int maxColumnCount = 0;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || row.LastCellNum <= maxColumnCount)
{
continue;
}
maxColumnCount = row.LastCellNum;
}
for (int i = 0; i < maxColumnCount; i++)
{
dt.Columns.Add();
}
for (int i = 0; i <= sheet.LastRowNum; i++)
{
DataRow dataRow = dt.NewRow();
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
default:
dataRow[j] = cell.ToString();
break;
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
private static DataTable SheetToDataTableHasTitle(ISheet sheet)
{
DataTable dt = new DataTable();
if (!string.IsNullOrWhiteSpace(sheet.SheetName))
{
dt.TableName = sheet.SheetName;
}
IRow firstRow = sheet.GetRow(0);
for (int i = 0; i < firstRow.Cells.Count; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
var colName = firstRow.GetCell(i).ToString();
colName = Regex.Replace(colName, @"\s", "");
if (dt.Columns[colName] == null)
{
dt.Columns.Add(colName);
}
else
{
dt.Columns.Add();
}
}
else
{
dt.Columns.Add();
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow dataRow = dt.NewRow();
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = 0; j < firstRow.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
default:
dataRow[j] = cell.ToString();
break;
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 导出Excel数据填充
/// </summary>
/// <param name="dt"></param>
/// <param name="workBook"></param>
private static void DataTableFillWorkBook(DataTable dt, IWorkbook workBook, Dictionary<int, List<string>> dic = null)
{
var sheetName = dt.TableName;
if (string.IsNullOrWhiteSpace(sheetName))
{
sheetName = string.Format("sheet{0}", workBook.NumberOfSheets + 1);
}
ISheet sheet = workBook.CreateSheet(sheetName);
if (dic != null && dic.Count > 0)
{
foreach (var item in dic.Keys)
{
if (dic[item] != null && dic[item].Count > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, item, item);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(dic[item].ToArray());
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
}
}
IRow firstRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
firstRow.CreateCell(i, CellType.String).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
DataRow dtRow = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j, CellType.String).SetCellValue(dtRow[j].ToString());
}
}
}
#endregion
}
Dictionary<int, string> dicCurrencyType = EnumAttribute.GetEnumDictionary(typeof(CurrencyType));
Dictionary<string, string> dicIsInsured = new Dictionary<string, string>();
dicIsInsured.Add("true", "购买");
dicIsInsured.Add("false", "不购买");
//读取模版
string tplFilePath = Server.MapPath("~/Areas/YXExports/Content/Temp/order_lcl_templete.xls");
FileStream fs = System.IO.File.OpenRead(tplFilePath);
IWorkbook workBook = WorkbookFactory.Create(fs, ImportOption.All);
fs.Close();
fs.Dispose();
ISheet sheet2 = workBook.GetSheetAt(1);
for (int i = 0; i < productList.Count; i++)
{
IRow row = sheet2.GetRow(i + 1);
if (row == null)
{
row = sheet2.CreateRow(i + 1);
}
ICell cell = row.GetCell(0);
if (cell == null)
{
cell = row.CreateCell(0);
}
cell.SetCellValue(productList[i].Name);
}
for (int i = 0; i < supportCountries.Count; i++)
{
IRow row = sheet2.GetRow(i + 1);
if (row == null)
{
row = sheet2.CreateRow(i + 1);
}
ICell cell = row.GetCell(1);
if (cell == null)
{
cell = row.CreateCell(1);
}
cell.SetCellValue(supportCountries[i].CnGeoName);
}
//下拉框
ISheet sheet1 = workBook.GetSheetAt(0);
Dictionary<int, List<string>> dropDown = new Dictionary<int, List<string>>();
dropDown.Add(1, productList.Select(l => l.Name).ToList());
dropDown.Add(23, dicCurrencyType.Select(l => l.Key.ToString()).ToList());
dropDown.Add(24, dicIsInsured.Select(l => l.Value.ToString()).ToList());
ExcelHelper.StructWorkbookDropdown(sheet1, dropDown);
ExcelHelper.Export(workBook, string.Format(" {0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
DataTable dt = new DataTable();
dt.Columns.Add("公司名称");
dt.Columns.Add("客户邮箱");
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["公司名称"] = item.CompanyName;
dr["客户邮箱"] = item.Email;
dt.Rows.Add(dr);
}
string fileName = string.Format("客户列表.xls");
ExcelHelper.Export(dt, fileName);
