时间戳

首页 > 技术文章 > 导出Excel

导出Excel

hnzheng 2020-04-03 16:03 原文

导出Excel

操作类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using System.IO;
using System.Data;
namespace SAFS.FundDataOperation.ExcelLib
{
public class ExcelWriter : IDisposable
{

#region Private Property
private IWorkbook _workbook;
private Stream _fileStream;
private string _fileName;
private EFileVersion _fileVersion { get; set; }

#endregion

public IWorkbook Workbook
{
get { return _workbook; }
set { _workbook = value; }
}

#region Constructor
public ExcelWriter(string fileName)
{
_fileName = fileName;
if (fileName.EndsWith(".xls"))
_fileVersion = EFileVersion.V97_2003;
else _fileVersion = EFileVersion.V2007;

using (_fileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read))
{
LoadFile();
_fileStream.Close();
_fileStream.Dispose();
}
}

public ExcelWriter(Stream stream, EFileVersion fileVersion = EFileVersion.V2007)
{
_fileStream = stream;
_fileName = string.Empty;
_fileVersion = fileVersion;
LoadFile();
}

#endregion

#region Private Method
private void LoadFile()
{

if (_fileVersion == EFileVersion.V97_2003)
_workbook = new HSSFWorkbook();
else
_workbook = new XSSFWorkbook();
}

private void SetCellValue(ICell cell, object obj)
{
if (obj != null)
{
IDataFormat format = _workbook.CreateDataFormat();
ICellStyle cellStyle = _workbook.CreateCellStyle();

Type type = obj.GetType();
switch (type.ToString())
{
case "System.String"://字符串类型
cell.SetCellValue(obj.ToString());
break;
case "System.DateTime"://日期类型
cell.SetCellValue(Convert.ToDateTime(obj));
cellStyle.DataFormat = format.GetFormat("yyyy/MM/dd");
cell.CellStyle = cellStyle;
break;
case "System.Data.SqlTypes.SqlDateTime":
cell.SetCellValue(((System.Data.SqlTypes.SqlDateTime)(obj)).Value);
cellStyle.DataFormat = format.GetFormat("yyyy/MM/dd");
cell.CellStyle = cellStyle;
break;
case "System.TimeSpan":
double numValue = ((TimeSpan)obj).TotalSeconds / 86400;
cell.SetCellValue(numValue);
cellStyle.DataFormat = format.GetFormat("hh:mm:ss");
cell.CellStyle = cellStyle;
break;
case "System.Boolean"://布尔型
cell.SetCellValue(Convert.ToBoolean(obj));
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
cell.SetCellValue(Convert.ToInt32(obj));
break;
case "System.Decimal"://浮点型
case "System.Double":
cell.SetCellValue(Convert.ToDouble(obj));
break;
case "System.DBNull"://空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}

private int GetRowIndex(ISheet sheet, int startRowIndex, bool isExtended)
{
int rowIndex = startRowIndex;
if (isExtended)
rowIndex = sheet.LastRowNum + startRowIndex;
return rowIndex;
}

private ISheet GetSheet(string sheetName)
{
ISheet sheet = _workbook.GetSheet(sheetName);
if (sheet == null)
sheet = _workbook.CreateSheet(sheetName);
return sheet;
}

private void AddDataTableToSheet(DataTable table, ISheet sheet, int startRowIndex, int leftColuIndex, bool isColumnNameWritten, bool isExtended)
{
int rowIndex = GetRowIndex(sheet, startRowIndex, isExtended);
IRow firstRow = sheet.CreateRow(rowIndex);

if (isColumnNameWritten == true)
{
int colIndex = leftColuIndex;
for (int j = 0; j < table.Columns.Count; ++j, colIndex++)
{
firstRow.CreateCell(colIndex).SetCellValue(table.Columns[j].ColumnName);
}
rowIndex++;
}

foreach (DataRow row in table.Rows)
{
IRow newRow = sheet.CreateRow(rowIndex++);
int colIndex = leftColuIndex;
for (int i = 0; i < table.Columns.Count; i++)
{
SetCellValue(newRow.CreateCell(colIndex), row[i]);
colIndex++;
}
}
}

private void AddTextToSheet(string text, ISheet sheet, int rowIndex, int colIndex, bool isExtended)
{
int index = GetRowIndex(sheet, rowIndex, isExtended);

IRow row = sheet.GetRow(index);
if (row == null)
row = sheet.CreateRow(index);

SetCellValue(row.CreateCell(colIndex), text);
}

#endregion

#region Public Method
public void AddSheetToWorkbook(string sheetName)
{
_workbook.CreateSheet(sheetName);
}

public void AddDatatableToExcel(string sheetName, DataTable table, bool isColumnNameWritten = true, bool isExtended = true)
{
AddDatatableToExcel(sheetName, table, 0, 0, isColumnNameWritten, isExtended);
}

public void AddDatatableToExcel(string sheetName, DataTable table, int startRowIndex = 0, int leftColIndex = 0, bool isColumnNameWritten = true, bool isExtended = true)
{
ISheet sheet = GetSheet(sheetName);
AddDataTableToSheet(table, sheet, startRowIndex, leftColIndex, isColumnNameWritten, isExtended);
}

public void AddTextToExcel(string sheetName, string text, int rowIndex = 0, int colIndex = 0, bool isExtended = true)
{
ISheet sheet = GetSheet(sheetName);
AddTextToSheet(text, sheet, rowIndex, colIndex, isExtended);
}

public void Write()
{
if (!string.IsNullOrEmpty(_fileName))
{
using (_fileStream = File.OpenWrite(_fileName))
{
_workbook.Write(_fileStream);
_fileStream.Close();
_fileStream.Dispose();
}
}
else
{
_workbook.Write(_fileStream);
}
}

public void Dispose()
{
// Close();
}

public void SetFont(string sheetName, int rowIndex, int colIndex, string fontName, short fontSize, bool boldWeight = false,
HorizontalAlignment horizontalAlignment = HorizontalAlignment.Left, bool withBorder = false, bool highlightForeground = false)
{
ISheet sheet = GetSheet(sheetName);
IRow row = sheet.GetRow(rowIndex);
ICell cell = row.GetCell(colIndex);
ICellStyle style = CreateStyle(fontName, fontSize, boldWeight, horizontalAlignment, withBorder, highlightForeground);
cell.CellStyle = style;
}

public ICellStyle CreateStyle(string fontName, short fontSize, bool boldWeight = false,
HorizontalAlignment horizontalAlignment = HorizontalAlignment.Left, bool withBorder = false, bool highlightForeground = false, bool isText = false)
{
ICellStyle style = Workbook.CreateCellStyle();
IFont font = Workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
if (boldWeight == true)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = horizontalAlignment;
if (highlightForeground)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Red.Index;
}
if (withBorder)
{
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
}
if (isText)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
}
return style;
}

public void SetCellStyle(string sheetName, int rowIndex, int colIndex, ICellStyle style)
{
ISheet sheet = GetSheet(sheetName);
IRow row = sheet.GetRow(rowIndex);
ICell cell = row.GetCell(colIndex);
cell.CellStyle = style;
}

public void SetMergedRegion(string sheetName, int firstRow, int lastRow, int firstCol, int lastCol)
{
ISheet sheet = GetSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}

//public bool GenerateByPattern(string patternFileNamePath, object obj)
//{
// var excel = new FilePattern.ExcelPattern();
// return excel.Generate(patternFileNamePath, obj, ref _workbook);
//}

#endregion
}

public enum EFileVersion
{
V97_2003,
V2007
}
}

