第一种:利用office插件进行读取,缺点是需要安装office才可以使用
/// <summary>
/// 将数据集中的数据导出到EXCEL文件
/// </summary>
/// <param name="dataSet">输入数据集</param>
/// <param name="isShowExcle">是否显示该EXCEL文件</param>
/// <returns>返回下载地址或错误消息</returns>
public string DataTableToExcel(System.Data.DataTable dt,string fileName)
{
string url = "";
int rowNumber = dt.Rows.Count;//不包括字段名
int columnNumber = dt.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
return "没有数据!";
}
Application excel = new Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Workbook workbook = null;
Worksheet worksheet = null;
Range range;
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (Worksheet)workbook.Worksheets[1];
//设置对象不可见
excel.Visible = false;
excel.DisplayAlerts = false;
try
{
//生成字段名称
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dt.Rows[r][c];
}
}
// 写入Excel,第一行写列名,从第二行开始写数据
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
//保存工作表
fileName = fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
workbook.SaveAs(filepath + fileName, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbook.Close(false, miss, miss);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();
url = fileUrl + fileName;
}
catch (Exception e)
{
url = e.Message;
}
finally
{
if (excel != null)
{
//关闭进程把程序也关掉了,待修改测试
//KillSpecialExcel(excel);
}
}
return url;
}
/// <summary>
/// 导出--键值对方式
/// </summary>
/// <param name="list"><列名,数据></param>
/// <param name="filepath"></param>
/// <returns></returns>
public bool NewExport(List<DictionaryEntry> list, string fileName)
{
bool bSuccess = true;
Application appexcel = new Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Application();
Workbook workbookdata = null;
Worksheet worksheetdata = null;
Range rangedata;