//第二种 导出的方式 NPIO方式
public ActionResult ExportToExcel2()
{
//创建工作簿
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
//创建 工作表
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("SheetTest"); ///通过 工作簿来创建一个 工作表
//创建Cell 单元格
NPOI.SS.UserModel.ICell cell1; //
int i = 0;
int rowLimit = 100;
DateTime originalTime = DateTime.Now;
for (i = 0; i < rowLimit; i++)
{
cell1 = sheet1.CreateRow(i).CreateCell(0);
cell1.SetCellValue("值" + i.ToString());
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
var buffer = ms.GetBuffer();
ms.Close();
return(File(buffer, "application/ms-excel", "test.xlsx"));
}
}
/// <summary>
/// 实体列表导出EXCEL
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">列表</param>
/// <param name="sheetName">Sheet名</param>
/// <param name="tableTitle">表头名称</param>
/// <param name="action">数据动作</param>
/// <returns></returns>
public static byte[] ExportExcelByList <T>(List <T> list, string sheetName, string[] tableTitle, Action <T, IRow> action) where T : class, new()
{
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow Title = null;
IRow rows = null;
int row = 0;
//表头
if (tableTitle.Length > 0)
{
row++;
Title = sheet.CreateRow(0);
for (int k = 0; k < tableTitle.Length; k++)
{
Title.CreateCell(k).SetCellValue(tableTitle[k]);
}
}
list.ForEach(it => {
rows = sheet.CreateRow(row);
action(it, rows);
row++;
});
byte[] buffer = new byte[1024 * 5];
using (MemoryStream ms = new MemoryStream()) {
workbook.Write(ms);
buffer = ms.GetBuffer();
ms.Close();
}
return(buffer);
}
/// <summary>
/// 向excel添加图片
/// </summary>
/// <param name="XlsFilePath"></param>
/// <param name="pictureData"></param>
/// <param name="sheetName"></param>
public static void AddPicture(string XlsFilePath, byte[] pictureData, bool isNewSheet, string sheetName = "PicTure")
{
FileStream fs = File.OpenRead(XlsFilePath);
//IWorkbook workbook = new XSSFWorkbook(fs);
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); // (NPOI.XSSF.UserModel.XSSFWorkbook)WorkbookFactory.Create(XlsFilePath);
fs.Close();
int pictureIdx = book.AddPicture(pictureData, PictureType.JPEG);
NPOI.SS.UserModel.ISheet sheet = null;
if (!isNewSheet)
{
sheet = book.GetSheetAt(0);
}
else
{
sheet = book.CreateSheet(sheetName);
}
IDrawing patriarch = sheet.CreateDrawingPatriarch();
NPOI.XSSF.UserModel.XSSFClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
using (FileStream fileStream = File.Open(XlsFilePath,
FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
book.Write(fileStream);
fileStream.Close();
}
}
public static void ExcelOut(DataTable dt, string fullXlsFilePath)
{
//创建Excel文件的对象
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headRow = sheet1.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headRow.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//加上设置大小下载下来的.xlsx文件打开时才没有错误
FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);
book.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意
FileStream fs = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
long fileSize = fs.Length;
//加上设置大小下载下来的.xlsx文件打开时才没有错误
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public static void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
/// <summary>
/// 将DataTable数据表内容读取到Excel导出
/// </summary>
/// <param name="dt"></param>
/// <param name="baseUri"></param>
/// <param name="hostingEnvironment"></param>
/// <returns></returns>
public static Notification ExportDataTableToExcel(DataTable dt,
string baseUri,
IHostingEnvironment hostingEnvironment)
{
string fileName = Guid.NewGuid().ToString() + ".xlsx";
string fileDir = hostingEnvironment.WebRootPath + "\\Content\\TempFiles\\";
if (!Directory.Exists(fileDir))
{
Directory.CreateDirectory(fileDir);
}
string filePath = "/Content/TempFiles/" + fileName;
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow Title = sheet.CreateRow(0);
for (int k = 0; k < dt.Columns.Count; k++)
{
Title.CreateCell(k).SetCellValue(dt.Columns[k].ColumnName);
}
IRow rows = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
rows = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rows.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
using (var stream = new FileStream(fileDir + fileName, FileMode.Create))
{
try
{
workbook.Write(stream);
return(new Notification(NotifyType.Success, "导出成功", new
{
Name = fileName,
Url = baseUri + filePath,
_Url = filePath
}));
}
catch (Exception ex)
{
return(new Notification(NotifyType.Error, string.Format("导出成功：{0}。", ex.Message)));
}
}
}
/// <summary>
/// 动态表头导出
/// </summary>
/// <param name="dt"></param>
/// <param name="list"></param>
/// <param name="fullXlsFilePath"></param>
public static void ExcelOut(DataTable dt, Dictionary <string, string> list, string fullXlsFilePath)
{
if (!System.IO.Directory.Exists(fullXlsFilePath))
{
var str = fullXlsFilePath.Substring(0, fullXlsFilePath.LastIndexOf('\\'));
System.IO.Directory.CreateDirectory(str);
}
//创建Excel文件的对象
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headRow = sheet1.CreateRow(0);
int headnum = 0;
foreach (var item in list)
{
headRow.CreateCell(headnum).SetCellValue(item.Value);
headnum++;
}
int num = 1;
foreach (DataRow item in dt.Rows)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(num);
int connum = 0;
foreach (var key in list)
{
rowtemp.CreateCell(connum).SetCellValue(item[key.Key] == DBNull.Value ? "" : item[key.Key].ToString());
connum++;
}
num++;
}
//加上设置大小下载下来的.xlsx文件打开时才没有错误
FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);
book.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意
FileStream fs = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
long fileSize = fs.Length;
//加上设置大小下载下来的.xlsx文件打开时才没有错误
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
}
public byte[] ExportXLSX()
{
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");
// 第一列
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("第一列第一行");
// 第二列
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("第二列第一行");
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
//Response.BinaryWrite(ms.ToArray());
byte[] excel = ms.ToArray();
book = null;
ms.Close();
ms.Dispose();
return(excel);
}
public FileResult getExport()
{
DataTable dt = new LowValueManager().getExcel();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
List <string> ttls = new List <string> {
"数据库记录ID", "单据编号", "领物单位", "验收日期", "编号1", "名称1", "规格型号1", "单位1", "数量1", "单价1", "金额1（元）", "编号2", "名称2", "规格型号2", "单位2", "数量2", "单价2", "金额2（元）", "编号3", "名称3", "规格型号3", "单位3", "数量3", "单价3", "金额3（元）", "编号4", "名称4", "规格型号4", "单位4", "数量4", "单价4", "金额4（元）", "其他总额合计（元）", "合计", "部门负责人", "采购人", "领用人", "验收人", "更新日期", "创建日期"
};
List <int> cols = new List <int>();
for (int i = 0; i < 40; i++)
{
cols.Add(i);
}
ExportHelper.CreateExcel(workbook, ttls, cols, dt, "低值耐用品及耗材验收单", "低值耐用品及耗材验收单", "");
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
var buffer = ms.GetBuffer();
ms.Close();
return(File(buffer, "application/ms-excel", "验收单.xlsx"));
}
}
/// <summary>
/// 多标签页导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="dataTableToExcelList"></param>
/// <returns></returns>
public static bool DataTableToExcel(string fileName, List <DataTableToExcelPram> dataTableToExcelList)
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var fs = new System.IO.FileStream(fileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite);
foreach (var sheetData in dataTableToExcelList)
{
int i = 0;
int j = 0;
NPOI.SS.UserModel.ISheet sheet = null;
try
{
#region 生成多头
if (workbook != null)
{
if (!String.IsNullOrWhiteSpace(sheetData.strHeaderText))
{
sheet = workbook.CreateSheet(sheetData.strHeaderText);
}
else
{
sheet = workbook.CreateSheet();
}
}
else
{
return(false);
}
var rowIndex = 0;
if (!String.IsNullOrEmpty(sheetData.strHeaderText) && sheetData.isWriteHeader)
{
var headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sheetData.strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 18;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, sheetData.data.Columns.Count - 1));
rowIndex = rowIndex + 1;
}
var cellStyle = workbook.CreateCellStyle();
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
cellStyle.WrapText = true;
//缩小字体填充
cellStyle.ShrinkToFit = false;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
if (sheetData.isColumnWritten)
{
for (j = 0; j < sheetData.data.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
cell.SetCellValue(sheetData.data.Columns[j].ColumnName);
cell.CellStyle = cellStyle;
}
rowIndex = rowIndex + 1;
}
for (i = 0; i < sheetData.data.Rows.Count; ++i)
{
row = sheet.CreateRow(rowIndex);
for (j = 0; j < sheetData.data.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
cell.SetCellValue(sheetData.data.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
rowIndex = rowIndex + 1;
}
for (j = 0; j < sheetData.data.Columns.Count; ++j)
{
sheet.AutoSizeColumn(j);
}
if (sheetData.regions != null)
{
foreach (NPOI.SS.Util.CellRangeAddress region in sheetData.regions)
{
sheet.AddMergedRegion(region);
}
}
#endregion
}
catch
{
continue;
}
}
workbook.Write(fs);
fs.Close();
return(true);
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public static bool DataTableToExcel(string fileName, System.Data.DataTable data, string strHeaderText = "")
{
int i = 0;
int j = 0;
NPOI.SS.UserModel.ISheet sheet = null;
var fs = new System.IO.FileStream(fileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite);
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
// workbook = new XSSFWorkbook();
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
// workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet();
}
else
{
return(false);
}
var rowIndex = 0;
if (string.IsNullOrEmpty(strHeaderText) == false)
{
var headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 18;
//font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, data.Columns.Count - 1));
rowIndex = rowIndex + 1;
}
var cellStyle = workbook.CreateCellStyle();
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
//cellStyle.WrapText = true;
//缩小字体填充
cellStyle.ShrinkToFit = false;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
for (j = 0; j < data.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
cell.SetCellValue(data.Columns[j].ColumnName);
cell.CellStyle = cellStyle;
}
rowIndex = rowIndex + 1;
for (i = 0; i < data.Rows.Count; ++i)
{
row = sheet.CreateRow(rowIndex);
for (j = 0; j < data.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
cell.SetCellValue(data.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
rowIndex = rowIndex + 1;
}
for (j = 0; j < data.Columns.Count; ++j)
{
sheet.AutoSizeColumn(j);
}
workbook.Write(fs);
fs.Close();
return(true);
}
catch
{
return(false);
}
}
private string ExportExcel(List <StarInfo> list, string filePath, int pageStart, int pageEnd, string sex, string area)
{
try
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
string fileName = "第" + pageStart + "页至第" + pageEnd + "页" + sex + "明星(地区：" + area + ").xlsx";
filePath = filePath + @"\" + fileName;
#region 导出封装
NPOI.SS.UserModel.IWorkbook book = null;
//if (sflg.FilterIndex == 1)
//{
// book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//}
//else
//{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
//}
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
int pictureCellCount = list.First().PicturePath.Count;
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("性别");
row1.CreateCell(2).SetCellValue("归属地");
for (int i = 0; i < pictureCellCount; i++)
{
row1.CreateCell(3 + i).SetCellValue("图片" + (i + 1));
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].Name);
rowtemp.CreateCell(1).SetCellValue(list[i].Sex);
rowtemp.CreateCell(2).SetCellValue(list[i].Area);
for (int j = 0; j < list[i].PicturePath.Count; j++)
{
rowtemp.CreateCell(3 + j).SetCellValue(list[i].PicturePath[j]);
}
}
#endregion
using (var fs = File.OpenWrite(filePath))
{
book.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
return("success");
}
//using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
}
catch (Exception ex)
{
return("");
}
}
public void CreateExcelFile()
{
//string FileName = @"d:\5.5.5.101_5555_Log_18.txt";
string FileName = ini12.INIRead(MonkeyTestPath, "Monket Test", "Path", "") + @"\logcat.txt";
string[] filelist = File.ReadAllLines(FileName, Encoding.Default);
List <string> StringLists = new List <string>();
for (int linenum = filelist.Length - 1; linenum >= 0; linenum--)
{
if (filelist[linenum].IndexOf("ANR") > -1)
{
int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length;
int last = filelist[linenum].LastIndexOf(" (");
string str2 = filelist[linenum].Substring(first, last - first);
StringLists.Add(str2);
Console.WriteLine(str2);
}
}
////建立Excel 2007檔案
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//合併區
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行
//背景色（藍色）
ICellStyle cellStyle0 = workbook.CreateCellStyle();
cellStyle0.FillPattern = FillPattern.SolidForeground;
cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index;
cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（綠色）
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = IndexedColors.Lime.Index;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（粉色）
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillForegroundColor = IndexedColors.Tan.Index;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（灰色）
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.FillPattern = FillPattern.SolidForeground;
cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index;
//背景色（白色）
ICellStyle cellStyle4 = workbook.CreateCellStyle();
cellStyle4.FillPattern = FillPattern.SolidForeground;
cellStyle4.FillForegroundColor = IndexedColors.White.Index;
//Summary儲存格格式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.FontHeightInPoints = 18;
summaryStyle.SetFont(summaryFont);
summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
//A列
sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time");
sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time");
sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No.");
sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device");
sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester");
for (int A = 0; A < 8; A++)
{
sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0;
}
//E列
sheet.GetRow(0).CreateCell(4).SetCellValue("Date");
sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)");
sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES");
sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash");
sheet.GetRow(4).CreateCell(4).SetCellValue("Result");
sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW");
sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash");
for (int E = 0; E < 7; E++)
{
sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0;
}
sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4;
//F列
sheet.GetRow(0).CreateCell(5).SetCellValue("-----");
sheet.GetRow(1).CreateCell(5).SetCellValue("-----");
sheet.GetRow(2).CreateCell(5).SetCellValue("-----");
sheet.GetRow(3).CreateCell(5).SetCellValue("-----");
sheet.GetRow(4).CreateCell(5).SetCellValue("");
sheet.GetRow(5).CreateCell(5).SetCellValue("-----");
sheet.GetRow(6).CreateCell(5).SetCellValue("-----");
for (int F = 0; F < 7; F++)
{
sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2;
}
sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4;
//Summary
sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary");
sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle;
//Error List
sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List");
sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3;
//Total
sheet.GetRow(10).CreateCell(6).SetCellValue("Total");
sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3;
//搜尋相同字串並記次
Dictionary <string, int> dict = new Dictionary <string, int>();
foreach (string myStringList in StringLists)
{
if (dict.ContainsKey(myStringList))
{
//如果Dictionary中存在这个关键词元素，则把这个Dictionary的key+1
dict[myStringList]++;
}
else
{
//如果Dictionary中不存在这个关键词元素，则把它添加进Dictionary
dict.Add(myStringList, 1);
}
}
int rowcnt = dict.Count;
while (rowcnt != 0)
{
foreach (KeyValuePair <string, int> item in dict)
{
Console.WriteLine(item.Key);
Console.WriteLine(item.Value);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行
sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key);
sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value);
rowcnt--;
}
}
for (int c = 0; c <= 25; c++)
{
sheet.AutoSizeColumn(c);
}
FileStream file = new FileStream(ini12.INIRead(MonkeyTestPath, "Monket Test", "Path", "") + @"\MonkeyTest Report.xlsx", FileMode.Create);//產生檔案
workbook.Write(file);
file.Close();
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
if (sfdReporters.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
//输出的Excel路径
string excelFile = sfdReporters.FileName;
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
#region 生成详细页
//创建一张数据表
DataTable dt = new DataTable();
#region 添加列字段
dt.Columns.Add("领域", typeof(string));
dt.Columns.Add("技术方向代码", typeof(string));
dt.Columns.Add("技术方向名称", typeof(string));
dt.Columns.Add("项目名称", typeof(string));
dt.Columns.Add("密级", typeof(string));
dt.Columns.Add("关键词", typeof(string));
dt.Columns.Add("单位名称", typeof(string));
dt.Columns.Add("单位常用名", typeof(string));
dt.Columns.Add("联系人", typeof(string));
dt.Columns.Add("联系电话", typeof(string));
dt.Columns.Add("项目负责人", typeof(string));
dt.Columns.Add("项目负责人电话", typeof(string));
dt.Columns.Add("身份证号", typeof(string));
dt.Columns.Add("通信地址", typeof(string));
dt.Columns.Add("研究周期", typeof(string));
dt.Columns.Add("研究经费", typeof(string));
dt.Columns.Add("研究目标", typeof(string));
dt.Columns.Add("项目摘要", typeof(string));
dt.Columns.Add("基础性问题", typeof(string));
string[] chsNumbers = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" };
for (int k = 0; k < 10; k++)
{
dt.Columns.Add("课题" + chsNumbers[k] + "名称", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "密级", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "负责人", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "身份证号", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "承担单位名称", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "研究经费", typeof(string));
}
dt.Columns.Add("项目负责人具体情况", typeof(string));
dt.Columns.Add("与有关计划关系", typeof(string));
#endregion
//拼凑数据
List <Catalog> nowCatalogs = ConnectionManager.Context.table("Catalog").select("*").getList <Catalog>(new Catalog());
foreach (Catalog catalog in nowCatalogs)
{
//项目信息
Project mainProj = ConnectionManager.Context.table("Project").where ("CatalogID = '" + catalog.CatalogID + "' and Type='项目'").select("*").getItem <Project>(new Project());
//承担单位
Unit mainUnit = ConnectionManager.Context.table("Unit").where ("ID = '" + mainProj.UnitID + "' and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Unit>(new Unit());
//项目负责人
Person mainPerson = ConnectionManager.Context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and CatalogID = '" + catalog.CatalogID + "') and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Person>(new Person());
if (string.IsNullOrEmpty(mainProj.ID) || string.IsNullOrEmpty(mainUnit.ID) || string.IsNullOrEmpty(mainPerson.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")信息不全无法改出！");
//信息不全
continue;
}
else
{
//拼装每个项目的信息
List <object> cells = new List <object>();
cells.Add(mainProj.Domain);
cells.Add(mainProj.DirectionCode);
cells.Add(mainProj.Direction);
cells.Add(mainProj.Name);
cells.Add(mainProj.SecretLevel);
cells.Add(mainProj.Keywords);
cells.Add(mainUnit.UnitName);
cells.Add(mainUnit.NormalName);
cells.Add(mainUnit.ContactName);
cells.Add(mainUnit.Telephone);
cells.Add(mainPerson.Name);
cells.Add(mainPerson.MobilePhone);
cells.Add(mainPerson.IDCard);
cells.Add(mainPerson.Address);
cells.Add(mainProj.TotalTime);
cells.Add(mainProj.TotalMoney);
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_4.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_0.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_5.rtf"));
//创建课题信息
List <Project> subjectList = ConnectionManager.Context.table("Project").where ("Type='课题' and CatalogID = '" + catalog.CatalogID + "'").select("*").getList <Project>(new Project());
foreach (Project sub in subjectList)
{
Unit subUnits = ConnectionManager.Context.table("Unit").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + sub.UnitID + "'").select("*").getItem <Unit>(new Unit());
Task subTasks = ConnectionManager.Context.table("Task").where ("Role = '负责人' and CatalogID = '" + catalog.CatalogID + "' and ProjectID = '" + sub.ID + "'").select("*").getItem <Task>(new Task());
if (string.IsNullOrEmpty(subUnits.ID) || string.IsNullOrEmpty(subTasks.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全！");
//信息不全
continue;
}
else
{
Person subPersons = ConnectionManager.Context.table("Person").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + subTasks.PersonID + "'").select("*").getItem <Person>(new Person());
if (string.IsNullOrEmpty(subPersons.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全！");
//信息不全
continue;
}
else
{
cells.Add(sub.Name);
cells.Add(sub.SecretLevel);
cells.Add(subPersons.Name);
cells.Add(subPersons.IDCard);
cells.Add(subUnits.UnitName);
cells.Add(subTasks.TotalMoney);
}
}
}
//给其它的课题信息项填充空格
for (int kk = 0; kk < (60 - (subjectList.Count * 6)); kk++)
{
cells.Add(string.Empty);
}
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_14.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_19.rtf"));
dt.Rows.Add(cells.ToArray());
}
}
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#endregion
//输出到Excel文件
workbook.Write(memoryStream);
File.WriteAllBytes(excelFile, memoryStream.ToArray());
MessageBox.Show("导出完成！路径：" + excelFile, "提示");
//打开Excel文件
System.Diagnostics.Process.Start(excelFile);
}
catch (Exception ex)
{
MessageBox.Show("对不起，导出失败！Ex:" + ex.ToString());
}
}
}
/// <summary>
/// 写错误日志的Excel文件
/// </summary>
private string writeErrorExcelFile()
{
try
{
//输出的Excel路径
string excelFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "缺少的文件_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx");
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
//输出Sheet表格
//MainForm.writeSheet(workbook, cellStyleA, cellStyleB, errorFileList);
//输出到Excel文件
workbook.Write(memoryStream);
File.WriteAllBytes(excelFile, memoryStream.ToArray());
if (errorFileList != null && errorFileList.Count >= 1)
{
MessageBox.Show("缺少文件列表生成完成!路径:" + excelFile);
try
{
System.Diagnostics.Process.Start(excelFile);
}
catch (Exception ex) { }
}
return(excelFile);
}
catch (Exception ex)
{
MainForm.writeLog(ex.ToString());
return(string.Empty);
}
}
protected void btnSampleDataFile_Click(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(this.txtReportName.Text))
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
String.Format(AppNum.ErrorMsg.fieldcannotbeempty, "Report Name"));
return;
}
#region Get ColumnInfo
CUSTOMRP.Model.SOURCEVIEW sv = WebHelper.bllSOURCEVIEW.GetModel(me.ID, Int32.Parse(this.ddlQueryName.SelectedValue));
List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> svclist = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, sv.ID).OrderBy(x => x.DisplayName).ToList();
string[] colnames = svclist.Select(x => x.DisplayName).ToArray();
switch (sv.SourceType)
{
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.View:
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.Table:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check view defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.StoredProc:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForStoredProc(me.ID, me.DatabaseNAME, sv.ID);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check stored proc defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
}
// Filter result to only columns that is requested
columninfos = columninfos.Where(x => colnames.Contains(x.ColName)).ToList();
#endregion Get ColumnInfo
StringBuilder selectclause = new StringBuilder(" TOP 5 ");
selectclause.Append(String.Join(",", columninfos.Select(x => "[" + x.DisplayName + "]").ToArray()));
//v1.2.0 - Cheong - 2016/07/04 - Add option to hide duplicate items - do not get duplicate data when getting sample
DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(me.ID, sv.ID, me.DatabaseNAME, selectclause.ToString(), CUSTOMRP.BLL.AppHelper.sql_plus(sv.ID, me), "", "", true);
NPOI.XSSF.UserModel.XSSFWorkbook XSSFworkbook = NPOIHelper.GetWorkbookAsMailMergeDataSource(dt);
string downloadFilename = this.txtReportName.Text.Trim().Replace(' ', '_') + ".xlsx";
string folder = PathHelper.getTempFolderName();
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
string filePath = folder + PathHelper.getSafePath(downloadFilename);
try
{
using (FileStream outFs = new FileStream(filePath, FileMode.Create))
{
XSSFworkbook.Write(outFs);
//outFs.Close();
}
FileInfo file = new System.IO.FileInfo(filePath);
Response.ContentType = "application/octet-stream";
//Response.AddHeader("Content-Disposition", "attachment; filename=\"" + downloadFilename + "\"");
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", downloadFilename, HttpUtility.UrlPathEncode(downloadFilename)));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.Flush();
p_fSuppressRender = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
finally
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
private void btnExportToExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.FileName = string.Empty;
sfd.Filter = "*.xlsx|*.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
//输出的Excel路径
string excelFile = sfd.FileName;
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//基本数据
DataTable dtBase = new DataTable();
//金额数据
DataTable dtMoney = new DataTable();
//人员数据
DataTable dtPerson = new DataTable();
#region 输出基本数据
//生成列
dtBase.Columns.Add("项目名称", typeof(string));
dtBase.Columns.Add("项目领域", typeof(string));
dtBase.Columns.Add("合同牵头单位", typeof(string));
dtBase.Columns.Add("项目负责人", typeof(string));
dtBase.Columns.Add("项目负责人电话", typeof(string));
dtBase.Columns.Add("研究周期", typeof(string));
dtBase.Columns.Add("项目总经费", typeof(string));
//dtBase.Columns.Add("研究目标", typeof(string));
//dtBase.Columns.Add("研究内容", typeof(string));
dtBase.Columns.Add("课题名称", typeof(string));
dtBase.Columns.Add("课题单位", typeof(string));
dtBase.Columns.Add("课题负责人", typeof(string));
dtBase.Columns.Add("课题负责人电话", typeof(string));
List <Catalog> catalogList = ConnectionManager.Context.table("Catalog").where ("CatalogType='建议书'").select("*").getList <Catalog>(new Catalog());
//生成内容
foreach (Catalog c in catalogList)
{
List <object> cells = new List <object>();
//项目信息
Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project());
//项目名称
cells.Add(p.ProjectName);
//项目领域
cells.Add(p.Domains);
//项目牵头单位
cells.Add(p.DutyUnit);
Person projectMaster = ConnectionManager.Context.table("Person").where ("IsProjectMaster='true' and JobInProject='负责人' and CatalogID = '" + c.CatalogID + "'").select("*").getItem <Person>(new Person());
//项目负责人
cells.Add(projectMaster.PersonName);
//项目负责人电话
cells.Add(projectMaster.Telephone);
//研究周期
cells.Add(p.TotalTime);
//总经费
cells.Add(p.TotalMoney);
////研究目标
//cells.Add(getTxtContent(c, "研究目标"));
////研究内容
//cells.Add(string.Empty);
StringBuilder sbSubjectNames = new StringBuilder();
StringBuilder sbSubjectUnits = new StringBuilder();
StringBuilder sbSubjectPersons = new StringBuilder();
StringBuilder sbSubjectPhones = new StringBuilder();
//课题列表
List <Subject> subjectList = ConnectionManager.Context.table("Subject").where ("CatalogID = '" + c.CatalogID + "'").select("*").getList <Subject>(new Subject());
foreach (Subject s in subjectList)
{
sbSubjectNames.AppendLine(s.SubjectName);
sbSubjectUnits.AppendLine(s.DutyUnit);
Person subjectMaster = ConnectionManager.Context.table("Person").where ("JobInProject='负责人' and CatalogID = '" + c.CatalogID + "' and SubjectID = '" + s.SubjectID + "'").select("*").getItem <Person>(new Person());
sbSubjectPersons.AppendLine(subjectMaster.PersonName);
sbSubjectPhones.AppendLine(subjectMaster.Telephone);
}
cells.Add(sbSubjectNames.ToString());
cells.Add(sbSubjectUnits.ToString());
cells.Add(sbSubjectPersons.ToString());
cells.Add(sbSubjectPhones.ToString());
dtBase.Rows.Add(cells.ToArray());
}
#endregion
#region 输出金额数据
//生成列
dtMoney.Columns.Add("项目领域", typeof(string));
dtMoney.Columns.Add("项目名称", typeof(string));
//dtMoney.Columns.Add("合同编号", typeof(string));
dtMoney.Columns.Add("研究周期", typeof(string));
dtMoney.Columns.Add("总经费", typeof(string));
dtMoney.Columns.Add("各年度经费(万元)", typeof(string));
//dtMoney.Columns.Add("各年度拨付时间", typeof(string));
//生成内容
foreach (Catalog c in catalogList)
{
List <object> cells = new List <object>();
//项目信息
Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project());
cells.Add(p.Domains);
cells.Add(p.ProjectName);
//cells.Add(p.ProjectNumber);
cells.Add(p.TotalTime);
cells.Add(p.TotalMoney);
//生成各年度经费和时间字符串
StringBuilder sbMoneyNum = new StringBuilder();
//StringBuilder sbMoneyTime = new StringBuilder();
for (int kkk = 1; kkk <= 5; kkk++)
{
sbMoneyNum.AppendLine(ConnectionManager.Context.table("Dicts").where ("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "'").select("DictValue").getValue <string>(string.Empty));
//sbMoneyTime.AppendLine(ConnectionManager.Context.table("Dicts").where("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "_SendDate'").select("DictValue").getValue<string>(string.Empty));
}
cells.Add(sbMoneyNum.ToString());
//cells.Add(sbMoneyTime.ToString());
dtMoney.Rows.Add(cells.ToArray());
}
#endregion
#region 输出人员数据
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dtBase);
//写入金额数据
writeSheet(workbook, cellStyleA, cellStyleB, dtMoney);
//写入人员数据
writeSheet(workbook, cellStyleA, cellStyleB, dtPerson);
#region 输出文件并打开文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
//显示提示
MessageBox.Show("导出完成！路径：" + excelFile, "提示");
//打开Excel文件
System.Diagnostics.Process.Start(excelFile);
#endregion
}
catch (Exception ex)
{
MessageBox.Show("对不起，导出失败！Ex:" + ex.ToString());
}
}
}
private async void executeExportCommand(object obj)
{
IsRunning = true;
await Task.Run(() =>
{
try
{
reader.CheckPermission();
var filePath = Path.Combine(reader.GetFolderPath(), "SmsExport-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx");
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
//第一行
{
var row = sheet.CreateRow(0);
var cColumnInd = 0;
row.CreateCell(cColumnInd).SetCellValue("编号");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("号码");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("时间");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("类型");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("内容");
cColumnInd++;
}
//当前行号
var cRowInd = 1;
var totalCount = reader.GetCount(StartIndex);
var enumerator = reader.GetEnumerator(StartIndex);
var index = 0;
while (enumerator.MoveNext())
{
index++;
var item = enumerator.Current;
var typeStr = "";
switch (item.ItemType)
{
case SmsItem.SmsItemType.Send:
typeStr = "发送";
break;
case SmsItem.SmsItemType.Recv:
typeStr = "接收";
break;
default:
continue;
}
{
var cColumnInd = 0;
var row = sheet.CreateRow(cRowInd);
NPOI.SS.UserModel.ICell cell = null;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Id);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Address);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Date.ToString("yyyy-MM-dd HH:mm:ss"));
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(typeStr);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Body);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
}
cRowInd++;
Progress = index * 1D / totalCount;
Message = $"读取短信[{index} / {totalCount}]中...";
}
Message = $"正在保存到文件...";
using (var stream = File.OpenWrite(filePath))
workbook.Write(stream);
Message = $"已保存到{filePath}";
}
catch (Exception ex)
{
Message = "错误," + ex;
}
});
IsRunning = false;
}
/// <summary>
/// NPOI 导出会议的管理人员
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void ExportMeeting()
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("电话号码");
row1.CreateCell(2).SetCellValue("会议名称");
row1.CreateCell(3).SetCellValue("开始时间");
row1.CreateCell(4).SetCellValue("结束时间");
//设置列宽
row1.Sheet.SetColumnWidth(0, 100 * 50);
row1.Sheet.SetColumnWidth(1, 100 * 50);
row1.Sheet.SetColumnWidth(2, 150 * 50);
row1.Sheet.SetColumnWidth(3, 100 * 50);
row1.Sheet.SetColumnWidth(4, 100 * 50);
MessasgeData mgdata = new MessasgeData();
mgdata = Datafun.MgfunctionData("select Bank,UserName,b.MeetingName,convert(nvarchar(11),Start_Date,120) ,convert(nvarchar(11),End_Date,120) from tb_login a left join tb_Meeting b on a.Meeting=b.id where Lvl=1 and a.isdel=1 ");
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "会议管理人员.xlsx")); //添加Excel名字
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
private void btnExportYearMoneyToExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.FileName = string.Empty;
sfd.Filter = "*.xlsx|*.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
//输出的Excel路径
string excelFile = sfd.FileName;
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//基本数据
DataTable dtBase = new DataTable();
#region 输出基本数据
//生成列
dtBase.Columns.Add("合同编号", typeof(string));
dtBase.Columns.Add("合同名称", typeof(string));
dtBase.Columns.Add("承担单位", typeof(string));
dtBase.Columns.Add("项目负责人", typeof(string));
dtBase.Columns.Add("联系电话", typeof(string));
dtBase.Columns.Add("职务职称", typeof(string));
dtBase.Columns.Add("周期", typeof(string));
dtBase.Columns.Add("总经费", typeof(string));
dtBase.Columns.Add("第一年度经费", typeof(string));
dtBase.Columns.Add("第二年度经费", typeof(string));
dtBase.Columns.Add("第三年度经费", typeof(string));
dtBase.Columns.Add("第四年度经费", typeof(string));
dtBase.Columns.Add("第五年度经费", typeof(string));
dtBase.Columns.Add("密级", typeof(string));
dtBase.Columns.Add("起止时间", typeof(string));
dtBase.Columns.Add("单位通信地址", typeof(string));
List <Catalog> catalogList = ConnectionManager.Context.table("Catalog").where ("CatalogType='合同书'").select("*").getList <Catalog>(new Catalog());
//生成内容
foreach (Catalog c in catalogList)
{
List <object> cells = new List <object>();
//项目信息
Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project());
//合同编号
cells.Add(p.ProjectNumber);
//合同名称
cells.Add(p.ProjectName);
//承担单位
cells.Add(p.DutyUnit);
Person projectMaster = ConnectionManager.Context.table("Person").where ("IsProjectMaster='true' and JobInProject='负责人' and CatalogID = '" + c.CatalogID + "'").select("*").getItem <Person>(new Person());
//项目负责人
cells.Add(projectMaster.PersonName);
//联系电话
cells.Add(projectMaster.Telephone);
//职务职称
cells.Add(projectMaster.PersonJob);
//周期
cells.Add(p.TotalTime);
//总经费
cells.Add(p.TotalMoney);
//第一年度经费,第二年度经费,第三年度经费,第四年度经费,第五年度经费
for (int kkk = 1; kkk <= 5; kkk++)
{
cells.Add(ConnectionManager.Context.table("Dicts").where ("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "'").select("DictValue").getValue <string>(string.Empty));
}
//密级
cells.Add(p.SecretLevel);
//起止时间(拨付时间)
List <DateTime> dtList = new List <DateTime>();
DataList dlMoneySendList = ConnectionManager.Context.table("MoneySends").where ("CatalogID='" + c.CatalogID + "'").orderBy("WillTime").select("WillTime").getDataList();
if (dlMoneySendList.getRowCount() >= 1)
{
foreach (DataItem diiii in dlMoneySendList.getRows())
{
DateTime willTime = diiii.get("WillTime") != null?DateTime.Parse(diiii.get("WillTime").ToString()) : DateTime.Now;
dtList.Add(willTime);
}
}
if (dtList.Count >= 2)
{
cells.Add(dtList[0].ToString("yyyy年MM月dd日") + "~" + dtList[dtList.Count - 1].ToString("yyyy年MM月dd日"));
}
else if (dtList.Count == 1)
{
cells.Add(dtList[0].ToString("yyyy年MM月dd日") + "~");
}
else
{
cells.Add("");
}
//单位通信地址
cells.Add(p.DutyUnitAddress);
dtBase.Rows.Add(cells.ToArray());
}
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dtBase);
#region 输出文件并打开文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
//显示提示
MessageBox.Show("导出完成！路径：" + excelFile, "提示");
//打开Excel文件
System.Diagnostics.Process.Start(excelFile);
#endregion
}
catch (Exception ex)
{
MessageBox.Show("对不起，导出失败！Ex:" + ex.ToString());
}
}
}
/// <summary>
/// 生成excel
/// </summary>
/// <param name="columns">栏位，格式为：{
/// column_name:'栏位名称',
/// column_display_name:'表头显示名称'
/// }
/// </param>
/// <param name="data">数据，格式为：
/// {
///column_name1:'值',
///column_name2:'值',
/// .....
///column_nameN:'值',
///}
/// </param>
/// <returns></returns>
public byte[] GenerateExcel(IEnumerable <FrameDLRObject> columns, IEnumerable <FrameDLRObject> data)
{
IWorkbook workbook = null;
try
{
using (var ms = new MemoryStream())
{
ms.Seek(0, SeekOrigin.Begin);
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(ms);
}
ISheet sheet = workbook.GetSheetAt(0);
var index = 0;
//设置表头
var header = sheet.CreateRow(index);
var headerstyle = workbook.CreateCellStyle();
headerstyle.Alignment = HorizontalAlignment.Center;
headerstyle.BorderBottom = BorderStyle.Thin;
headerstyle.BorderTop = BorderStyle.Thin;
headerstyle.BorderLeft = BorderStyle.Thin;
headerstyle.BorderRight = BorderStyle.Thin;
headerstyle.FillBackgroundColor = IndexedColors.BlueGrey.Index;
headerstyle.FillPattern = FillPattern.SolidForeground;
var cellindex = 0;
foreach (dynamic item in columns)
{
var cell = header.CreateCell(cellindex);
cell.CellStyle = headerstyle;
cell.SetCellValue(item.column_display_name);
cellindex++;
}
cellindex = 0;
var datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Center;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
foreach (var item in data)
{
index++;
var row = sheet.CreateRow(index);
foreach (dynamic c in columns)
{
var cell = row.CreateCell(cellindex);
cell.CellStyle = datastyle;
var v = item.GetValue(c.column_name);
if (v == null)
{
cell.SetCellValue("");
}
else if (v is DateTime)
{
cell.SetCellValue(((DateTime)v).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(v);
}
}
cellindex++;
}
byte[] buffer = new byte[1024];
using (MemoryStream output = new MemoryStream())
{
workbook.Write(output);
buffer = output.ToArray();
}
return(buffer);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}
protected void btnDownloadDatafile_Click(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(this.txtReportName.Text))
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>",
String.Format(AppNum.ErrorMsg.fieldcannotbeempty, "Report Name"));
return;
}
#region Get ColumnInfo
CUSTOMRP.Model.SOURCEVIEW sv = WebHelper.bllSOURCEVIEW.GetModel(me.ID, Int32.Parse(this.ddlQueryName.SelectedValue));
List <CUSTOMRP.Model.SOURCEVIEWCOLUMN> svclist = WebHelper.bllSOURCEVIEWCOLUMN.GetModelsForSourceView(me.ID, sv.ID).OrderBy(x => x.DisplayName).ToList();
string[] colnames = svclist.Select(x => x.DisplayName).ToArray();
switch (sv.SourceType)
{
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.View:
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.Table:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check view defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForTblView(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
case CUSTOMRP.Model.SOURCEVIEW.SourceViewType.StoredProc:
{
try
{
columninfos = CUSTOMRP.BLL.AppHelper.GetColumnInfoForStoredProc(me.ID, me.DatabaseNAME, sv.ID);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
columninfos = new List <CUSTOMRP.Model.ColumnInfo>();
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert('Error in retrieving columns for [{0}]. Please check stored proc defination.');</script>", sv.TBLVIEWNAME);
}
if (colnames == null)
{
colnames = CUSTOMRP.BLL.AppHelper.GetColumnNamesForStoredProc(me.ID, me.DatabaseNAME, sv.TBLVIEWNAME);
}
}
break;
}
// Filter result to only columns that is requested
columninfos = columninfos.Where(x => colnames.Contains(x.ColName)).ToList();
#endregion Get ColumnInfo
StringBuilder selectclause = new StringBuilder("TOP 5 ");
//v1.2.0 Fai 2017.01.11 - Avoid to throw Exception if columninfos have no any items - Begin
//selectclause.Append(String.Join(",", columninfos.Select(x => "[" + x.DisplayName + "]").ToArray()));
if (columninfos.Select(x => "[" + x.DisplayName + "]").ToArray().Length > 0)
{
selectclause.Append(String.Join(",", columninfos.Select(x => "[" + x.DisplayName + "]").ToArray()));
}
else
{
selectclause.Append("*");
}
//v1.2.0 Fai 2017.01.11 - Avoid to throw Exception if columninfos have no any items - End
//v1.2.0 - Cheong - 2016/07/04 - Add option to hide duplicate items
//DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(sv.ID, me.DatabaseNAME, selectclause.ToString(), CUSTOMRP.BLL.AppHelper.sql_plus(sv.ID, me), "", "", true);
DataTable dt = CUSTOMRP.BLL.AppHelper.getDataForReport(me.ID, sv.ID, me.DatabaseNAME, selectclause.ToString(), CUSTOMRP.BLL.AppHelper.sql_plus(sv.ID, me), "", "", myReport.fHideDuplicate);
NPOI.XSSF.UserModel.XSSFWorkbook XSSFworkbook = NPOIHelper.GetWorkbookAsMailMergeDataSource(dt);
string path = g_Config["WordTemplatePath"];
string filenamewithoutextension = null;
string datafilename = null;
if ((myReport.WordFile != null) && File.Exists(path + myReport.WordFile.WordFileName))
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(myReport.WordFile.WordFileName);
if (filenamewithoutextension.LastIndexOf('.') > 0)
{
filenamewithoutextension = filenamewithoutextension.Substring(0, filenamewithoutextension.LastIndexOf('.'));
}
datafilename = filenamewithoutextension + ".xlsx";
}
else
{
CUSTOMRP.Model.WORDTEMPLATE wordtemplate = WebHelper.bllWORDTEMPLATE.GetModelBySVID(me.ID, sv.ID, me.ID);
if (wordtemplate != null)
{
filenamewithoutextension = Path.GetFileNameWithoutExtension(wordtemplate.TemplateFileName);
datafilename = wordtemplate.DataFileName;
}
}
if (datafilename == null)
{
this.lblJavascript.Text = String.Format("<script type=\"text/javascript\">alert(\"{0}\");</script>", AppNum.ErrorMsg.parameter_error);
return;
}
string folder = PathHelper.getTempFolderName();
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
string filePath = folder + PathHelper.getSafePath(datafilename);
try
{
using (FileStream outFs = new FileStream(filePath, FileMode.Create))
{
XSSFworkbook.Write(outFs);
//CA2202
//outFs.Close();
}
FileInfo file = new System.IO.FileInfo(filePath);
Response.ContentType = "application/octet-stream";
//Encode filename according to RFC5987
//Response.AddHeader("Content-Disposition", "attachment; filename=\"" + datafilename + "\"");
Context.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\"; filename*=utf-8''{1}", datafilename, HttpUtility.UrlPathEncode(datafilename)));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.Flush();
p_fSuppressRender = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
finally
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
/// <summary>
/// NPOI 宴会统计导出
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void Export(string id, string ids)
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("电话号码");
row1.CreateCell(2).SetCellValue("签到号");
row1.CreateCell(3).SetCellValue("红包");
row1.CreateCell(4).SetCellValue("礼物");
row1.CreateCell(5).SetCellValue("实际人数");
row1.CreateCell(6).SetCellValue("预计人数");
//设置列宽
row1.Sheet.SetColumnWidth(0, 80 * 50);
row1.Sheet.SetColumnWidth(1, 80 * 50);
row1.Sheet.SetColumnWidth(2, 50 * 50);
row1.Sheet.SetColumnWidth(3, 80 * 50);
row1.Sheet.SetColumnWidth(4, 150 * 50);
row1.Sheet.SetColumnWidth(5, 80 * 50);
row1.Sheet.SetColumnWidth(6, 80 * 50);
MessasgeData mgdata = new MessasgeData();
SqlParameter[] pms =
{
new SqlParameter("@meeting", id),
new SqlParameter("@ms", ids)
};
mgdata = Datafun.MgfunctionData("select MeetingName from tb_Meeting where id=@meeting", pms);
if (mgdata.Mgdatacount > 0)
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "" + mgdata.Mgdata.Rows[0][0].ToString() + ".xlsx"));//添加Excel名字
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "宴会统计.xlsx"));//添加Excel名字
}
mgdata = Datafun.MgfunctionData("Tj_rstj_poc", pms, "poc");
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.CreateCell(5).SetCellValue(mgdata.Mgdata.Rows[i][5].ToString());
rowtemp.CreateCell(6).SetCellValue(mgdata.Mgdata.Rows[i][6].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
rowtemp.GetCell(5).CellStyle = textStyle;
rowtemp.GetCell(6).CellStyle = numberStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
public async Task <byte[]> GetDataForExcelAsync(int testID)
{
//get data and only determination columns
var data = await GetDataWithMarkerForExcelAsync(testID);
//create excel file here
var wb = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet1 = wb.CreateSheet("Sheet1");
//write headers
var header = sheet1.CreateRow(0);
var ColNr = 0;
int maxMarkers = 20;
foreach (DataColumn dc in data.Columns)
{
ColNr = dc.Ordinal;
//var cell = header.CreateCell(dc.Ordinal);
if (!dc.ColumnName.EqualsIgnoreCase("Determinations"))
{
var cell = header.CreateCell(ColNr);
cell.SetCellValue(dc.ColumnName);
}
}
for (int i = 1; i <= maxMarkers; i++)
{
var cell = header.CreateCell(ColNr);
cell.SetCellValue("Marker" + i.ToString("00"));
ColNr++;
}
//write data
var rowNr = 1;
foreach (DataRow dr in data.Rows)
{
var row = sheet1.CreateRow(rowNr++);
foreach (DataColumn dc in data.Columns)
{
ColNr = dc.Ordinal;
var value = dr[dc.ColumnName].ToText();
if (dc.ColumnName.EqualsIgnoreCase("Determinations"))
{
var determinations = value.Split(',').ToList();
foreach (var determination in determinations)
{
var cell = row.CreateCell(ColNr);
cell.SetCellValue(determination);
ColNr++;
}
}
else
{
var cell = row.CreateCell(ColNr);
cell.SetCellValue(value);
}
}
}
byte[] result = null;
using (var ms = new MemoryStream())
{
wb.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
result = ms.ToArray();
}
return(result);
}