//设置格式

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

namespace SAFS.FundDataOperation.ExcelLib
{
class ExcelFormater
{
private IWorkbook m_workbook;

public ExcelFormater(IWorkbook workbook)
{
m_workbook = workbook;
}

public void MergeCellsAndFormat(string sheetName, int startRow, int startColumn, int endRow, int endColumn, string fontName, short fontSize, bool boldWeight = false, bool border = true)
{
//SetBorderAndFont(sheetName, startRow, startColumn, endRow, endColumn, fontName, fontSize, boldWeight);//找不到后面的单元格，报错
SetBorderAndFont(sheetName, startRow, startColumn, endRow, endColumn, fontName, fontSize, border, boldWeight);

CellRangeAddress range = new CellRangeAddress(startRow, endRow, startColumn, endColumn);
ISheet sheet = m_workbook.GetSheet(sheetName);
sheet.AddMergedRegion(range);

IRow row = sheet.GetRow(startRow);
IFont font = m_workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
if (boldWeight == true)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

ICell cell = row.GetCell(startColumn);
ICellStyle style = m_workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font);
if (border == true)
{
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = HSSFColor.Black.Index;
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = HSSFColor.Black.Index;
}

cell.CellStyle = style;
}

public ICellStyle CreateCellStyle(string fontName, short fontSize, string direction, bool border, bool isCurrencyStyle, bool isPercentStyle, bool boldWeight)
{
IFont font = m_workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
if (boldWeight == true)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

ICellStyle style = m_workbook.CreateCellStyle();
style.SetFont(font);
if (border == true)
{
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = HSSFColor.Black.Index;
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = HSSFColor.Black.Index;
}

HorizontalAlignment align;
switch (direction)
{
case "center":
align = HorizontalAlignment.Center;
break;
case "right":
align = HorizontalAlignment.Right;
break;
case "left":
align = HorizontalAlignment.Left;
break;
default:
align = HorizontalAlignment.Right;
break;
}
style.Alignment = align;
if (isCurrencyStyle)
{
IDataFormat format = m_workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("#,##0.0000");
}
if (isPercentStyle)
{
IDataFormat format = m_workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("0.00%");
}
return style;
}

public void SetStyle(string sheetName, int startRow, int startColumn, int endRow, int endColumn, ICellStyle style)
{
ISheet sheet = m_workbook.GetSheet(sheetName);
for (int i = startRow; i <= endRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = startColumn; j <= endColumn; j++)
{
ICell cell = row.GetCell(j);
cell.CellStyle = style;
}
}
}

