使用C#语言开发导出EXCEL ,推荐使用 NPOI框架
1、添加程序包。选择管理NuGet程序包,浏览处搜索NPOI并安装。
2.导出方法
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = ""; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 40 ? 40 : arrColWidth[item.Ordinal];
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
arrColWidth[j] = arrColWidth[j] > 40 ? 40 : arrColWidth[j];
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表,填充表头,填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 40;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//边框
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
//样式
//HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
边框
//cellStyle.BorderBottom = BorderStyle.THIN;
//cellStyle.BorderLeft = BorderStyle.THIN;
//cellStyle.BorderRight = BorderStyle.THIN;
//cellStyle.BorderTop = BorderStyle.THIN;
自动换行
//cellStyle.WrapText = true;
//cellStyle.VerticalAlignment = VerticalAlignment.Center;
newCell.CellStyle.VerticalAlignment = VerticalAlignment.Top;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
新增的四句话,设置CELL格式为文本格式
//HSSFCellStyle cellStyle2 = workbook.CreateCellStyle() as HSSFCellStyle;
//HSSFDataFormat fmt = workbook.CreateDataFormat() as HSSFDataFormat;
//cellStyle2.DataFormat = fmt.GetFormat("@");
if (drValue.Contains(".png") || drValue.Contains(".PNG")|| drValue.Contains(".jpg")|| drValue.Contains(".JPG")|| drValue.Contains(".jpeg"))
{
AddCellPicture(sheet, workbook, "http://10.71.3.196/MIOS.Web/" + drValue, rowIndex, column.Ordinal);
break;
}
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
if (DateTime.TryParse(drValue, out dateV))
{
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue);
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();
return ms;
}
}
3.插入图片方法
/// <summary>
/// 向sheet插入图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="workbook">文档对象</param>
/// <param name="imgPath">网络图片地址</param>
/// <param name="row">哪行</param>
/// <param name="col">哪列</param>
private static void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string imgPath, int row, int col)
{
Uri uri = new Uri(imgPath); //imgPath :网络图片地址
WebRequest webRequest = WebRequest.Create(uri);
using (WebResponse webResponse = webRequest.GetResponse())
{
//防止发生报错:GDI+中发生一般性错误的解决办法
Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流
Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中
bitmap.Dispose(); //将原来的图片流释放,将图片文件进行解锁。
using (MemoryStream ms = new MemoryStream())
{
OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.ToArray();
int pictureIdx = 0;
if (imgPath.Contains(".png")|| imgPath.Contains(".PNG"))
{
pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片
}
if (imgPath.Contains(".jpg")|| imgPath.Contains(".jpeg")|| imgPath.Contains(".JPG") )
{
pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); //添加图片
}
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//图片位置,图片左上角为(col, row)
HSSFPicture pict = (HSSFPicture)sheet.CreateDrawingPatriarch().CreatePicture(anchor, pictureIdx);
//pict.Resize(); //用图片原始大小来显示
}
}
}
5.生成table,需要改造 个人的 此方法是根据配置(xml)文件自动 查询数据库生成
public override void WriteToStream(Type type, object value, Stream writeStream, HttpContent content)
{
ODataPath path = Request.ODataProperties().Path;
IEdmNavigationSource targetNavigationSource = path == null ? null : path.NavigationSource;
var queryDatas = value.GetType().GetProperty("Query").GetValue(value) as IQueryable;
var export_conf = HttpContext.Current.Request.QueryString["export_conf"];
if (export_conf.IsEmpty()) export_conf = "Export_" + targetNavigationSource.Name;
var export = XmlUtils.GetXml<ExportXml>(HttpContext.Current.Server.MapPath("~/ExportConfig/Excel/"
+ export_conf + ".xml"));
if (export != null)
{
DataTable table = new DataTable();
var dicSysDatas = new Dictionary<string, List<T_SYS_DATA_DETAILEntity>>();
export.FileName = FormatReplaceSymbol(export.FileName);
export.SheetName = FormatReplaceSymbol(export.SheetName);
export.DisplayName = FormatReplaceSymbol(export.DisplayName);
//添加列
foreach (var field in export.Fields)
{
table.Columns.Add(field.Name, Type.GetType(field.Type, true, true));
}
//添加行数据
ExportApi exportApi = new ExportApi(export);
var _formatFun = exportApi.GetType().GetMethod("OnFormat_" + export_conf);
Type entityType = null;
if (!string.IsNullOrWhiteSpace(export.TableName))
{
var mdAssembly = typeof(Data.EFContextFactory).Assembly;
entityType = mdAssembly.GetType("MIOS.Data.EntityModel." + export.TableName);
if (entityType == null) throw new Exception("下载失败,未知的表实体名!!!");
}
// export.TableName
foreach (var data in queryDatas)
{
if (data == null) continue;
var row = table.NewRow();
var dItems = SelectExpandToData(data);
foreach (var field in export.Fields)
{
var pVal = GetValue(dItems, field.ID);
//字段为系统数据定义
if (!field.SysData.IsEmpty())
{
var zhi = (pVal ?? "").ToString();
if (!dicSysDatas.ContainsKey(field.SysData))
{
var sysdatas = Domain.DataCache.FindSysDataDetails(field.SysData, true)
?? new List<T_SYS_DATA_DETAILEntity>();
dicSysDatas.Add(field.SysData, sysdatas);
}
pVal = dicSysDatas[field.SysData].Where(p => p.DATA_VALUE == zhi)
.Select(p => p.DATA_DESC).FirstOrDefault();
}
if (field.ExistsFormatOptions)
{
var zhi = (pVal ?? "").ToString();
//存在格式化选项
var option = field.FormatOptions.Where(p => p.key != null && zhi == p.key).FirstOrDefault();
if (option != null) pVal = option.value;
}
if (!string.IsNullOrWhiteSpace(field.CustomFormat) && _formatFun != null)
{
// 存在自定义格式化
row[field.Name] = _formatFun.Invoke(exportApi, new object[] { field, dItems });
}
else
{
//添加行字段数据
row[field.Name] = (pVal == null ? DBNull.Value : pVal);
}
}
table.Rows.Add(row);
}
if (table.Columns.Count < 256 && table.Rows.Count < 65536)
{
using (var exp = ExcelHelper.ExportDT(table, export.DisplayName))
{
var exporByt = exp.ToArray();
writeStream.Write(exporByt, 0, exporByt.Length);
}
}
else {
if (export.FileName.EndsWith(".xls")) export.FileName += "x";
using (var exp = ExcelHelper.ExportDT_X(table, export.DisplayName))
{
var exporByt = exp.ToArray();
writeStream.Write(exporByt, 0, exporByt.Length);
}
}
table.Dispose();
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
content.Headers.ContentType = new MediaTypeHeaderValue(MIME_TYPE);
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(export.FileName, Encoding.UTF8));
}
}
6.配置文件截图