using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Collections.Generic;
using CarlosAg.ExcelXmlWriter;
namespace ExcelExp
{ /// <summary>
///Excel主处理类
/// </summary>
public class ExcelBook
{
private Workbook _book = new Workbook();
private DataTable _dataTable = null;
private string _title = "";
private Page _page = null;
private List<ExcelColumnCollection> _columnNamesCollection = new List<ExcelColumnCollection>();
private SortedList<string, int> _maxLengthOfField = new SortedList<string, int>();
private bool _isAutoFitWidth = true;
private string _Author = "";
private string _LastAuthor = "";
private string _Company = "";
private string _Version = "11.6408";
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="dt">DataTable形式的数据源</param>
/// <param name="title">Excel显示标题</param>
public ExcelBook(DataTable dt, string title)
{
Page page = (Page)HttpContext.Current.Handler;
if (dt == null)
{
throw new Exception("数据源为空");
}
_dataTable = dt;
_title = title;
_page = page;
}
/**//// <summary>
/// 以GridView的Head为标题
/// </summary>
/// <param name="row">GridView表头行对象</param>
public void SetColumnNameFromGridViewHeadRow(GridViewRow row)
{
ExcelColumnCollection excelcols = new ExcelColumnCollection();
_columnNamesCollection.Add(excelcols);
foreach (TableCell cell in row.Cells)
{
excelcols.Add(new ExcelColumn(cell.Text));
}
}
/**//// <summary>
/// 初始化Excel Workbook
/// </summary>
/// <param name="book">book</param>
private void InitializeBook(Workbook book)
{
book.Properties.Author = Author;//作者
book.Properties.LastAuthor = LastAuthor;//最后作者
book.Properties.Created = DateTime.Now;//创建时间
book.Properties.Company = Company;//公司
book.Properties.Version = Version;//版本
book.ExcelWorkbook.WindowHeight = 13500;
book.ExcelWorkbook.WindowWidth = 17100;
book.ExcelWorkbook.WindowTopX = 360;
book.ExcelWorkbook.WindowTopY = 75;
book.ExcelWorkbook.ProtectWindows = false;
book.ExcelWorkbook.ProtectStructure = false;
}
/**//// <summary>
/// 设置样式
/// </summary>
/// <param name="styles">样式集合</param>
private void SetStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "宋体";
Default.Font.Size = 12;
Default.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// TitleStyle
// -----------------------------------------------
WorksheetStyle TitleStyle = styles.Add("TitleStyle");
TitleStyle.Font.Bold = true;
TitleStyle.Font.FontName = "黑体";
TitleStyle.Font.Size = 14;
TitleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
TitleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// FieldStyle
// -----------------------------------------------
WorksheetStyle FieldStyle = styles.Add("FieldStyle");
FieldStyle.Font.Bold = true;
FieldStyle.Font.FontName = "宋体";
FieldStyle.Font.Size = 12;
FieldStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
FieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// LastFieldStyle
// -----------------------------------------------
WorksheetStyle LastFieldStyle = styles.Add("LastFieldStyle");
LastFieldStyle.Font.Bold = true;
LastFieldStyle.Font.FontName = "宋体";
LastFieldStyle.Font.Size = 12;
LastFieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// DataStyle
// -----------------------------------------------
WorksheetStyle DataStyle = styles.Add("DataStyle");
DataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
}
/**//// <summary>
/// 设置Excel Sheet
/// </summary>
/// <param name="sheets">sheets集合</param>
private void SetSheels(WorksheetCollection sheets)
{
Worksheet sheet = sheets.Add(_title);
sheet.Table.DefaultRowHeight = 14.25F;
sheet.Table.DefaultColumnWidth = 54F;
sheet.Table.FullColumns = 1;
sheet.Table.FullRows = 1;
// -----------------------------------------------
WorksheetRow row = null;
WorksheetCell cell = null;
#region 大标题
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
row.Height = 30;
cell = row.Cells.Add();
cell.StyleID = "TitleStyle";
cell.Data.Type = DataType.String;
cell.Data.Text = _title;
cell.MergeAcross = _dataTable.Columns.Count - 1;
#endregion
foreach (DataColumn dc in _dataTable.Columns)//初始化列宽度集合
{
_maxLengthOfField[dc.ColumnName] = 0;
}
//-----------------------------------------------字段
#region 字段标题行
if (_columnNamesCollection.Count != 0)
{
for (int i = 0; i < _columnNamesCollection.Count; i++)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
int j = 0;
foreach (ExcelColumn ec in _columnNamesCollection[i])
{
cell = row.Cells.Add();
cell.Data.Text = ec.Name;
cell.Data.Type = DataType.String;
if (i != _columnNamesCollection.Count - 1)
{
cell.MergeAcross = ec.MergeAcross;
cell.StyleID = "FieldStyle";
}
else//最下层标题行
{
cell.StyleID = "LastFieldStyle";
_maxLengthOfField[_dataTable.Columns[j++].ColumnName] =
GetMaxLength(_maxLengthOfField[_dataTable.Columns[j++].ColumnName], ec.Name);
}
}
}
}
else
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable.Columns)
{
cell = row.Cells.Add();
cell.Data.Text = dc.ColumnName;
cell.Data.Type = DataType.String;
cell.StyleID = "FieldStyle";
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName],dc.ColumnName);
}
}
#endregion
// -----------------------------------------------
#region 数据行
object dcValueO = null;
string dcValueS = null;
foreach (DataRow dr in _dataTable.Rows)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable.Columns)
{
dcValueO = dr[dc];
if (dcValueO == DBNull.Value)
dcValueS = string.Empty;
else
dcValueS = dcValueO.ToString();
cell = row.Cells.Add();
cell.Data.Text = dcValueS;
cell.Data.Type = TypeConvert(dc.DataType);
cell.StyleID = "DataStyle";
if (_isAutoFitWidth || _columnNamesCollection.Count == 0)
{
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dcValueS);
}
}
}
#endregion
// -----------------------------------------------
#region 设置列
WorksheetColumn column = null;
if (!_isAutoFitWidth && _columnNamesCollection.Count != 0)
{
foreach (ExcelColumn ec in _columnNamesCollection[_columnNamesCollection.Count - 1])
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = ec.Width;
sheet.Table.Columns.Add(column);
}
}
else
{
foreach (DataColumn dc in _dataTable.Columns)
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = _maxLengthOfField[dc.ColumnName] * 7;
sheet.Table.Columns.Add(column);
}
}
#endregion
// Options
// -----------------------------------------------
sheet.Options.Selected = true;
sheet.Options.ProtectObjects = false;
sheet.Options.ProtectScenarios = false;
sheet.Options.Print.PaperSizeIndex = 9;
sheet.Options.Print.HorizontalResolution = 300;
sheet.Options.Print.VerticalResolution = 300;
sheet.Options.Print.ValidPrinterInfo = true;
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
/// <param name="downloadFileName">下载时显示的文件名称</param>
public void WriteExcelToClient(string downloadFileName)
{
string fileName = string.IsNullOrEmpty(downloadFileName) ?
(string.IsNullOrEmpty(_title) ? "未命名文件" : _title) : downloadFileName;
InitializeBook(_book);
SetStyles(_book.Styles);
SetSheels(_book.Worksheets);
_book.Save(_page.Response.OutputStream);
_page.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" +
HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls;");
_page.Response.ContentEncoding = System.Text.Encoding.UTF8;
_page.Response.Charset = "UTF-8";
_page.Response.Flush();
_page.Response.End();
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
public void WriteExcelToClient()
{
WriteExcelToClient(null);
}
/**//// <summary>
/// 数据类型转换
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private DataType TypeConvert(Type type)
{
switch (type.Name)
{
case "Decimal":
case "Double":
case "Single":
return DataType.Number;
case "Int16":
case "Int32":
case "Int64":
case "SByte":
case "UInt16":
case "UInt32":
case "UInt64":
return DataType.Number;
case "String":
return DataType.String;
case "DateTime":
return DataType.String;
default:
return DataType.String;
}
}
/**//// <summary>
/// 添加标题行 集合
/// </summary>
/// <param name="exColumnCollection"></param>
public void AddColumnNamesCollection(ExcelColumnCollection exColumnCollection)
{
_columnNamesCollection.Add(exColumnCollection);
}
/**//// <summary>
/// 清楚标题行集合
/// </summary>
public void ClearColumnNamesCollection()
{
_columnNamesCollection.Clear();
}
/**//// <summary>
/// 列宽是否自适应
/// </summary>
public bool IsAutoFitWidth
{
get { return _isAutoFitWidth; }
set { _isAutoFitWidth = value; }
}
public string Author
{
get { return _Author; }
set { _Author = value; }
}
public string LastAuthor
{
get { return _LastAuthor; }
set { _LastAuthor = value; }
}
public string Company
{
get { return _Company; }
set { _Company = value; }
}
public string Version
{
get { return _Version; }
set { _Version = value; }
}
/**//// <summary>
/// 获取字段最大宽度函数
/// </summary>
/// <param name="oldLength">原来长度</param>
/// <param name="str">当前字符串</param>
/// <returns>最大值</returns>
private int GetMaxLength(int oldLength, string str)
{
if (str == null) str = "";
byte[] bs = System.Text.Encoding.Default.GetBytes(str.Trim());
int newLength = bs.Length;
if (oldLength > newLength)
return oldLength;
else
return newLength;
}
}
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace ExcelExp
{
/// <summary>
/// Excel表头字段类用于处理自定义表头时使用
/// </summary>
public class ExcelColumn
{
private int _width = 150;//默认宽度
private string _name = string.Empty;//列名
private int _mergeAcross = 0;//跨越字段
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="width">列宽</param>
/// <param name="name">列名</param>
public ExcelColumn(int width, string name)
{
this._width = width;
this._name = name;
this._mergeAcross = 0;
}
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="name">列名</param>
/// <param name="colspan">合并列数</param>
public ExcelColumn(string name, int colspan)
{
this._name = name;
this._mergeAcross = colspan - 1;
}
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="name">列名</param>
public ExcelColumn(string name)
{
this._name = name;
}
public int Width
{
get { return _width; }
}
public string Name
{
get { return _name; }
}
public int MergeAcross
{
get { return _mergeAcross; }
}
}
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace ExcelExp
{
/**/
/// <summary>
/// Excel表头字段集合，用于添加Excel表头
/// </summary>
public class ExcelColumnCollection : System.Collections.CollectionBase
{
public ExcelColumnCollection()
{
}
/**/
/// <summary>
/// 将对象添加到 ExcelColumnCollection 的结尾处。
/// </summary>
/// <param name="value">要添加到 ExcelColumnCollection 的末尾处的 <see cref="ExcelColumn"/>。</param>
/// <returns>ExcelColumnCollection 索引，已在此处添加了 value。</returns>
public int Add(ExcelColumn value)
{
return (List.Add(value));
}
/**/
/// <summary>
/// 搜索指定的 <see cref="ExcelColumn"/>，并返回整个 ExcelColumnCollection 中第一个匹配项的从零开始的索引。
/// </summary>
/// <param name="value">要在 ExcelColumnCollection 中查找的 <see cref="ExcelColumn"/>。</param>
/// <returns>如果在整个 ExcelColumnCollection 中找到 value 的第一个匹配项，则为该项的从零开始的索引；否则为 -1。</returns>
public int IndexOf(ExcelColumn value)
{
return (List.IndexOf(value));
}
/**/
/// <summary>
/// 从 RoleCollection 中移除特定对象的第一个匹配项。
/// </summary>
/// <param name="value">要从 <see cref="ExcelColumnCollection"/> 移除的 <see cref="ExcelColumn"/>。</param>
///
/// <exception cref="System.ArgumentException">未在 ExcelColumnCollection 对象中找到 value 参数。</exception>
/// <exception cref="System.NotSupportedException">ExcelColumnCollection 为只读，或 ExcelColumnCollection 具有固定大小。
/// </exception>
public void Remove(ExcelColumn value)
{
List.Remove(value);
}
/**/
/// <summary>
/// 获取或设置指定索引处的元素。
/// </summary>
/// <param name="index">要获得或设置的元素从零开始的索引。</param>
/// <returns>指定索引处的元素。</returns>
/// <exception cref="System.ArgumentOutOfRangeException">index 小于零。
/// - 或 -
/// index 等于或大于 Count。
/// </exception>
public ExcelColumn this[int index]
{
get
{
return (ExcelColumn)List[index];
}
set
{
List[index] = value;
}
}
/**/
/// <summary>
/// 获取或设置指定 关键字 的元素。
/// </summary>
/// <param name="nodeText">要获得或设置的元素的关键字。</param>
/// <returns>如果在整个 ItemNodeCollection 中找到 关键字 的第一个匹配项，则为该项的元素；否则为 null。</returns>
/// <exception cref="System.ArgumentException">设置未成功，集合中未找到指定关键字的元素。</exception>
public ExcelColumn this[string name]
{
get
{
ExcelColumn excelColumn;
for (int i = 0; i < List.Count; i++)
{
excelColumn = (ExcelColumn)List[i];
if (excelColumn.Name == name)
{
return excelColumn;
}
}
return null;
}
set
{
ExcelColumn excelColumn;
for (int i = 0; i < List.Count; i++)
{
excelColumn = (ExcelColumn)List[i];
if (excelColumn.Name == name)
{
excelColumn = value;
return;
}
}
throw new ArgumentException("设置未成功，集合中未找到指定关键字的元素。");
}
}
}
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Collections.Generic;
using CarlosAg.ExcelXmlWriter;
namespace ExcelExp
{
/// <summary>
///ExcelMoreBook导出多个sheet表
/// </summary>
public class ExcelMoreBook
{
private Workbook _book = new Workbook();
private List<DataTable> _dataTable = null;
private List<string> _title =null;
private Page _page = null;
private List<ExcelColumnCollection> _columnNamesCollection = new List<ExcelColumnCollection>();
private SortedList<string, int> _maxLengthOfField = new SortedList<string, int>();
private bool _isAutoFitWidth = true;
private string _Author = "";
private string _LastAuthor = "";
private string _Company = "";
private string _Version = "11.6408";
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="dt">List(数据源)</param>
/// <param name="title">List(Excel显示标题)</param>
public ExcelMoreBook(List<DataTable> dt, List<string> title)
{
Page page = (Page)HttpContext.Current.Handler;
if (dt.Count == 0)
{
throw new Exception("数据源为空");
}
_dataTable = dt;
_title = title;
_page = page;
}
/**//// <summary>
/// 以GridView的Head为标题
/// </summary>
/// <param name="row">GridView表头行对象</param>
public void SetColumnNameFromGridViewHeadRow(GridViewRow row)
{
ExcelColumnCollection excelcols = new ExcelColumnCollection();
_columnNamesCollection.Add(excelcols);
foreach (TableCell cell in row.Cells)
{
excelcols.Add(new ExcelColumn(cell.Text));
}
}
/**//// <summary>
/// 初始化Excel Workbook
/// </summary>
/// <param name="book">book</param>
private void InitializeBook(Workbook book)
{
book.Properties.Author = Author;//作者
book.Properties.LastAuthor = LastAuthor;//最后作者
book.Properties.Created = DateTime.Now;//创建时间
book.Properties.Company = Company;//公司
book.Properties.Version = Version;//版本
book.ExcelWorkbook.WindowHeight = 13500;
book.ExcelWorkbook.WindowWidth = 17100;
book.ExcelWorkbook.WindowTopX = 360;
book.ExcelWorkbook.WindowTopY = 75;
book.ExcelWorkbook.ProtectWindows = false;
book.ExcelWorkbook.ProtectStructure = false;
}
/**//// <summary>
/// 设置样式
/// </summary>
/// <param name="styles">样式集合</param>
private void SetStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "宋体";
Default.Font.Size = 12;
Default.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// TitleStyle
// -----------------------------------------------
WorksheetStyle TitleStyle = styles.Add("TitleStyle");
TitleStyle.Font.Bold = true;
TitleStyle.Font.FontName = "黑体";
TitleStyle.Font.Size = 14;
TitleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
TitleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// FieldStyle
// -----------------------------------------------
WorksheetStyle FieldStyle = styles.Add("FieldStyle");
FieldStyle.Font.Bold = true;
FieldStyle.Font.FontName = "宋体";
FieldStyle.Font.Size = 12;
FieldStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
FieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// LastFieldStyle
// -----------------------------------------------
WorksheetStyle LastFieldStyle = styles.Add("LastFieldStyle");
LastFieldStyle.Font.Bold = true;
LastFieldStyle.Font.FontName = "宋体";
LastFieldStyle.Font.Size = 12;
LastFieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// DataStyle
// -----------------------------------------------
WorksheetStyle DataStyle = styles.Add("DataStyle");
DataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
}
/**//// <summary>
/// 设置Excel Sheet
/// </summary>
/// <param name="sheets">sheets集合</param>
private void SetSheels(WorksheetCollection sheets)
{
for (int i = 0; i < _title.Count;i++ )
{
Worksheet sheet = sheets.Add(_title[i].ToString());
sheet.Table.DefaultRowHeight = 14.25F;
sheet.Table.DefaultColumnWidth = 54F;
sheet.Table.FullColumns = 1;
sheet.Table.FullRows = 1;
// -----------------------------------------------
WorksheetRow row = null;
WorksheetCell cell = null;
#region 大标题
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
row.Height = 30;
cell = row.Cells.Add();
cell.StyleID = "TitleStyle";
cell.Data.Type = DataType.String;
cell.Data.Text = _title[i];
cell.MergeAcross = _dataTable[i].Columns.Count - 1;
#endregion
foreach (DataColumn dc in _dataTable[i].Columns)//初始化列宽度集合
{
_maxLengthOfField[dc.ColumnName] = 0;
}
//-----------------------------------------------字段
#region 字段标题行
if (_columnNamesCollection.Count != 0)
{
for (int i_ = 0; i_ < _columnNamesCollection.Count; i_++)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
int j = 0;
foreach (ExcelColumn ec in _columnNamesCollection[i_])
{
cell = row.Cells.Add();
cell.Data.Text = ec.Name;
cell.Data.Type = DataType.String;
if (i_ != _columnNamesCollection.Count - 1)
{
cell.MergeAcross = ec.MergeAcross;
cell.StyleID = "FieldStyle";
}
else//最下层标题行
{
cell.StyleID = "LastFieldStyle";
_maxLengthOfField[_dataTable[i].Columns[j++].ColumnName] =
GetMaxLength(_maxLengthOfField[_dataTable[i].Columns[j++].ColumnName], ec.Name);
}
}
}
}
else
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable[i].Columns)
{
cell = row.Cells.Add();
cell.Data.Text = dc.ColumnName;
cell.Data.Type = DataType.String;
cell.StyleID = "FieldStyle";
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dc.ColumnName);
}
}
#endregion
// -----------------------------------------------
#region 数据行
object dcValueO = null;
string dcValueS = null;
foreach (DataRow dr in _dataTable[i].Rows)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable[i].Columns)
{
dcValueO = dr[dc];
if (dcValueO == DBNull.Value)
dcValueS = string.Empty;
else
dcValueS = dcValueO.ToString();
cell = row.Cells.Add();
cell.Data.Text = dcValueS;
cell.Data.Type = TypeConvert(dc.DataType);
cell.StyleID = "DataStyle";
if (_isAutoFitWidth || _columnNamesCollection.Count == 0)
{
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dcValueS);
}
}
}
#endregion
// -----------------------------------------------
#region 设置列
WorksheetColumn column = null;
if (!_isAutoFitWidth && _columnNamesCollection.Count != 0)
{
foreach (ExcelColumn ec in _columnNamesCollection[_columnNamesCollection.Count - 1])
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = ec.Width;
sheet.Table.Columns.Add(column);
}
}
else
{
foreach (DataColumn dc in _dataTable[i].Columns)
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = _maxLengthOfField[dc.ColumnName] * 7;
sheet.Table.Columns.Add(column);
}
}
#endregion
// Options
// -----------------------------------------------
sheet.Options.Selected = true;
sheet.Options.ProtectObjects = false;
sheet.Options.ProtectScenarios = false;
sheet.Options.Print.PaperSizeIndex = 9;
sheet.Options.Print.HorizontalResolution = 300;
sheet.Options.Print.VerticalResolution = 300;
sheet.Options.Print.ValidPrinterInfo = true;
}
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
/// <param name="downloadFileName">下载时显示的文件名称</param>
public void WriteExcelToClient(string downloadFileName)
{
string fileName = string.IsNullOrEmpty(downloadFileName) ?
(string.IsNullOrEmpty(_title[0]) ? "未命名文件" : _title[0]) : downloadFileName;
InitializeBook(_book);
SetStyles(_book.Styles);
SetSheels(_book.Worksheets);
_book.Save(_page.Response.OutputStream);
_page.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" +
HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls;");
_page.Response.ContentEncoding = System.Text.Encoding.UTF8;
_page.Response.Charset = "UTF-8";
_page.Response.Flush();
_page.Response.End();
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
public void WriteExcelToClient()
{
WriteExcelToClient(null);
}
/**//// <summary>
/// 数据类型转换
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private DataType TypeConvert(Type type)
{
switch (type.Name)
{
case "Decimal":
case "Double":
case "Single":
return DataType.Number;
case "Int16":
case "Int32":
case "Int64":
case "SByte":
case "UInt16":
case "UInt32":
case "UInt64":
return DataType.Number;
case "String":
return DataType.String;
case "DateTime":
return DataType.String;
default:
return DataType.String;
}
}
/**//// <summary>
/// 添加标题行 集合
/// </summary>
/// <param name="exColumnCollection"></param>
public void AddColumnNamesCollection(ExcelColumnCollection exColumnCollection)
{
_columnNamesCollection.Add(exColumnCollection);
}
/**//// <summary>
/// 清楚标题行集合
/// </summary>
public void ClearColumnNamesCollection()
{
_columnNamesCollection.Clear();
}
/**//// <summary>
/// 列宽是否自适应
/// </summary>
public bool IsAutoFitWidth
{
get { return _isAutoFitWidth; }
set { _isAutoFitWidth = value; }
}
public string Author
{
get { return _Author; }
set { _Author = value; }
}
public string LastAuthor
{
get { return _LastAuthor; }
set { _LastAuthor = value; }
}
public string Company
{
get { return _Company; }
set { _Company = value; }
}
public string Version
{
get { return _Version; }
set { _Version = value; }
}
/**//// <summary>
/// 获取字段最大宽度函数
/// </summary>
/// <param name="oldLength">原来长度</param>
/// <param name="str">当前字符串</param>
/// <returns>最大值</returns>
private int GetMaxLength(int oldLength, string str)
{
if (str == null) str = "";
byte[] bs = System.Text.Encoding.Default.GetBytes(str.Trim());
int newLength = bs.Length;
if (oldLength > newLength)
return oldLength;
else
return newLength;
}
}
}
1、问题背景 有一个学生表，需要导出到Excel，有学号、姓名、性别和年龄四个字段2、实现源码/** * * @Project:Report * @Title:ExcelExport.java * @Package:com.you.excel * @Description: * @Author:...
其实导出可以分两种，一种是直接将页面的数据导出（即前台导出），一种是点击“导出”后再次请求后台，然后封装数据进行导出（即后台导出）。前台导出虽然可以不用再次请求数据，但其不足也是明显，如分页表格就无法...
vue-element-admin项目使用Export2Excel导出excel样式修改 参考链接: https://www.cnblogs.com/yinxingen/p/11052184.html. 主要是在vue-element-admin自带Export2Excel模板的基础上进行修改，进行单元格宽度，边框...
xlsx-style 为xlsx库添加样式，比如字体颜色，大小，行宽等。但是只支持xlsx、xlsm、xlsb格式 FileSaver.js 负责下载保存文件。对各种兼容性比较好，对跨域文件也有处理 lodash js常用工具库 需要注意的地方 一、...
1、问题背景 Java导出Excel表格时，表头出现了三个，即多表头Excel2、实现源码/** * * @Project:Report * @Title:ThreeHead.java * @Package:com.you.excel * @Description: * @Author:YouHaiDong * @Date:...
1.多表头导出最多支持到三行，表头格式说明 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',') 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 ...
此小工具主要功能为将iview表格数据导出为Excel文件，并在之前的基础上可将多个表分页导出为一个Excel文件，如下图 下面是使用流程 首先先安装依赖 npm下 npm install -S file-saver xlsx npm install -D script-...
先装包 安装注意 import XLSX from "xlsx-style"报错：This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-...需要修改源码： 在\node_modules\xlsx-style\dist\cpexcel.js ...
1.首先安装以下三个安装包 npm install --save xlsx(修改样式需要下载npm install --save xlsx-style) npm install -S file-saver npm install -D script-loader 注意：如果安装了npm install --save xlsx-style ...
使用Export2Excel导出表格，导出多个sheet功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右...
市面上有很多封装好的导出工具(如：阿里的easyExcel，GitHub上xxl-excel等)，但如果直接引用依赖，扩展性和定制性比较差，所以博主通过apache.poi，自己实现一款Excel导出工具，方便定制使用。本工具类支持SpringM.....
应项目需要，前端直接导出表格中的数据。 百度找到了table2excel ，很实用，但是导出的表格没有边框，且表格中的数据没有居中。 网上没找到对应的办法，就自己对table2excel.js做了修改，能够实现导出的表格有边框，...
所以交给前端来实现，导出表格，需要实现：1、支持多个sheet，并且有自己的name2、根据要求合并单元格，设置单元格的宽高3、在表格内有各自的二维码4、打印的页边距为左右各0.5cm5、打印多页的话，固定表头6、设置...
项目中经常需要导出excel，有时还有汇总需求。但导出方式都一样，而汇总方式不同。可以参考以下方式，使用标准的导出方式，和自定义的汇总方式。 1.首先NuGet引用NPOI version 2.3.0 2.导出表格源码 /// <...
目录前言功能简介功能展示1. 一对多数据关系， 生成多个...7. 设置样式（对齐方式、边框、边框颜色、前景色、填充方式）【全局设置、局部设置】8. 设置字体（字体大小、字体名字、字体颜色）【全局设置、局部设置...
excel导出网上参考了一些代码感觉写的都比较复杂,就自己看了一下poi的api自己封装了一段代码(如下) 目前，比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和JavaExcel 此功能就是poi试下的 POI简介:...
首先导入poi包 <!--excel poi--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version&...
前一段时间我写过通用的导入Excel，前几天也写了导出pdf格式的，还有我之前搞得导出Word，我在之前的博客也都介绍了导出和导入是一个道理，无非是一个获取一个是赋值。昨天有一位同仁看了我的Excel通用导入导出那个...
最近把数据导出为Excel文件并保存到本地，由于有很多功能都会用到导出功能，所以就简单封装了一个导出框架，分享给大家。 导出框架思路： 1.一个Map对应一条数据记录 2.多条数据存放在List对象中 3.为了简化...
今天公司要做导出功能，所以就在SSM下研究了下 发现还挺简单的做了个工具类以便自己以后使用 使用的第三方架包是POI,使用的框架是SSM 第三方架包下载地址: POI官网 如果是使用的maven创建的项目，直接导入依赖就好...
最近工作导数据失败，找问题原因：ORALCE导出数据，超出5w多条数据，由于数据量过大（加上列数多），无法导出。 PLSQL工具导出EXCEL失败，卡死白屏。 使用NPOI第三方类导出EXCEL失败，超出100+M多的数据就抛出异常...
前些日子做了一个简单的winform程序，需要导出的功能，刚开始省事直接使用微软的组件，但是导出之后发现效率极其低下，绝对像web那样使用npoi组件，因此简单的进行了整理，包括直接根据DataTable导出excel及...
下面主要给大家分享一下该工具类的使用方法与实现思路。 实现的功能点 对于每个报表都相同的操作，我们很自然的会抽离出来，这个很简单。而最重要的是：如何把那些每个报表不相同的操作进行良好的封装，尽可能的...
Java导出Excel（附完整源码）前言1. idea创建spring boot项目2. 修改pom文件，添加依赖；3. 新建一个实体类，命名为User.java；4. 新建一个excel样式工具类；5. 新建一个下载文件的通用controller；6. 新建一个...
关于 SheetJS 导出Excel 数据和标题显示问题解决 GitHub 英文文档：https://github.com/SheetJS/sheetjs GitHub 中文文档：https://github.com/rockboom/SheetJS-docs-zh-CN/blob/master/README.md 1.在浏览器里使用...
项目中经常要用到Excel表格的导出导入。故此，写了一个测试。现在奉上源码。 项目的目录结构: 目录结构截图 页面展示截图： 导出截图 导入截图成功插入数据库 现在给出全部源码： 一： PoiService.java...
今天捣鼓了一天NPOI导出excel终于把制作
java 使用poi-ooxml 3.17 excel的通用导入导出，导出的样式未做处理，可自行处理 可以在csdn下载或者在gitHub（https://github.com/aojd/excel-importAndExport.git）git clone 也可以在csdn搜索 【java poi-ooxml...
本套Java视频完全针对初级学员，课堂实录，自发布以来，好评如潮！Java视频中注重与学生互动，讲授幽默诙谐、细致入微，覆盖Java基础所有核心知识点，同类Java视频中也是代码量大、案例多、实战性强的。同时，本Java视频教程注重技术原理剖析，深入JDK源码，辅以代码实战贯穿始终，用实践驱动理论，并辅以必要的代码练习。 通过20的课程学习，使学员掌握java核心语法、面向对象思想编程、异常处理、IO流、集合类、多线程、网络编程等。
C语言源代码大全，有源代码上百个