public void SetBorderAndFont(string sheetName, int startRow, int startColumn, int endRow, int endColumn, string fontName, short fontSize, bool border = true, bool boldWeight = false)
{
ISheet sheet = m_workbook.GetSheet(sheetName);

IFont font = m_workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
if (boldWeight == true)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;

ICellStyle style = m_workbook.CreateCellStyle();
if (border == true)
{
style.BorderTop = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.BorderLeft = BorderStyle.Thin;
style.LeftBorderColor = HSSFColor.Black.Index;
style.BorderRight = BorderStyle.Thin;
style.RightBorderColor = HSSFColor.Black.Index;
}

for (int i = startRow; i <= endRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = startColumn; j <= endColumn; j++)
{
//ICellStyle style = m_workbook.GetCellStyleAt(;
style.SetFont(font);
ICell cell = row.GetCell(j);
cell.CellStyle = style;
}
}
}

public void SetColumnWidth(string sheetName, int columnIndex, int width)
{
ISheet sheet = m_workbook.GetSheet(sheetName);
sheet.SetColumnWidth(columnIndex, width * 256);
}

public void SetDoubleRowHeight(string sheetName, int rowIndex)
{
ISheet sheet = m_workbook.GetSheet(sheetName);
IRow row = sheet.GetRow(rowIndex);
row.HeightInPoints = 2 * sheet.DefaultRowHeight / 20;
}

public void SetRrightAlignAndCurrencyStyle(string sheetName, int startRow, int startColumn, int endRow, int endColumn)
{
SetAlign(sheetName, startRow, startColumn, endRow, endColumn, "right", true);
}

public void SetCenterAlign(string sheetName, int startRow, int startColumn, int endRow, int endColumn)
{
SetAlign(sheetName, startRow, startColumn, endRow, endColumn, "center");
}

public void SetLeftAlign(string sheetName, int startRow, int startColumn, int endRow, int endColumn)
{
SetAlign(sheetName, startRow, startColumn, endRow, endColumn, "left");
}

private void SetAlign(string sheetName, int startRow, int startColumn, int endRow, int endColumn, string direction, bool isCurrencyStyle = false)
{
HorizontalAlignment align;
switch (direction)
{
case "center":
align = HorizontalAlignment.Center;
break;
case "right":
align = HorizontalAlignment.Right;
break;
case "left":
align = HorizontalAlignment.Left;
break;
default:
align = HorizontalAlignment.Right;
break;
}

ISheet sheet = m_workbook.GetSheet(sheetName);
for (int i = startRow; i <= endRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = startColumn; j <= endColumn; j++)
{
ICell cell = row.GetCell(j);
if (cell.CellStyle != null)
{
cell.CellStyle.Alignment = align;
if (isCurrencyStyle)
{
IDataFormat format = m_workbook.CreateDataFormat();
cell.CellStyle.DataFormat = format.GetFormat("#,##0.0000");
}
}
else
{
ICellStyle style = m_workbook.CreateCellStyle();
style.Alignment = align;
if (isCurrencyStyle)
{
IDataFormat format = m_workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("#,##0.0000");
}
cell.CellStyle = style;
}
}
}
}

