--------------------------导入---------------------------------------------
protected void btnImport_Click(object sender, EventArgs e)
{
//验证文件后缀名
if (string.IsNullOrEmpty(fldupload.FileName))
{
base.MessageBox("请选择文件路径!");
return;
}
string extension = System.IO.Path.GetExtension(fldupload.FileName);
if (extension != ".xls" && extension != ".xlsx")
{
base.MessageBox("不支持的文件类型,请选择Excel文件!");
return;
}
string fileNewName = fldupload.FileName.Substring(0, fldupload.FileName.LastIndexOf('.')) + DateTime.Now.ToString("yyyyMMddHHmmss") + fldupload.FileName.Substring(fldupload.FileName.LastIndexOf('.'));
string FilePath = "~/UploadFiles/Attachments";
string excelPath = HttpContext.Current.Server.MapPath(FilePath);
fldupload.SaveAs(excelPath + fileNewName);
//读取Excel文件，将数据放入DataTable
DataTable dt = ReadDataToTable(fileNewName);
if (dt != null)
{
//如果验证通过，则存入数据库
string err = CheckExcelData(dt);
if (err == "")
{
int insertnum = 0;
int updatenum = 0;
//获得数据
foreach (DataRow dr in dt.Rows)
{
if (dr["费用中心编码"].ToString().Trim() != "")
{
//判断是否存在
var existRlation = 查询数据库;
if (existRlation != null && !string.IsNullOrEmpty(existRlation.关键字))
{
//判断数据是否和之前数据一致
if (existRlation.字段名== dr["excel列名"].ToString().Trim()
)
{
continue;
}
else
{
existRlation.字段名= dr["excel列名"].ToString().Trim();
existRlation.Persist();
updatenum++;
}
}
else
{
XXX BusiRlation = XXX.RetrieveNew();
BusiRlation.字段名= dr["excel列名"].ToString().Trim();
BusiRlation.Persist();
insertnum++;
}
}
}
base.MessageBox("上传成功!共新增" + insertnum + "条，更新" + updatenum + "条");
this.BindGrid(0);
}
//否则显示消息
else
{
base.MessageBox("上传失败,错误原因:" + err);
}
}
else
{
base.MessageBox("您导入的Excel文档无数据！");
}
}
#region 读取excel到datatable方法
/// <summary>
/// 读取Excel文件，将数据放入DataTable
/// </summary>
private DataTable ReadDataToTable(string fileName)
{
DataTable dtNew = new DataTable();
string excelPath = HttpContext.Current.Server.MapPath("~/xxx/xxxs") + fileName;
string strConn = "";
string Extension = Path.GetExtension(fileName);
if (Extension == ".xls")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;MAXSCANROWS=1000;MINSCANROWS=1000;HDR=YES;IMEX=1\";";
else
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;MAXSCANROWS=1000;MINSCANROWS=1000;HDR=YES;IMEX=1\";";
strConn = strConn + "Data Source=" + excelPath;
OleDbConnection oleConn = new OleDbConnection(strConn);
oleConn.Open();
DataTable dtSheets = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtSheets.Rows.Count > 0)
{
OleDbDataAdapter oleDap = new OleDbDataAdapter("select * from [" + dtSheets.Rows[0]["TABLE_NAME"].ToString() + "]", strConn);
oleDap.Fill(dtNew);
oleConn.Close();
FileInfo fi = new FileInfo(excelPath);
fi.Delete();
return dtNew;
}
else
{
return null;
}
}
#endregion
#region 检测excel里面的错误方法
/// <summary>
/// 检测excel里面的错误
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private string CheckExcelData(DataTable dt)
{
string errorMsg = "";
if (!dt.Columns.Contains("Excel列名")
)
{
errorMsg = "表格列名格式不正确!";
}
return errorMsg;
}
#endregion
--------------------------导出---------------------------------------------
Dictionary<string, string> dictionary = new Dictionary<string, string>();
dictionary.Add("Excel列名", "字段名");
IList<xxx> list = xxx.RetrieveListByWhere(this.BuildWhere());
ExcelHelp<xxx>.ExcelOut(list, dictionary, "dddd" + DateTime.Now.ToString("yyyyMMddHHmmss"));
------------------------------帮助类-----------------------------------------
public class ExcelHelp<T>
{
#region NPOI Excel导出功能
/// <summary>
/// NPOI Excel导出功能
/// </summary>
/// <param name="arr">实体类集合</param>
/// <param name="ky">需要显示的名称对应的列名</param>
public static void ExcelOut(IList<T> arr, Dictionary<string, string> dictionary, string fileName)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄
ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表
int columnCount = 0;
IRow rw = sheet.CreateRow(0);
#region 设置样式
var cellFont = wb.CreateFont();
var cellStyle = wb.CreateCellStyle();
cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyle.SetFont(cellFont);
#endregion
foreach (KeyValuePair<string, string> kyp in dictionary)//循环一个表头来创建第一行的表头
{
ICell ic = rw.CreateCell(columnCount);
ic.SetCellValue(kyp.Key);
ic.CellStyle = cellStyle; //- 设置单元格的独立样式
columnCount++;
}
Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型
PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性
for (int i = 0; i < arr.Count; i++)//循环实体泛型集合
{
rw = sheet.CreateRow(i + 1);//创建一个新行，把传入集合中的每条数据创建一行
foreach (PropertyInfo property in properties)//循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中）
{
int count = 0;
foreach (KeyValuePair<string, string> kyp in dictionary)//循环需要导出属性值 的 属性名
{
string attry = kyp.Value;//获得一个需要导入的属性名；
if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0)//如果需要导出的属性名和当前循环实体的属性名一样，
{
object result = property.GetValue(arr[i], null);//获取当前循环的实体属性在当前实体对象（arr[i]）的值
//创建单元格并进行赋值
if (result == null)
{
rw.CreateCell(count).SetCellValue(string.Empty);
}
else
{
rw.CreateCell(count).SetCellValue(result.ToString());
}
}
count++;
}
}
}
MemoryStream file = new MemoryStream();
wb.Write(file);
string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
if (UserAgent.IndexOf("firefox") > 0)
{
Encoding eGB3212 = Encoding.GetEncoding("GB2312");
fileName = eGB3212.GetString(eGB3212.GetBytes(fileName));
}
else
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream);
}
catch (Exception ex)
{
throw new Exception("在导出Excel时文件出错啦=====" + ex.Message);
}
}
public static void ExcelOut(IList<T> arr, Dictionary<string, string> dictionary, string title, string fileName)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄
ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表
int columnCount = 0;
#region 设置标题样式
var titleCellFont = wb.CreateFont();
var titleStyle = wb.CreateCellStyle();
titleCellFont.FontHeightInPoints = 14;
titleCellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
titleStyle.SetFont(titleCellFont);
titleStyle.Alignment = HorizontalAlignment.CENTER;
titleStyle.VerticalAlignment = VerticalAlignment.CENTER;
titleStyle.WrapText = true;
#endregion
#region 设置列头样式
var columnTitleCellFont = wb.CreateFont();
var columnTitleStyle = wb.CreateCellStyle();
columnTitleCellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
columnTitleStyle.SetFont(columnTitleCellFont);
#endregion
ICell titleCell = sheet.CreateRow(0).CreateCell(0);
titleCell.CellStyle = titleStyle;
titleCell.SetCellValue(title);
sheet.GetRow(0).HeightInPoints = 50;
IRow rw = sheet.CreateRow(1);
foreach (KeyValuePair<string, string> kyp in dictionary)//循环一个表头来创建第一行的表头
{
ICell ic = rw.CreateCell(columnCount);
ic.SetCellValue(kyp.Key);
ic.CellStyle = columnTitleStyle; //- 设置单元格的独立样式
columnCount++;
}
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columnCount - 1));
Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型
PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性
for (int i = 0; i < arr.Count; i++)//循环实体泛型集合
{
rw = sheet.CreateRow(i + 2);//创建一个新行，把传入集合中的每条数据创建一行
foreach (PropertyInfo property in properties)//循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中）
{
int count = 0;
foreach (KeyValuePair<string, string> kyp in dictionary)//循环需要导出属性值 的 属性名
{
string attry = kyp.Value;//获得一个需要导入的属性名；
if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0)//如果需要导出的属性名和当前循环实体的属性名一样，
{
object result = property.GetValue(arr[i], null);//获取当前循环的实体属性在当前实体对象（arr[i]）的值
//创建单元格并进行赋值
if (result == null)
{
rw.CreateCell(count).SetCellValue(string.Empty);
}
else
{
rw.CreateCell(count).SetCellValue(result.ToString());
}
}
count++;
}
}
}
MemoryStream file = new MemoryStream();
wb.Write(file);
string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
if (UserAgent.IndexOf("firefox") > 0)
{
Encoding eGB3212 = Encoding.GetEncoding("GB2312");
fileName = eGB3212.GetString(eGB3212.GetBytes(fileName));
}
else
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream);
}
catch (Exception ex)
{
throw new Exception("在导出Excel时文件出错啦=====" + ex.Message);
}
}
#endregion
}
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.IO;using System.Reflection;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Text;using NPOI.SS.Util;using System.Data;using Com.Movitech.BPM.AbstractModel.Base;using System.Collections;
namespace Com.Movitech.BPM.BLL{ public class ExcelHelp<T> { #region NPOI Excel导出功能 /// <summary> /// NPOI Excel导出功能 /// </summary> /// <param name="arr">实体类集合</param> /// <param name="ky">需要显示的名称对应的列名</param> public static void ExcelOut(IList<T> arr, Dictionary<string, string> dictionary, string fileName) { try { HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄 ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表 int columnCount = 0; IRow rw = sheet.CreateRow(0); #region 设置样式 var cellFont = wb.CreateFont(); var cellStyle = wb.CreateCellStyle(); cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellStyle.SetFont(cellFont); #endregion foreach (KeyValuePair<string, string> kyp in dictionary)//循环一个表头来创建第一行的表头 { ICell ic = rw.CreateCell(columnCount); ic.SetCellValue(kyp.Key); ic.CellStyle = cellStyle; //- 设置单元格的独立样式 columnCount++; } Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型 PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性 for (int i = 0; i < arr.Count; i++)//循环实体泛型集合 { rw = sheet.CreateRow(i + 1);//创建一个新行，把传入集合中的每条数据创建一行 foreach (PropertyInfo property in properties)//循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中） { int count = 0; foreach (KeyValuePair<string, string> kyp in dictionary)//循环需要导出属性值 的 属性名 { string attry = kyp.Value;//获得一个需要导入的属性名； if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0)//如果需要导出的属性名和当前循环实体的属性名一样， { object result = property.GetValue(arr[i], null);//获取当前循环的实体属性在当前实体对象（arr[i]）的值
//创建单元格并进行赋值 if (result == null) { rw.CreateCell(count).SetCellValue(string.Empty); } else { rw.CreateCell(count).SetCellValue(result.ToString()); }
} count++; } } } MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { Encoding eGB3212 = Encoding.GetEncoding("GB2312"); fileName = eGB3212.GetString(eGB3212.GetBytes(fileName)); } else { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName)); HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream);
} catch (Exception ex) {
throw new Exception("在导出Excel时文件出错啦=====" + ex.Message); } }
public static void ExcelOut(IList<T> arr, Dictionary<string, string> dictionary, string title, string fileName) { try { HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄 ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表 int columnCount = 0; #region 设置标题样式 var titleCellFont = wb.CreateFont(); var titleStyle = wb.CreateCellStyle(); titleCellFont.FontHeightInPoints = 14; titleCellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; titleStyle.SetFont(titleCellFont); titleStyle.Alignment = HorizontalAlignment.CENTER; titleStyle.VerticalAlignment = VerticalAlignment.CENTER; titleStyle.WrapText = true; #endregion #region 设置列头样式 var columnTitleCellFont = wb.CreateFont(); var columnTitleStyle = wb.CreateCellStyle(); columnTitleCellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; columnTitleStyle.SetFont(columnTitleCellFont); #endregion ICell titleCell = sheet.CreateRow(0).CreateCell(0); titleCell.CellStyle = titleStyle; titleCell.SetCellValue(title); sheet.GetRow(0).HeightInPoints = 50; IRow rw = sheet.CreateRow(1); foreach (KeyValuePair<string, string> kyp in dictionary)//循环一个表头来创建第一行的表头 { ICell ic = rw.CreateCell(columnCount); ic.SetCellValue(kyp.Key); ic.CellStyle = columnTitleStyle; //- 设置单元格的独立样式 columnCount++; } sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columnCount - 1)); Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型 PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性 for (int i = 0; i < arr.Count; i++)//循环实体泛型集合 { rw = sheet.CreateRow(i + 2);//创建一个新行，把传入集合中的每条数据创建一行 foreach (PropertyInfo property in properties)//循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中） { int count = 0; foreach (KeyValuePair<string, string> kyp in dictionary)//循环需要导出属性值 的 属性名 { string attry = kyp.Value;//获得一个需要导入的属性名； if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0)//如果需要导出的属性名和当前循环实体的属性名一样， { object result = property.GetValue(arr[i], null);//获取当前循环的实体属性在当前实体对象（arr[i]）的值
//创建单元格并进行赋值 if (result == null) { rw.CreateCell(count).SetCellValue(string.Empty); } else { rw.CreateCell(count).SetCellValue(result.ToString()); }
} count++; } } }
MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { Encoding eGB3212 = Encoding.GetEncoding("GB2312"); fileName = eGB3212.GetString(eGB3212.GetBytes(fileName)); } else { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName)); HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream);
} catch (Exception ex) {
throw new Exception("在导出Excel时文件出错啦=====" + ex.Message); } } #endregion }
/// <summary> /// add by ben.jiang 2015年5月18日16:23:07 /// </summary> public class ExcelHelp { //导出集团审批角色 public static void ExportJTRoleData(DataTable dt, List<string> headList, string fileName) { try { HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄 ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表 int columnCount = 0; IRow rw = sheet.CreateRow(0); #region 设置样式 var cellFont = wb.CreateFont(); var cellStyle = wb.CreateCellStyle(); cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellStyle.SetFont(cellFont); #endregion foreach (string column in headList)//循环一个表头来创建第一行的表头 { ICell ic = rw.CreateCell(columnCount); ic.SetCellValue(column); ic.CellStyle = cellStyle; //- 设置单元格的独立样式 //设置宽度 if (columnCount == 0) { sheet.SetColumnWidth(columnCount, (int)((10 + 0.72) * 256)); } else { sheet.SetColumnWidth(columnCount, (int)((20 + 0.72) * 256)); } columnCount++; }
int i = 0; foreach (DataRow dr in dt.Rows) { rw = sheet.CreateRow(i + 1); rw.CreateCell(0).SetCellValue(i + 1); rw.CreateCell(1).SetCellValue(dr["RoleName"] == null ? "" : dr["RoleName"].ToString().Trim()); rw.CreateCell(2).SetCellValue(dr["RoleCode"] == null ? "" : dr["RoleCode"].ToString().Trim()); rw.CreateCell(3).SetCellValue(dr["UserName"] == null ? "" : dr["UserName"].ToString().Trim()); i++; } MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { Encoding eGB3212 = Encoding.GetEncoding("GB2312"); fileName = eGB3212.GetString(eGB3212.GetBytes(fileName)); } else { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName)); HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream); } catch (Exception ex) { throw new Exception("导出出错，请您联系系统管理员。" + ex.Message); } }
//导出地区公司审批角色/导出项目公司审批角色 public static void ExportDQRoleData(DataTable dt, List<ITblOrganization> listOrg, string fileName, string sheetName) { try { HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄 ISheet sheet = wb.CreateSheet(sheetName);//在工作薄中创建一个工作表 int columnCount = 0; IRow rw = sheet.CreateRow(0); #region 设置样式 var cellFont = wb.CreateFont(); var cellStyle = wb.CreateCellStyle(); cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellStyle.SetFont(cellFont); #endregion foreach (DataColumn column in dt.Columns)//循环一个表头来创建第一行的表头 { ICell ic = rw.CreateCell(columnCount); switch (column.ColumnName) { case "ID": ic.SetCellValue("序号"); break; case "RoleCode": ic.SetCellValue("角色编码"); break; case "RoleName": ic.SetCellValue("角色名称"); break; default: ic.SetCellValue(column.ColumnName); break;
} ic.CellStyle = cellStyle;
//设置宽度 if (columnCount == 0) { sheet.SetColumnWidth(columnCount, (int)((10 + 0.72) * 256)); } else { sheet.SetColumnWidth(columnCount, (int)((20 + 0.72) * 256)); }
columnCount++; }
int i = 0; foreach (DataRow dr in dt.Rows) { rw = sheet.CreateRow(i + 1); rw.CreateCell(0).SetCellValue(i + 1); rw.CreateCell(1).SetCellValue(dr["RoleName"] == null ? "" : dr["RoleName"].ToString().Trim()); rw.CreateCell(2).SetCellValue(dr["RoleCode"] == null ? "" : dr["RoleCode"].ToString().Trim());
int j = 3; foreach (ITblOrganization org in listOrg) { rw.CreateCell(j).SetCellValue(dr[org.OrgName] == null ? "" : dr[org.OrgName].ToString().Trim()); j++; } i++; } MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { Encoding eGB3212 = Encoding.GetEncoding("GB2312"); fileName = eGB3212.GetString(eGB3212.GetBytes(fileName)); } else { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName)); HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream); } catch (Exception ex) { throw new Exception("导出出错，请您联系系统管理员。" + ex.Message); } }
/// <summary> /// 导出组织架构信息及部门负责人 /// </summary> public static void ExportOrganizationInfo(DataTable dt,string fileName,string text, string value, string parentField, string rootid) { try { HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄 ISheet sheet = wb.CreateSheet();//在工作薄中创建一个工作表 IRow rw = sheet.CreateRow(0); #region 设置样式 var cellFont = wb.CreateFont(); var cellStyle = wb.CreateCellStyle(); cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellFont.FontHeightInPoints = 12; cellStyle.SetFont(cellFont); #endregion ICell headCell0 = rw.CreateCell(0); ICell headCell1 = rw.CreateCell(1); headCell0.SetCellValue("组织架构序列"); headCell1.SetCellValue("部门负责人"); headCell0.CellStyle = cellStyle; headCell1.CellStyle = cellStyle; sheet.SetColumnWidth(0, (int)((35 + 0.72) * 256)); sheet.SetColumnWidth(1, (int)((17 + 0.72) * 256));
ICellStyle style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.LEFT; style.WrapText = true; IFont font = wb.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; style.SetFont(font);//HEAD 样式 rw.GetCell(0).CellStyle = style; rw.GetCell(1).CellStyle = style;
ArrayList allItems = new ArrayList(); ArrayList DeptLeaderIdArr = new ArrayList(); DataRow[] rows = dt.Select(parentField + "='" + rootid+"'"); foreach (DataRow row in rows) CreateLevelAssistant(dt, ref allItems,DeptLeaderIdArr, row, string.Empty,text, value, parentField); for (int i = 0; i < allItems.Count; i++) { rw = sheet.CreateRow(i+1); rw.CreateCell(0).SetCellValue(allItems[i].ToString()); rw.CreateCell(1).SetCellValue(DeptLeaderIdArr[i].ToString()); } MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { Encoding eGB3212 = Encoding.GetEncoding("GB2312"); fileName = eGB3212.GetString(eGB3212.GetBytes(fileName)); } else { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName)); HttpContext.Current.Response.Clear();
file.WriteTo(HttpContext.Current.Response.OutputStream); } catch (Exception ex) { throw new Exception("导出出错，请您联系系统管理员。" + ex.Message); } }
public static void CreateLevelAssistant(DataTable dt, ref ArrayList items,ArrayList DeptLeaderIdArr, DataRow parentRow, string curHeader, string text, string value, string parentid) { items.Add(curHeader + parentRow[text].ToString()); DeptLeaderIdArr.Add(parentRow["DeptLeaderId"].ToString()); DataRow[] rows = dt.Select(parentid + "='" + parentRow[value].ToString() + "'"); for (int i = 0; i < rows.Length - 1; i++) CreateLevelAssistant(dt, ref items,DeptLeaderIdArr, rows[i], curHeader.Replace("┣", "┃").Replace("┗", "┣") + "┣", text, value, parentid); if (rows.Length > 0) CreateLevelAssistant(dt, ref items,DeptLeaderIdArr, rows[rows.Length - 1], curHeader.Replace("┣", "┃").Replace("┗", "┣") + "┗", text, value, parentid); } }}
