using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
namespace CallCenter.Utility
{
public class NPOIHelper
{
private string _title;
private string _sheetName;
private string _filePath;
///
/// 导出到Excel
///
///
///
public bool ToExcel(DataTable table, string[] columns = null)
{
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workBook = new HSSFWorkbook();
if (string.IsNullOrWhiteSpace(this._sheetName))
{
this._sheetName = "sheet1";
}
ISheet sheet = workBook.CreateSheet(this._sheetName);
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(this._title);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
row.Height = 500;
ICellStyle cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表格列头
row = sheet.CreateRow(1);
if (columns == null)
{
for (int i = 0; i < table.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
}
else
{
for (int i = 0; i < columns.Length; i++)
{
row.CreateCell(i).SetCellValue(columns[i]);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
}
//处理数据内容
for (int i = 0; i < table.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250;
for (int j = 0; j < table.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
sheet.SetColumnWidth(j, 256 * 15);
}
}
//写入数据流
workBook.Write(fs);
fs.Flush();
fs.Close();
return true;
}
///
/// 导出到Excel
///
///
///
/// 空字符串或null的话默认sheet1
/// 自定义表格列头，默认null
///
public bool ToExcel(DataTable table, string title, string sheetName, string filePath, string[] columns = null)
{
this._title = title;
this._sheetName = sheetName;
this._filePath = filePath;
return ToExcel(table, columns);
}
///
/// 投诉产品日期分布表
///
///
///
public string SatisfiedToExcel(string Name, IOrderedEnumerable deptSatisfied, string[] cols = null)
{
try
{
//if (dt.Rows.Count > 0)
//{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (string dc in cols)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
string strsj = "";
foreach (var dr in deptSatisfied )
{
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i <5 ; i++)
{
switch (i )
{
case 0:
strsj = dr.deptname;
ICell cell = irow.CreateCell(i);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
break;
case 1:
strsj = dr.count.ToString ();
ICell cell1 = irow.CreateCell(i);
cell1.SetCellValue(strsj);
cell1.CellStyle = cellStyle;
break;
case 2:
strsj = dr.satisfiedcount.ToString();
ICell cell2 = irow.CreateCell(i);
cell2.SetCellValue(strsj);
cell2.CellStyle = cellStyle;
break;
case 3:
strsj = dr.notsatisfiedcount.ToString();
ICell cell3 = irow.CreateCell(i);
cell3.SetCellValue(strsj);
cell3.CellStyle = cellStyle;
break;
case 4:
strsj = dr.satisfiedrate;
ICell cell4 = irow.CreateCell(i);
cell4.SetCellValue(strsj);
cell4.CellStyle = cellStyle;
break;
}
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
//}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 弹出下载框导出excel
///
///
///
///
public string ExportToExcel(string Name, DataTable dt, string[] cols = null, int issort = 0)
{
try
{
//if (dt.Rows.Count > 0)
//{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(Name);
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
if (issort == 1)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue("序号");
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
if (cols == null || (cols != null && cols.Length == 0))
{
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
else
{
foreach (string dc in cols)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
if (issort == 1)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(iRowIndex + 1);
cell.CellStyle = cellStyle;
iCellIndex++;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
//}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 导入excel转换为datatable
///
///
///
///
public DataTable ExcelToTable(HttpPostedFile upfile, int headrow)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
Stream stream = upfile.InputStream;
string suffix = upfile.FileName.Substring(upfile.FileName.LastIndexOf(".") + 1).ToLower();
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(stream);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(stream);
}
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
//获取sheet的第一行
IRow headerRow = sheet.GetRow(headrow);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
//列名
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(headerRow.GetCell(i).ToString());
}
for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dr[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dr);
}
sheet = null;
workbook = null;
return dt;
}
///
/// 导入excel转换为datatable
///
///
///
///
public DataTable ExcelToTable(HttpPostedFileBase upfile, int headrow)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
Stream stream = upfile.InputStream;
string suffix = upfile.FileName.Substring(upfile.FileName.LastIndexOf(".") + 1).ToLower();
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(stream);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(stream);
}
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
//获取sheet的第一行
IRow headerRow = sheet.GetRow(headrow);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
//列名
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(headerRow.GetCell(i).ToString());
}
for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dr[j] = row.GetCell(j).ToString().Trim(' ').Trim('\t');
}
}
dt.Rows.Add(dr);
}
sheet = null;
workbook = null;
return dt;
}
private string GetCellValue(DataRow dr,int index,int i )
{
string str = "";
switch (i)
{
case 0:
str = index.ToString();
break;
case 1:
str = dr["deptname"].ToString();
break;
case 2:
str = dr["Undertakers"].ToString();
break;
case 3:
str = dr["Undertakersrate"].ToString();
break;
case 4:
str = dr["Undertakersscore"].ToString();
break;
case 5:
str = dr["overdue"].ToString();
break;
case 6:
str = dr["overduerate"].ToString();
break;
case 7:
str = dr["overduescore"].ToString();
break;
case 8:
str = dr["unsuccessful"].ToString();
break;
case 9:
str = dr["unsuccessfulrate"].ToString();
break;
case 10:
str = dr["unsuccessfulscore"].ToString();
break;
case 11:
str = dr["Chargeback"].ToString();
break;
case 12:
str = dr["Chargebackrate"].ToString();
break;
case 13:
str = dr["Chargebackscore"].ToString();
break;
case 14:
str = dr["Comment"].ToString();
break;
case 15:
str = dr["Oncedissatisfied"].ToString();
break;
case 16:
str = dr["dissatisfied"].ToString();
break;
case 17:
str = dr["satisfiedrate"].ToString();
break;
case 18:
str = dr["satisfiedscore"].ToString();
break;
case 19:
str = dr["total"].ToString();
break;
case 20:
str = index.ToString();
break;
}
return str;
}
public string ExcelMergerHotline(List mergerHotSources
)
{
return "";
}
///
/// 督办数据报表导出
///
///
///
public string DBExportToExcel(DataTable dt,string Name,string month,string starttime ,string endtime )
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(Name);
ICellStyle cellStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle cellStylebt = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt.SetFont(cellfontbt);
cellStylebt.VerticalAlignment = VerticalAlignment.Center;
cellStylebt.Alignment = HorizontalAlignment.Center;
IRow irow1 = sheet.CreateRow(0);
ICell cell1 = irow1.CreateCell(0);
cell1.SetCellValue("12345联动服务工作"+ month + "月份办理情况通报表");
cell1.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 19));
IRow irow2 = sheet.CreateRow(2);
ICell cell2 = irow2.CreateCell(0);
cell2.SetCellValue("统计周期："+ starttime+"至"+ endtime+" 统计时间："+DateTime .Now.ToString ("yyyy年MM月dd日"));
cell2.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 19));
IRow irow3 = sheet.CreateRow(3);
ICell cell3 = irow3.CreateCell(0);
cell3.SetCellValue("一、县（市、区）联动单位");
cell3.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 19));
string[] cols = {"序号","联动单位","承办件",
"承办率","得分(5分)","超期件","按时反馈率","得分(20分)",
"未果件","办结率","得分(10分)","退单件","有效回复率",
"得分(15分)","群众评议总数","一次不满意件","不满意件","满意率","得分(50分)","总分","排名"};
IRow irow4 = sheet.CreateRow(4);
int icolIndex = 0;
foreach (string dc in cols)
{
ICell cell = irow4.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = cellStylebt;
icolIndex++;
}
int iRowIndex = 5;
DataRow[] rows = dt.Select("category=1");
int index = 0;
foreach (DataRow dr in rows)
{
index++;
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex );
for (int i = 0; i < 21; i++)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(GetCellValue(dr, index,i ));
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
IRow irow5 = sheet.CreateRow(iRowIndex);
ICell cell5 = irow5.CreateCell(0);
cell5.SetCellValue("二、市直机关联动单位一组");
cell5.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
iRowIndex++;
IRow irow6 = sheet.CreateRow(iRowIndex);
icolIndex = 0;
foreach (string dc in cols)
{
ICell cell = irow6.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = cellStylebt;
icolIndex++;
}
iRowIndex++;
rows = dt.Select("category=2");
index = 0;
foreach (DataRow dr in rows)
{
index++;
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex);
for (int i = 0; i < 21; i++)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(GetCellValue(dr, index,i ));
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
IRow irow7 = sheet.CreateRow(iRowIndex);
ICell cell7 = irow7.CreateCell(0);
cell7.SetCellValue("三、市直机关联动单位二组");
cell7.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
iRowIndex++;
IRow irow8 = sheet.CreateRow(iRowIndex);
icolIndex = 0;
foreach (string dc in cols)
{
ICell cell = irow8.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = cellStylebt;
icolIndex++;
}
iRowIndex++;
rows = dt.Select("category=3");
index = 0;
foreach (DataRow dr in rows)
{
index++;
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex);
for (int i = 0; i < 21; i++)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(GetCellValue(dr, index,i ));
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
IRow irow9 = sheet.CreateRow(iRowIndex);
ICell cell9 = irow9.CreateCell(0);
cell9.SetCellValue("四、公益型企业联动单位");
cell9.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
iRowIndex++;
IRow irow10 = sheet.CreateRow(iRowIndex);
icolIndex = 0;
foreach (string dc in cols)
{
ICell cell = irow10.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = cellStylebt;
icolIndex++;
}
iRowIndex++;
rows = dt.Select("category=4");
index = 0;
foreach (DataRow dr in rows)
{
index++;
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex);
for (int i = 0; i < 21; i++)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(GetCellValue(dr, index,i ));
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < 20; i++)
{
sheet.AutoSizeColumn(i);
// sheet.SetColumnWidth(i, 20 * 256);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 督办数据报表导出
///
///
///
public string DBEJExportToExcel(DataTable dt, string Name, string month, string starttime, string endtime)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(Name);
ICellStyle cellStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle cellStylebt = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt.SetFont(cellfontbt);
cellStylebt.VerticalAlignment = VerticalAlignment.Center;
cellStylebt.Alignment = HorizontalAlignment.Center;
IRow irow1 = sheet.CreateRow(0);
ICell cell1 = irow1.CreateCell(0);
cell1.SetCellValue("12345联动服务工作" + month + "月份办理情况通报表");
cell1.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 19));
IRow irow2 = sheet.CreateRow(2);
ICell cell2 = irow2.CreateCell(0);
cell2.SetCellValue("统计周期：" + starttime + "至" + endtime + " 统计时间：" + DateTime.Now.ToString("yyyy年MM月dd日"));
cell2.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 19));
IRow irow3 = sheet.CreateRow(3);
ICell cell3 = irow3.CreateCell(0);
cell3.SetCellValue("一、县（市、区）联动单位");
cell3.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 19));
string[] cols = {"序号","联动单位","承办件",
"承办率","得分(5分)","超期件","按时反馈率","得分(20分)",
"未果件","办结率","得分(10分)","退单件","有效回复率",
"得分(15分)","群众评议总数","一次不满意件","不满意件","满意率","得分(50分)","总分","排名"};
IRow irow4 = sheet.CreateRow(4);
int icolIndex = 0;
foreach (string dc in cols)
{
ICell cell = irow4.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = cellStylebt;
icolIndex++;
}
int iRowIndex = 5;
// DataRow[] rows = dt.Select("category=1");
int index = 0;
foreach (DataRow dr in dt.Rows )
{
index++;
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex);
for (int i = 0; i < 21; i++)
{
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(GetCellValue(dr, index, i));
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < 20; i++)
{
sheet.AutoSizeColumn(i);
// sheet.SetColumnWidth(i, 20 * 256);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
return "";
}
catch
{
return "导出失败！";
}
}
//
///
/// 督办数据报表导出
///
///
///
public string ExportMergerToExcel(List MergerHotSource)
{
try
{
int lenth = 0;
if (MergerHotSource!=null&& MergerHotSource.Count>0)
{
if (MergerHotSource[0]!=null && MergerHotSource[0].MergerHot !=null
&& MergerHotSource[0].MergerHot.Count >0)
{
lenth = MergerHotSource[0].MergerHot.Count * 5+4;
}
else
{
return "";
}
}
else
{
return "";
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("归并热线每日数据统计");
ICellStyle cellStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle cellStylebt = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt.SetFont(cellfontbt);
cellStylebt.VerticalAlignment = VerticalAlignment.Center;
cellStylebt.Alignment = HorizontalAlignment.Center;
cellStylebt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IRow irow1 = sheet.CreateRow(0);
ICell cell1 = irow1.CreateCell(0);
cell1.SetCellValue("归并热线每日数据统计");
cell1.CellStyle=cellStylebt;
ICellStyle cellStylebt1 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt1.FontHeightInPoints = 22;
cellStylebt1.SetFont(cellfontbt1);
cellStylebt1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
cellStylebt1.Alignment = HorizontalAlignment.Center;
cell1.CellStyle = cellStylebt1;
sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, lenth));
IRow irow2 = sheet.CreateRow(3);
int icolIndex = 0;
ICellStyle cellStylebt2 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt2 = workbook.CreateFont();
cellfontbt2.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt2.WrapText = true;//设置换行这个要先设置
cellfontbt2.FontHeightInPoints = 14;
cellStylebt2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt2.SetFont(cellfontbt2);
cellStylebt2.VerticalAlignment = VerticalAlignment.Center;
cellStylebt2.Alignment = HorizontalAlignment.Center;
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex(8, 237,237, 237);
NPOI.HSSF.Util.HSSFColor hssFColor = palette.FindColor(237, 237, 237);
cellStylebt2.FillForegroundColor = hssFColor.Indexed ;
cellStylebt2.FillPattern = FillPattern.SolidForeground;
cellStylebt2.FillBackgroundColor = hssFColor.Indexed; ;
ICellStyle cellStylebt3 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
cellfontbt3.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt3.WrapText = true;//设置换行这个要先设置
cellfontbt3.FontHeightInPoints = 14;
cellStylebt3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt3.SetFont(cellfontbt3);
cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
cellStylebt3.Alignment = HorizontalAlignment.Center;
HSSFPalette palette1 = workbook.GetCustomPalette(); //调色板实例
palette1.SetColorAtIndex(9, 221,235,247);
NPOI.HSSF.Util.HSSFColor hssFColor1 = palette1.FindColor(221, 235, 247);
cellStylebt3.FillForegroundColor = hssFColor1.Indexed ;
cellStylebt3.FillPattern = FillPattern.SolidForeground;
cellStylebt3.FillBackgroundColor = hssFColor1.Indexed;
ICellStyle cellStylebt4 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt4 = workbook.CreateFont();
cellStylebt4.WrapText = true;//设置换行这个要先设置
cellfontbt4.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt4.FontHeightInPoints = 11;
cellStylebt4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt4.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt4.SetFont(cellfontbt4);
cellStylebt4.VerticalAlignment = VerticalAlignment.Center;
cellStylebt4.Alignment = HorizontalAlignment.Center;
ICellStyle cellStylebt5 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt5 = workbook.CreateFont();
cellfontbt5.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt5.FontHeightInPoints = 11;
cellStylebt5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt5.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt5.WrapText = true;//设置换行这个要先设置
cellStylebt5.SetFont(cellfontbt5);
cellStylebt5.VerticalAlignment = VerticalAlignment.Center;
cellStylebt5.Alignment = HorizontalAlignment.Center;
HSSFPalette palette2 = workbook.GetCustomPalette(); //调色板实例
palette2.SetColorAtIndex(10, 226, 239, 218);
NPOI.HSSF.Util.HSSFColor hssFColor2 = palette2.FindColor(226, 239, 218);
cellStylebt5.FillForegroundColor = hssFColor2.Indexed ;
cellStylebt5.FillPattern = FillPattern.SolidForeground;
cellStylebt5.FillBackgroundColor = hssFColor2.Indexed;
ICellStyle cellStylebt8 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt8 = workbook.CreateFont();
cellfontbt8.FontHeightInPoints = 10;
cellStylebt8.SetFont(cellfontbt8);
cellStylebt8.WrapText = true;//设置换行这个要先设置
cellStylebt8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt8.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt8.VerticalAlignment = VerticalAlignment.Center;
cellStylebt8.Alignment = HorizontalAlignment.Center;
HSSFPalette palette3 = workbook.GetCustomPalette(); //调色板实例
palette3.SetColorAtIndex(11, 198, 224, 180);
NPOI.HSSF.Util.HSSFColor hssFColor3 = palette3.FindColor(198, 224, 180);
cellStylebt8.FillForegroundColor = hssFColor3.Indexed ;
cellStylebt8.FillPattern = FillPattern.SolidForeground;
cellStylebt8.FillBackgroundColor = hssFColor3.Indexed;
ICellStyle cellStylebt9 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt9 = workbook.CreateFont();
cellfontbt9.FontHeightInPoints = 11;
cellfontbt9.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt9.SetFont(cellfontbt9);
cellStylebt9.WrapText = true;//设置换行这个要先设置
cellStylebt9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt9.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt9.VerticalAlignment = VerticalAlignment.Center;
cellStylebt9.Alignment = HorizontalAlignment.Center;
HSSFPalette palette4 = workbook.GetCustomPalette(); //调色板实例
palette4.SetColorAtIndex(12, 255, 242, 204);
NPOI.HSSF.Util.HSSFColor hssFColor4 = palette4.FindColor(255, 242, 204);
cellStylebt9.FillForegroundColor = hssFColor4.Indexed ; ;
cellStylebt9.FillPattern = FillPattern.SolidForeground;
cellStylebt9.FillBackgroundColor = hssFColor4.Indexed; ; ;
ICellStyle cellStylebt10 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt10 = workbook.CreateFont();
cellfontbt10.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt10.FontHeightInPoints = 11;
cellStylebt10.WrapText = true;//设置换行这个要先设置
cellStylebt10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt10.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt10.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt10.SetFont(cellfontbt10);
cellStylebt10.VerticalAlignment = VerticalAlignment.Center;
cellStylebt10.Alignment = HorizontalAlignment.Center;
HSSFPalette palette5 = workbook.GetCustomPalette(); //调色板实例
palette5.SetColorAtIndex(13, 217, 225, 242);
NPOI.HSSF.Util.HSSFColor hssFColor5 = palette5.FindColor(217, 225, 242);
cellStylebt10.FillForegroundColor = hssFColor5.Indexed ;
cellStylebt10.FillPattern = FillPattern.SolidForeground;
cellStylebt10.FillBackgroundColor = hssFColor5.Indexed; ;
ICellStyle cellStylebt6 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt6 = workbook.CreateFont();
cellfontbt6.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt6.WrapText = true;//设置换行这个要先设置
cellfontbt6.FontHeightInPoints = 10;
cellStylebt6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt6.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt6.SetFont(cellfontbt6);
cellStylebt6.VerticalAlignment = VerticalAlignment.Center;
cellStylebt6.Alignment = HorizontalAlignment.Center;
cellStylebt6.FillForegroundColor = hssFColor.Indexed ;
cellStylebt6.FillPattern = FillPattern.SolidForeground;
cellStylebt6.FillBackgroundColor = hssFColor.Indexed; ;
ICellStyle cellStylebt11 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt11 = workbook.CreateFont();
cellfontbt11.FontHeightInPoints = 10;
cellStylebt11.WrapText = true;//设置换行这个要先设置
cellStylebt11.SetFont(cellfontbt11);
cellStylebt11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt11.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt11.VerticalAlignment = VerticalAlignment.Center;
cellStylebt11.Alignment = HorizontalAlignment.Center;
ICellStyle cellStylebt12 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt12 = workbook.CreateFont();
cellfontbt12.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt12.FontHeightInPoints = 10;
cellStylebt12.WrapText = true;//设置换行这个要先设置
cellStylebt12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt12.SetFont(cellfontbt12);
cellStylebt12.VerticalAlignment = VerticalAlignment.Center;
cellStylebt12.Alignment = HorizontalAlignment.Center;
cellStylebt12.FillForegroundColor = hssFColor5.Indexed;
cellStylebt12.FillPattern = FillPattern.SolidForeground;
cellStylebt12.FillBackgroundColor = hssFColor5.Indexed; ;
ICellStyle cellStylebt13 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt13 = workbook.CreateFont();
cellfontbt13.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt13.WrapText = true;//设置换行这个要先设置
cellfontbt13.FontHeightInPoints = 11;
cellStylebt13.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt13.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt13.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt13.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt13.SetFont(cellfontbt13);
cellStylebt13.VerticalAlignment = VerticalAlignment.Center;
cellStylebt13.Alignment = HorizontalAlignment.Center;
cellStylebt13.FillForegroundColor = hssFColor.Indexed;
cellStylebt13.FillPattern = FillPattern.SolidForeground;
cellStylebt13.FillBackgroundColor = hssFColor.Indexed; ;
ICellStyle cellStylebt14 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt14 = workbook.CreateFont();
cellfontbt14.FontHeightInPoints = 10;
cellStylebt14.SetFont(cellfontbt14);
cellStylebt14.WrapText = true;//设置换行这个要先设置
cellStylebt14.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt14.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt14.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt14.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt14.VerticalAlignment = VerticalAlignment.Center;
cellStylebt14.Alignment = HorizontalAlignment.Center;
cellStylebt14.FillForegroundColor = hssFColor4.Indexed; ;
cellStylebt14.FillPattern = FillPattern.SolidForeground;
cellStylebt14.FillBackgroundColor = hssFColor4.Indexed; ; ;
ICellStyle cellStylebt15 = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt15 = workbook.CreateFont();
cellfontbt15.Boldweight = (short)FontBoldWeight.Bold;
cellfontbt15.FontHeightInPoints = 10;
cellStylebt15.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt15.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt15.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt15.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylebt15.WrapText = true;//设置换行这个要先设置
cellStylebt15.SetFont(cellfontbt15);
cellStylebt15.VerticalAlignment = VerticalAlignment.Center;
cellStylebt15.Alignment = HorizontalAlignment.Center;
cellStylebt15.FillForegroundColor = hssFColor2.Indexed;
cellStylebt15.FillPattern = FillPattern.SolidForeground;
cellStylebt15.FillBackgroundColor = hssFColor2.Indexed;
int indexsource = -1;
for (int i = 0; i <= MergerHotSource[0].MergerHot.Count
;i++)
{
if (i == 0)
{
ICell cell = irow2.CreateCell(0);
cell.SetCellValue("来源");
cell.CellStyle = cellStylebt2;
indexsource = 0;
sheet.SetColumnWidth(0, 12 * 300);
}
else if (i >0&&i <= MergerHotSource[0].MergerHot.Count-2)
{
ICell cell = irow2.CreateCell(indexsource+1);
cell.SetCellValue(MergerHotSource[0].MergerHot[i - 1].Source);
cell.CellStyle = cellStylebt2;
if (i ==1 )
{
if (MergerHotSource[0].MergerHot[i - 1].Source=="12345")
{
indexsource = 6;
for (int z=2;z <= indexsource - 1;z++)
{
ICell cell4 = irow2.CreateCell(z);
cell4.CellStyle = cellStylebt2;
}
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, indexsource-1));
}
else
{
indexsource = 5;
for (int z = 2; z <= indexsource - 1; z++)
{
ICell cell4 = irow2.CreateCell(z);
cell4.CellStyle = cellStylebt2;
}
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, indexsource-1));
}
}
else
{
if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
{
for (int z = indexsource + 2; z <= indexsource + 5 ; z++)
{
ICell cell4 = irow2.CreateCell(z);
cell4.CellStyle = cellStylebt2;
}
sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 5));
indexsource = indexsource + 6;
}
else
{
for (int z = indexsource + 2; z <= indexsource + 4; z++)
{
ICell cell4 = irow2.CreateCell(z);
cell4.CellStyle = cellStylebt2;
}
sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 4));
indexsource += 5;
}
}
}
else
{
ICell cell = irow2.CreateCell(indexsource + 1);
if (i == MergerHotSource[0].MergerHot.Count - 1)
{
cell.SetCellValue("当日小计");
}
else if (i == MergerHotSource[0].MergerHot.Count )
{
cell.SetCellValue("累计");
}
for (int z = indexsource + 2; z <= indexsource + 5; z++)
{
ICell cell4 = irow2.CreateCell(z);
cell4.CellStyle = cellStylebt2;
}
sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 5));
cell.CellStyle = cellStylebt3;
indexsource += 6;
}
}
IRow irow3 = sheet.CreateRow(4);
indexsource = 0;
sheet.CreateRow(4).Height = 200 * 8;
for (int i = 0; i <= MergerHotSource[0].MergerHot.Count; i++)
{
if (i == 0)
{
ICell cell = irow3.CreateCell(0);
cell.SetCellValue("日期");
cell.CellStyle = cellStylebt4;
}
else if (i > 0 && i <= MergerHotSource[0].MergerHot.Count - 2)
{
if (i == 1)
{
indexsource = 1;
if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
{
for (int j = 0; j < 5; j++)
{
ICell cell = irow3.CreateCell(indexsource);
switch (j)
{
case 0:
cell.SetCellValue("来\n电\n量\n");
cell.CellStyle = cellStylebt5;
sheet.SetColumnWidth(1, 12 * 100);
break;
case 1:
cell.SetCellValue("接\n通\n量\n");
cell.CellStyle = cellStylebt9;
sheet.SetColumnWidth(2, 12 * 100);
break;
case 2:
cell.SetCellValue("接\n通\n率\n");
cell.CellStyle = cellStylebt13;
sheet.SetColumnWidth(3, 12 * 100);
break;
case 3:
cell.SetCellValue("未\n接\n通\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(4, 12 * 100);
break;
case 4:
cell.SetCellValue("主\n动\n放\n弃\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(5, 12 * 100);
break;
}
indexsource += 1;
}
sheet.SetColumnWidth(6, 12 * 35);
indexsource = 6;
}
else
{
indexsource = 1;
for (int j = 0; j < 4; j++)
{
ICell cell = irow3.CreateCell(indexsource);
switch (j)
{
case 0:
cell.SetCellValue("来\n电\n量\n");
cell.CellStyle = cellStylebt5;
sheet.SetColumnWidth(1, 12 * 100);
break;
case 1:
cell.SetCellValue("接\n通\n量\n");
cell.CellStyle = cellStylebt9;
sheet.SetColumnWidth(2, 12 * 100);
break;
case 2:
cell.SetCellValue("未\n接\n通\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(3, 12 * 100);
break;
case 3:
cell.SetCellValue("主\n动\n放\n弃\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(4, 12 * 100);
break;
}
indexsource += 1;
}
sheet.SetColumnWidth(5, 12 * 35);
indexsource = 5;
}
}
else
{
if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
{
for (int j = 0; j < 5; j++)
{
indexsource += 1;
ICell cell = irow3.CreateCell(indexsource);
switch (j)
{
case 0:
cell.SetCellValue("来\n电\n量\n");
cell.CellStyle = cellStylebt5;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 1:
cell.SetCellValue("接\n通\n量\n");
cell.CellStyle = cellStylebt9;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 2:
cell.SetCellValue("接\n通\n率\n");
cell.CellStyle = cellStylebt2;
sheet.SetColumnWidth(7, 12 * 100);
break;
case 3:
cell.SetCellValue("未\n接\n通\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 4:
cell.SetCellValue("主\n动\n放\n弃\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
}
}
indexsource += 1;
sheet.SetColumnWidth(indexsource, 12 * 35);
}
else
{
for (int j = 0; j < 4; j++)
{
indexsource += 1;
ICell cell = irow3.CreateCell(indexsource);
switch (j)
{
case 0:
cell.SetCellValue("来\n电\n量\n");
cell.CellStyle = cellStylebt5;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 1:
cell.SetCellValue("接\n通\n量\n");
cell.CellStyle = cellStylebt9;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 2:
cell.SetCellValue("未\n接\n通\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 3:
cell.SetCellValue("主\n动\n放\n弃\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
}
}
indexsource += 1;
sheet.SetColumnWidth(indexsource, 12 * 35);
}
}
}
else
{
for (int j = 0; j < 5; j++)
{
indexsource += 1;
ICell cell = irow3.CreateCell(indexsource);
switch (j)
{
case 0:
cell.SetCellValue("来\n电\n量\n");
cell.CellStyle = cellStylebt5;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 1:
cell.SetCellValue("接\n通\n量\n");
cell.CellStyle = cellStylebt9;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 2:
cell.SetCellValue("未\n接\n通\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 3:
cell.SetCellValue("主\n动\n放\n弃\n");
cell.CellStyle = cellStylebt4;
sheet.SetColumnWidth(indexsource, 12 * 100);
break;
case 4:
cell.SetCellValue("接\n通\n率\n");
cell.CellStyle = cellStylebt10;
sheet.SetColumnWidth(indexsource, 12 * 200);
break;
}
}
indexsource += 1;
sheet.SetColumnWidth(indexsource, 12 * 35);
}
}
for (int i=0;i< MergerHotSource.Count; i++)
{
IRow irow4 = sheet.CreateRow(5+i );
for (int j = 0; j <= MergerHotSource[i].MergerHot.Count; j++)
{
if (j == 0)
{
ICell cell = irow4.CreateCell(0);
cell.SetCellValue(MergerHotSource[i].Data);
cell.CellStyle = cellStylebt11;
indexsource = 0;
}
else
{
if (i == MergerHotSource.Count-1)
{
if (j == 1)
{
if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt8;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt8;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt8;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt8;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt8;
break;
}
}
indexsource += 1;
}
else
{
for (int z = 0; z < 4; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt8;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt8;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt8;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt8;
break;
}
}
indexsource += 1;
}
}
else
{
if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt8;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt8;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt8;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt8;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt8;
break;
}
}
indexsource += 1;
}
else if (MergerHotSource[i].MergerHot[j - 1].Source == "当日小计"
|| MergerHotSource[i].MergerHot[j - 1].Source == "累计")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt8;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt8;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt8;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt8;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt8;
break;
}
}
indexsource += 1;
}
else
{
for (int z = 0; z < 4; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt8;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt8;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt8;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt8;
break;
}
}
indexsource += 1;
}
}
}
else
{
if (j == 1)
{
if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt15;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt14;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt6;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt11;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt11;
break;
}
}
indexsource += 1;
}
else
{
for (int z = 0; z < 4; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt15;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt14;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt11;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt11;
break;
}
}
indexsource += 1;
}
}
else
{
if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt15;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt14;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt6;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt11;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt11;
break;
}
}
indexsource += 1;
}
else if (MergerHotSource[i].MergerHot[j - 1].Source == "当日小计"
|| MergerHotSource[i].MergerHot[j - 1].Source == "累计")
{
for (int z = 0; z < 5; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt15;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt14;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt11;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt11;
break;
case 4:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].rate);
cell.CellStyle = cellStylebt12;
break;
}
}
indexsource += 1;
}
else
{
for (int z = 0; z < 4; z++)
{
indexsource += 1;
ICell cell = irow4.CreateCell(indexsource);
switch (z)
{
case 0:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Call);
cell.CellStyle = cellStylebt15;
break;
case 1:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Connect);
cell.CellStyle = cellStylebt14;
break;
case 2:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].Notconnected);
cell.CellStyle = cellStylebt11;
break;
case 3:
cell.SetCellValue(MergerHotSource[i]
.MergerHot[j - 1].voluntarily);
cell.CellStyle = cellStylebt11;
break;
}
}
indexsource += 1;
}
}
}
}
}
}
//自适应列宽度
//for (int i = 0; i < 20; i++)
//{
// sheet.AutoSizeColumn(i);
// // sheet.SetColumnWidth(i, 20 * 256);
//}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("归并数据统计" + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 弹出下载框导出excel
///
///
///
///
public string TSExportToExcel(DataTable dt, int tscount)
{
try
{
//if (dt.Rows.Count > 0)
//{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(1, tscount, 0, 0));
for (int i = iRowIndex; i > tscount; i--)
{
sheet.AddMergedRegion(new CellRangeAddress(i, i, 0, 1));
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("投诉统计_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
//}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 生成excel到路径
///
///
///
///
public string CreateExcelFile(string Name, DataTable dt, string Path,string[] cols = null)
{
try
{
if (dt.Rows.Count > 0)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(Name);
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
if (cols == null || (cols != null && cols.Length == 0))
{
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
else
{
foreach (string dc in cols)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
Name = HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls");
Path = HttpContext.Current.Server.MapPath("..") + Path;
if (!Directory.Exists(Path)) Directory.CreateDirectory(Path);
FileStream fileHSSF = new FileStream(Path+ Name, FileMode.Create);
workbook.Write(fileHSSF);
fileHSSF.Close();
fileHSSF.Dispose();
workbook = null;
}
return "";
}
catch
{
return "生成失败！";
}
}
///
/// 弹出下载框导出excel
///
///
///
///
public string ExportToExcel64(string name, string base64url)
{
try
{
int delLength = base64url.IndexOf(',') + 1;
string str = base64url.Substring(delLength, base64url.Length - delLength);
byte[] bData = Convert.FromBase64String(str);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(bData);
curContext.Response.End();
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 简报导出
///
///
///
public string SimpleExportToExcel(DataSet ds)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
//cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle cellStylebt = workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
cellStylebt.SetFont(cellfontbt);
cellStylebt.VerticalAlignment = VerticalAlignment.Center;
cellStylebt.Alignment = HorizontalAlignment.Center;
IRow irow1 = sheet.CreateRow(1);
ICell cell1 = irow1.CreateCell(0);
cell1.SetCellValue("时间："+ ds.Tables[5].Rows[0]["sdate"].ToString()+ " 至 "+ ds.Tables[5].Rows[0]["edate"].ToString());
cell1.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new CellRangeAddress(1, 1, 0, 7), BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
#region 话务受理情况
DataTable dt1 = ds.Tables[0];
IRow irow2 = sheet.CreateRow(2);
ICell cell2 = irow2.CreateCell(0);
cell2.SetCellValue("话务受理情况");
cell2.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 7));
IRow irow3 = sheet.CreateRow(3);
ICell cell31 = irow3.CreateCell(0);
cell31.SetCellValue("来电");
cell31.CellStyle = cellStyle;
ICell cell32 = irow3.CreateCell(1);
cell32.SetCellValue(Int32.Parse(dt1.Rows[0]["ldcount"].ToString()));
cell32.CellStyle = cellStyle;
ICell cell33 = irow3.CreateCell(2);
cell33.SetCellValue("接听");
cell33.CellStyle = cellStyle;
ICell cell34 = irow3.CreateCell(3);
cell34.SetCellValue(Int32.Parse(dt1.Rows[0]["jtcount"].ToString()));
cell34.CellStyle = cellStyle;
ICell cell35 = irow3.CreateCell(4);
cell35.SetCellValue("有效接听");
cell35.CellStyle = cellStyle;
ICell cell36 = irow3.CreateCell(5);
cell36.SetCellValue(Int32.Parse(dt1.Rows[0]["yxcount"].ToString()));
cell36.CellStyle = cellStyle;
#endregion
#region 工单受理情况
DataTable dt2 = ds.Tables[1];
IRow irow4 = sheet.CreateRow(4);
ICell cell4 = irow4.CreateCell(0);
cell4.SetCellValue("工单受理情况");
cell4.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 7));
IRow irow5 = sheet.CreateRow(5);
ICell cell51 = irow5.CreateCell(0);
cell51.SetCellValue("受理");
cell51.CellStyle = cellStyle;
ICell cell52 = irow5.CreateCell(1);
cell52.SetCellValue(Int32.Parse(dt2.Rows[0]["slcount"].ToString()));
cell52.CellStyle = cellStyle;
ICell cell53 = irow5.CreateCell(2);
cell53.SetCellValue("待提交");
cell53.CellStyle = cellStyle;
ICell cell54 = irow5.CreateCell(3);
cell54.SetCellValue(Int32.Parse(dt2.Rows[0]["dtjcount"].ToString()));
cell54.CellStyle = cellStyle;
ICell cell55 = irow5.CreateCell(4);
cell55.SetCellValue("待交办");
cell55.CellStyle = cellStyle;
ICell cell56 = irow5.CreateCell(5);
cell56.SetCellValue(Int32.Parse(dt2.Rows[0]["djbcount"].ToString()));
cell56.CellStyle = cellStyle;
ICell cell57 = irow5.CreateCell(6);
cell57.SetCellValue("待查收");
cell57.CellStyle = cellStyle;
ICell cell58 = irow5.CreateCell(7);
cell58.SetCellValue(Int32.Parse(dt2.Rows[0]["dcscount"].ToString()));
cell58.CellStyle = cellStyle;
IRow irow6 = sheet.CreateRow(6);
ICell cell61 = irow6.CreateCell(0);
cell61.SetCellValue("待审核退回");
cell61.CellStyle = cellStyle;
ICell cell62 = irow6.CreateCell(1);
cell62.SetCellValue(Int32.Parse(dt2.Rows[0]["dshthcount"].ToString()));
cell62.CellStyle = cellStyle;
ICell cell63 = irow6.CreateCell(2);
cell63.SetCellValue("待办理");
cell63.CellStyle = cellStyle;
ICell cell64 = irow6.CreateCell(3);
cell64.SetCellValue(Int32.Parse(dt2.Rows[0]["dblcount"].ToString()));
cell64.CellStyle = cellStyle;
ICell cell65 = irow6.CreateCell(4);
cell65.SetCellValue("待延时审核");
cell65.CellStyle = cellStyle;
ICell cell66 = irow6.CreateCell(5);
cell66.SetCellValue(Int32.Parse(dt2.Rows[0]["dshyscount"].ToString()));
cell66.CellStyle = cellStyle;
ICell cell67 = irow6.CreateCell(6);
cell67.SetCellValue("待回访");
cell67.CellStyle = cellStyle;
ICell cell68 = irow6.CreateCell(7);
cell68.SetCellValue(Int32.Parse(dt2.Rows[0]["dhfcount"].ToString()));
cell68.CellStyle = cellStyle;
IRow irow7 = sheet.CreateRow(7);
ICell cell71 = irow7.CreateCell(0);
cell71.SetCellValue("待结案");
cell71.CellStyle = cellStyle;
ICell cell72 = irow7.CreateCell(1);
cell72.SetCellValue(Int32.Parse(dt2.Rows[0]["dwjcount"].ToString()));
cell72.CellStyle = cellStyle;
ICell cell73 = irow7.CreateCell(2);
cell73.SetCellValue("待重办");
cell73.CellStyle = cellStyle;
ICell cell74 = irow7.CreateCell(3);
cell74.SetCellValue(Int32.Parse(dt2.Rows[0]["dcbcount"].ToString()));
cell74.CellStyle = cellStyle;
ICell cell75 = irow7.CreateCell(4);
cell75.SetCellValue("已结案");
cell75.CellStyle = cellStyle;
ICell cell76 = irow7.CreateCell(5);
cell76.SetCellValue(Int32.Parse(dt2.Rows[0]["ywjcount"].ToString()));
cell76.CellStyle = cellStyle;
ICell cell77 = irow7.CreateCell(6);
cell77.SetCellValue("在线办理");
cell77.CellStyle = cellStyle;
ICell cell78 = irow7.CreateCell(7);
cell78.SetCellValue(Int32.Parse(dt2.Rows[0]["zxbjcount"].ToString()));
cell78.CellStyle = cellStyle;
IRow irow8 = sheet.CreateRow(8);
ICell cell81 = irow8.CreateCell(0);
cell81.SetCellValue("中心转派");
cell81.CellStyle = cellStyle;
ICell cell82 = irow8.CreateCell(1);
cell82.SetCellValue(Int32.Parse(dt2.Rows[0]["zxzpcount"].ToString()));
cell82.CellStyle = cellStyle;
ICell cell83 = irow8.CreateCell(2);
cell83.SetCellValue("在线办理率");
cell83.CellStyle = cellStyle;
ICell cell84 = irow8.CreateCell(3);
cell84.SetCellValue(dt2.Rows[0]["zxbjrate"].ToString());
cell84.CellStyle = cellStyle;
#endregion
#region 政府热线受理情况
DataTable dt3 = ds.Tables[2];
IRow irow9 = sheet.CreateRow(9);
ICell cell9 = irow9.CreateCell(0);
cell9.SetCellValue("政府热线受理情况");
cell9.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(9, 9, 0, 7));
IRow irow10 = sheet.CreateRow(10);
IRow irow11 = sheet.CreateRow(11);
int n = 0;
foreach (DataRow dr3 in dt3.Rows)
{
if (n < 4)
{
ICell cell101 = irow10.CreateCell(2 * (n + 1) - 2);
cell101.SetCellValue(dr3["source"].ToString());
cell101.CellStyle = cellStyle;
ICell cell102 = irow10.CreateCell(2 * (n + 1) - 1);
cell102.SetCellValue(Int32.Parse(dr3["count"].ToString()));
cell102.CellStyle = cellStyle;
}
else
{
ICell cell111 = irow11.CreateCell(2 * (n -3) - 2);
cell111.SetCellValue(dr3["source"].ToString());
cell111.CellStyle = cellStyle;
ICell cell112 = irow11.CreateCell(2 * (n - 3) - 1);
cell112.SetCellValue(Int32.Parse(dr3["count"].ToString()));
cell112.CellStyle = cellStyle;
}
n = n + 1;
}
#endregion
#region 工单受理类型情况
DataTable dt4 = ds.Tables[3];
IRow irow12 = sheet.CreateRow(12);
ICell cell12 = irow12.CreateCell(0);
cell12.SetCellValue("工单受理类型情况");
cell12.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(12, 12, 0, 7));
IRow irow13 = sheet.CreateRow(13);
IRow irow14 = sheet.CreateRow(14);
int m = 0;
foreach (DataRow dr4 in dt4.Rows)
{
if (m < 4)
{
ICell cell131 = irow13.CreateCell(2 * (m + 1) - 2);
cell131.SetCellValue(dr4["type"].ToString());
cell131.CellStyle = cellStyle;
ICell cell132 = irow13.CreateCell(2 * (m + 1) - 1);
cell132.SetCellValue(Int32.Parse(dr4["count"].ToString()));
cell132.CellStyle = cellStyle;
}
else
{
ICell cell141 = irow14.CreateCell(2 * (m - 3) - 2);
cell141.SetCellValue(dr4["type"].ToString());
cell141.CellStyle = cellStyle;
ICell cell152 = irow14.CreateCell(2 * (m - 3) - 1);
cell152.SetCellValue(Int32.Parse(dr4["count"].ToString()));
cell152.CellStyle = cellStyle;
}
m = m + 1;
}
#endregion
#region 工单事发区域情况
DataTable dt5 = ds.Tables[4];
IRow irow15 = sheet.CreateRow(15);
ICell cell15 = irow15.CreateCell(0);
cell15.SetCellValue("工单事发区域情况");
cell15.CellStyle = cellStylebt;
sheet.AddMergedRegion(new CellRangeAddress(15, 15, 0, 7));
int rowcount = (dt5.Rows.Count / 4) + 1;
for (int i = 0; i < rowcount; i++)
{
IRow irow = sheet.CreateRow(16 + i);
for (int j = 0; j < 4; j++)
{
int num = i * 4 + j;
if (num != dt5.Rows.Count)
{
var dr = dt5.Rows[num];
ICell cellname = irow.CreateCell(2 * (j + 1) - 2);
cellname.SetCellValue(dr["areaname"].ToString());
cellname.CellStyle = cellStyle;
ICell cellcount = irow.CreateCell(2 * (j + 1) - 1);
cellcount.SetCellValue(Int32.Parse(dr["count"].ToString()));
cellcount.CellStyle = cellStyle;
}
else
{
break;
}
}
}
#endregion
//自适应列宽度
for (int i = 0; i < 8; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("业务简报_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 弹出下载框导出excel（数据第一行为标题）
///
///
///
///
public string ExportToExcel2(string Name, DataTable dt, string[] cols = null)
{
try
{
//if (dt.Rows.Count > 0)
//{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
if (cols == null || (cols != null && cols.Length == 0))
{
//foreach (DataColumn dc in dt.Columns)
//{
// ICell cell = headerRow.CreateCell(icolIndex);
// cell.SetCellValue(dc.ColumnName);
// cell.CellStyle = HeadercellStyle;
// icolIndex++;
//}
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dt.Rows[0][i].ToString());
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
else
{
foreach (string dc in cols)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
if (iRowIndex > 0)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Access-Control-Expose-Headers", "Content-Disposition");
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
//}
return "";
}
catch
{
return "导出失败！";
}
}
///
/// 导出word
///
///
public byte[] ExportToWordDemo()
{
try
{
//创建document文档对象对象实例
NPOI.XWPF.UserModel.XWPFDocument document = new NPOI.XWPF.UserModel.XWPFDocument();
document.CreateParagraph();
NPOI.XWPF.UserModel.XWPFParagraph paragraph = document.CreateParagraph();//创建段落对象
paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;//文字显示位置,段落排列（左对齐，居中，右对齐）
NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
xwpfRun.IsBold = true;//文字加粗
xwpfRun.SetText("安阳市12345政务服务热线交办单");//填充内容
xwpfRun.FontSize = 18;//设置文字大小
xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定
document.CreateParagraph();
//创建文档中的表格对象实例
NPOI.XWPF.UserModel.XWPFTable table = document.CreateTable(9, 8);//显示的行列数rows:3行,cols:4列
table.Width = 5200;//总宽度
table.SetColumnWidth(0, 650); /* 设置列宽 */
table.SetColumnWidth(1, 650);
table.SetColumnWidth(2, 650);
table.SetColumnWidth(3, 650);
table.SetColumnWidth(4, 650);
table.SetColumnWidth(5, 650);
table.SetColumnWidth(6, 650);
table.SetColumnWidth(7, 650);
for (int i = 0; i < 9; i++)
{
for (int j = 0; j < 8; j++)
{
var cp = table.GetRow(i).GetCell(j).GetCTTc().AddNewTcPr();
cp.AddNewVAlign().val = ST_VerticalJc.center;
}
}
//第一行
table.GetRow(0).GetCTRow().AddNewTrPr().AddNewTrHeight().val= (ulong)300;
var cp0 = table.GetRow(0).GetCell(0).GetCTTc().AddNewTcPr();
cp0.AddNewVMerge().val = ST_Merge.restart;
cp0.AddNewVAlign().val = ST_VerticalJc.center;
cp0.tcW = new CT_TblWidth();cp0.tcW.w = "650";cp0.tcW.type = ST_TblWidth.dxa;
table.GetRow(0).GetCell(0).SetParagraph(GetParagraph(table, true, "事项编号"));
var cp1 = table.GetRow(0).GetCell(1).GetCTTc().AddNewTcPr();
cp1.AddNewVMerge().val = ST_Merge.restart;
cp1.AddNewVAlign().val = ST_VerticalJc.center;
table.GetRow(0).GetCell(1).SetParagraph(GetParagraph(table, false, "DH9941052721041200351"));
table.GetRow(0).GetCell(2).SetParagraph(GetParagraph(table, true, "工单来源"));
table.GetRow(0).GetCell(3).SetParagraph(GetParagraph(table, false, "市长电话"));
table.GetRow(0).GetCell(4).SetParagraph(GetParagraph(table, true, "办理时限"));
table.GetRow(0).GetCell(5).SetParagraph(GetParagraph(table, false, "2021-04-21"));
table.GetRow(0).GetCell(6).SetParagraph(GetParagraph(table, true, "联系电话"));
table.GetRow(0).GetCell(7).SetParagraph(GetParagraph(table, false, "017185328292"));
//第二行
table.GetRow(1).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
var cp2 = table.GetRow(1).GetCell(0).GetCTTc().AddNewTcPr();
cp2.AddNewVMerge().val = ST_Merge.@continue;
var cp3 = table.GetRow(1).GetCell(1).GetCTTc().AddNewTcPr();
cp3.AddNewVMerge().val = ST_Merge.@continue;
table.GetRow(1).GetCell(2).SetParagraph(GetParagraph(table, true, "签收时间"));
table.GetRow(1).GetCell(3).SetParagraph(GetParagraph(table, false, "2021-04-12"));
table.GetRow(1).GetCell(4).SetParagraph(GetParagraph(table, true, "办理时间"));
table.GetRow(1).GetCell(5).SetParagraph(GetParagraph(table, false, "2021-04-12"));
table.GetRow(1).GetCell(6).SetParagraph(GetParagraph(table, true, "来电人"));
table.GetRow(1).GetCell(7).SetParagraph(GetParagraph(table, false, "徐青田"));
//第三行
table.GetRow(2).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
table.GetRow(2).MergeCells(1, 7);//合并列
table.GetRow(2).GetCell(0).SetParagraph(GetParagraph(table, true, "事项区域"));
table.GetRow(2).GetCell(1).SetParagraph(GetParagraph(table, false, "河南省郑州市高新技术开发区大学科技园东区", false));
//第四行
table.GetRow(3).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
table.GetRow(3).MergeCells(1, 7);//合并列
table.GetRow(3).GetCell(0).SetParagraph(GetParagraph(table, true, "内容摘要"));
table.GetRow(3).GetCell(1).SetParagraph(GetParagraph(table, false, "来电人反映：内黄县城关镇宛庄村第四生产队第二、三小组土地至今无法确权（遗留问题），村委会和镇政府承诺给解决，至今未解决，请政府责成相关部门调查处理。", false));
//第五行
table.GetRow(4).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
table.GetRow(4).MergeCells(1, 4);//合并列
table.GetRow(4).MergeCells(3, 4);//合并列
table.GetRow(4).GetCell(0).SetParagraph(GetParagraph(table, true, "承办单位"));
table.GetRow(4).GetCell(1).SetParagraph(GetParagraph(table, false, "内黄县政府"));
table.GetRow(4).GetCell(2).SetParagraph(GetParagraph(table, true, "协办单位"));
table.GetRow(4).GetCell(3).SetParagraph(GetParagraph(table, false, "内黄县政府"));
//第六行
table.GetRow(5).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
table.GetRow(5).MergeCells(1, 7);//合并列
table.GetRow(5).GetCell(0).SetParagraph(GetParagraph(table, true, "调度意见"));
table.GetRow(5).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
//第七行
table.GetRow(6).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)1500;
table.GetRow(6).MergeCells(1, 7);//合并列
table.GetRow(6).GetCell(0).SetParagraph(GetParagraph(table, true, "承办意见"));
table.GetRow(6).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
//第八行
table.GetRow(7).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)1500;
table.GetRow(7).MergeCells(1, 7);//合并列
table.GetRow(7).GetCell(0).SetParagraph(GetParagraph(table, true, "领导批示"));
table.GetRow(7).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
//第九行
table.GetRow(8).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
table.GetRow(8).MergeCells(1, 7);//合并列
table.GetRow(8).GetCell(0).SetParagraph(GetParagraph(table, true, "处理结果"));
table.GetRow(8).GetCell(1).SetParagraph(GetParagraph(table, false, "2021-04-12 10:19:14 内黄县政府部门接到市长电话来源工单（编号：DH9941052721041200351）反映土地确权问题，已联系郑州第三方中标土地确权公司负责人待工作人员到我县后，就可以确权 [办理结果：已办理，承办人：刘志强，职务：城关镇人大主席，联系电话：15824608111，刘志强向当事人进行了反馈，当事人对结果表示未评价。]", false));
document.CreateParagraph();
NPOI.XWPF.UserModel.XWPFParagraph paragraph1 = document.CreateParagraph();//创建段落对象
paragraph1.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;//文字显示位置,段落排列（左对齐，居中，右对齐）
NPOI.XWPF.UserModel.XWPFRun xwpfRun1 = paragraph1.CreateRun();//创建段落文本对象
xwpfRun1.IsBold = true;//文字加粗
xwpfRun1.SetText("联系电话：(0372) 12345审核人：8000 调度员：8065");//填充内容
xwpfRun1.FontSize = 9;//设置文字大小
xwpfRun1.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定
NPOI.XWPF.UserModel.XWPFParagraph paragraph2 = document.CreateParagraph();//创建段落对象
paragraph2.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT;//文字显示位置,段落排列（左对齐，居中，右对齐）
NPOI.XWPF.UserModel.XWPFRun xwpfRun2 = paragraph2.CreateRun();//创建段落文本对象
using (FileStream picData = new FileStream(HttpContext.Current.Server.MapPath("/Upload/Word/word.png"), FileMode.Open, FileAccess.Read))
{
//图片的文件流 图片类型 图片名称 设置的宽度以及高度
xwpfRun2.AddPicture(picData, (int)PictureType.PNG, "word.png", NPOI.Util.Units.ToEMU(100), NPOI.Util.Units.ToEMU(100));
}
using (MemoryStream ms = new MemoryStream())
{
document.Write(ms);
return ms.GetBuffer();
}
}
catch(Exception ex)
{
return null;
}
}
///
///
///
///
///
///
///
private NPOI.XWPF.UserModel.XWPFParagraph GetParagraph(NPOI.XWPF.UserModel.XWPFTable table,bool isbold,string content, bool iscenter=true)
{
var para = new CT_P();
//设置单元格文本对齐
para.AddNewPPr().AddNewTextAlignment();
NPOI.XWPF.UserModel.XWPFParagraph paragraph = new NPOI.XWPF.UserModel.XWPFParagraph(para,table.Body);
paragraph.VerticalAlignment = NPOI.XWPF.UserModel.TextAlignment.CENTER;
if (iscenter)
{
paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;
}
else
{
paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.LEFT;
}
NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
xwpfRun.IsBold = isbold;//文字加粗
xwpfRun.SetText(content);//填充内容
xwpfRun.FontSize = 9;//设置文字大小
xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定
return paragraph;
}
///
/// 插入图片
///
///
///
private NPOI.XWPF.UserModel.XWPFParagraph GetParagraphImg(NPOI.XWPF.UserModel.XWPFTable table, bool isbold, string content, bool iscenter = true)
{
var para = new CT_P();
//设置单元格文本对齐
para.AddNewPPr().AddNewTextAlignment();
NPOI.XWPF.UserModel.XWPFParagraph paragraph = new NPOI.XWPF.UserModel.XWPFParagraph(para, table.Body);
paragraph.VerticalAlignment = NPOI.XWPF.UserModel.TextAlignment.CENTER;
if (iscenter)
{
paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;
}
else
{
paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.LEFT;
}
NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
xwpfRun.IsBold = isbold;//文字加粗
xwpfRun.SetText(content);//填充内容
xwpfRun.FontSize = 9;//设置文字大小
xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定
using (FileStream picData = new FileStream(HttpContext.Current.Server.MapPath("/Upload/Word/word.png"), FileMode.Open, FileAccess.Read))
{
//图片的文件流 图片类型 图片名称 设置的宽度以及高度
xwpfRun.AddPicture(picData, (int)PictureType.PNG, "word.png", NPOI.Util.Units.ToEMU(100), NPOI.Util.Units.ToEMU(100));
}
return paragraph;
}
}
}