public void SetFont(string sheetName, int startRow, int startColumn, int endRow, int endColumn, string fontName, short fontSize, bool boldWeight = false)
{
ISheet sheet = m_workbook.GetSheet(sheetName);
IFont font = m_workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
if (boldWeight == true)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
for (int i = startRow; i <= endRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = startColumn; j <= endColumn; j++)
{
ICell cell = row.GetCell(j);
ICellStyle style = m_workbook.CreateCellStyle();
style.SetFont(font);
cell.CellStyle = style;

}
}
}

public void SetWrap(string sheetName, int startRow, int startColumn, int endRow, int endColumn)
{
ISheet sheet = m_workbook.GetSheet(sheetName);
for (int i = startRow; i <= endRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = startColumn; j <= endColumn; j++)
{
ICell cell = row.GetCell(j);
if (cell.CellStyle != null)
cell.CellStyle.WrapText = true;
else
{
ICellStyle style = m_workbook.CreateCellStyle();
style.WrapText = true;
cell.CellStyle = style;
}
}
}
}
}
}

****************winform导出

private void ExportBtn_Click(object sender, EventArgs e)
{
string fileName = string.Empty;
if (list != null)
{
if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
{
fileName = textBox1.Text + "_" + startTime.ToString("yyyy-MM-dd") + "_" + endTime.AddDays(-1).ToString("yyyy-MM-dd");
}
else
fileName = startTime.ToString("yyyy-MM-dd") + "_" + endTime.AddDays(-1).ToString("yyyy-MM-dd");

fileName = string.Concat(fileName, ".xls");
label7.Text = "正在导出...";
var dt = ExcelOperate.ConvertToTable(list);
FolderBrowserDialog dialog = new FolderBrowserDialog();
if (dialog.ShowDialog() == DialogResult.OK)
{
string path = Path.Combine(dialog.SelectedPath, fileName);
ExcelOperate.SaveToExcel("估值报告",dt, path);
}
label7.Text = "导出完成";
label7.Text = "";
}
else
MessageBox.Show("无数据可导出！");
}

**********************************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Web.UI;
using SAFS.FundDataOperation.Models;
namespace SAFS.FundDataOperation.Mail
{
class ExcelOperate
{
public static void SaveToExcel(string sheetName, DataTable dt, string fileName)
{
using (ExcelLib.ExcelWriter write = new ExcelLib.ExcelWriter(fileName))
{

ExcelLib.ExcelFormater formater = new ExcelLib.ExcelFormater(write.Workbook);
write.AddDatatableToExcel(sheetName, dt, true, false);
SetStyle(sheetName, dt.Rows.Count, formater);
write.Write();//导出
}
}
private static void SetStyle(string sheetName, int dtCount, ExcelLib.ExcelFormater formater)
{
//设置格式
// formater.MergeCellsAndFormat(sheetName, 0, 0, 0, 0, "宋体", 14, true);
formater.SetBorderAndFont(sheetName, 0, 0, dtCount, 2, "宋体", 12,false,true);
for (int i = 0; i < 3; i++)
{
if (i == 2)
formater.SetColumnWidth(sheetName, i, 60);
else
formater.SetColumnWidth(sheetName, i, 50);
}
}
public static DataTable ConvertToTable(List<MailModel> list)
{
DataTable dt = new DataTable();
dt.Columns.Add("报告主题", typeof(string));
dt.Columns.Add("发送时间", typeof(string));
dt.Columns.Add("操作人", typeof(string));
list.ToList().ForEach(d =>
{
dt.Rows.Add(d.ObjectMail, d.DateMail.ToString(), d.FromMail);
});
return dt;
}

}
}

******************ASP 文件名为全路径

MemoryStream stream = new MemoryStream();
using (ExcelWriter writer = new ExcelWriter(stream))
{
ExcelFormater formater = new ExcelFormater(writer.Workbook);
writeMeterialList(writer, "aa", formater, workType.ToString());
}

Response.Clear();
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(fileName)));
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(stream.ToArray());
Response.Flush();
stream.Close();
stream.Dispose();

　　

推荐阅读