/// <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>
/// 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();
}
/// <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();
}
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();
}
/// <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);
}
public ExcelCellStyle(NPOI.XSSF.UserModel.XSSFWorkbook book)
{
/*新建名为style的（普通）CellStyle，水平垂直居中*/
ICellStyle style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
/*新建名为styleLeft的（普通）CellStyle，水平垂直居左*/
ICellStyle styleLeft = book.CreateCellStyle();
styleLeft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
styleLeft.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
styleLeft.BorderBottom = BorderStyle.Thin;
styleLeft.BorderTop = BorderStyle.Thin;
styleLeft.BorderLeft = BorderStyle.Thin;
styleLeft.BorderRight = BorderStyle.Thin;
/*新建名为style4LemonChiffon的（普通）CellStyle，水平垂直居中*/
ICellStyle style4LemonChiffon = book.CreateCellStyle();
style4LemonChiffon.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style4LemonChiffon.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
style4LemonChiffon.FillPattern = FillPattern.SolidForeground;
style4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
style4LemonChiffon.BorderBottom = BorderStyle.Thin;
style4LemonChiffon.BorderTop = BorderStyle.Thin;
style4LemonChiffon.BorderLeft = BorderStyle.Thin;
style4LemonChiffon.BorderRight = BorderStyle.Thin;
/*新建名为style4CornflowerBlue的（普通）CellStyle，水平垂直居中*/
ICellStyle style4CornflowerBlue = book.CreateCellStyle();
style4CornflowerBlue.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style4CornflowerBlue.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
style4CornflowerBlue.FillPattern = FillPattern.SolidForeground;
style4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
style4CornflowerBlue.BorderBottom = BorderStyle.Thin;
style4CornflowerBlue.BorderTop = BorderStyle.Thin;
style4CornflowerBlue.BorderLeft = BorderStyle.Thin;
style4CornflowerBlue.BorderRight = BorderStyle.Thin;
/*新建名为style4LightGreen的（普通）CellStyle，水平垂直居中*/
ICellStyle style4LightGreen = book.CreateCellStyle();
style4LightGreen.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style4LightGreen.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style4LightGreen.FillPattern = FillPattern.SolidForeground;
style4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style4LightGreen.BorderBottom = BorderStyle.Thin;
style4LightGreen.BorderTop = BorderStyle.Thin;
style4LightGreen.BorderLeft = BorderStyle.Thin;
style4LightGreen.BorderRight = BorderStyle.Thin;
/*新建名为style4Lime的（普通）CellStyle，水平垂直居中*/
ICellStyle style4Lime = book.CreateCellStyle();
style4Lime.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style4Lime.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
style4Lime.FillPattern = FillPattern.SolidForeground;
style4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
style4Lime.BorderBottom = BorderStyle.Thin;
style4Lime.BorderTop = BorderStyle.Thin;
style4Lime.BorderLeft = BorderStyle.Thin;
style4Lime.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleDate的（日期用）CellStyle，水平居中，日期格式化为yyyy-MM-dd HH:mm:ss*/
ICellStyle cellStyleDate = book.CreateCellStyle();
IDataFormat formatDate = book.CreateDataFormat();
cellStyleDate.DataFormat = formatDate.GetFormat("yyyy-MM-dd HH:mm:ss");
cellStyleDate.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleDate.BorderBottom = BorderStyle.Thin;
cellStyleDate.BorderTop = BorderStyle.Thin;
cellStyleDate.BorderLeft = BorderStyle.Thin;
cellStyleDate.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleSmallDate的（日期用）CellStyle，水平居中，日期格式化为yyyy-MM-dd*/
ICellStyle cellStyleSmallDate = book.CreateCellStyle();
IDataFormat formatSmallDate = book.CreateDataFormat();
cellStyleSmallDate.DataFormat = formatSmallDate.GetFormat("yyyy-MM-dd");
cellStyleSmallDate.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleSmallDate.BorderBottom = BorderStyle.Thin;
cellStyleSmallDate.BorderTop = BorderStyle.Thin;
cellStyleSmallDate.BorderLeft = BorderStyle.Thin;
cellStyleSmallDate.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleNumber的（千位分割数字）CellStyle，水平居中，整数化*/
ICellStyle cellStyleNumber = book.CreateCellStyle();
IDataFormat formatNumber = book.CreateDataFormat();
cellStyleNumber.DataFormat = formatNumber.GetFormat("#,##0");
cellStyleNumber.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleNumber.BorderBottom = BorderStyle.Thin;
cellStyleNumber.BorderTop = BorderStyle.Thin;
cellStyleNumber.BorderLeft = BorderStyle.Thin;
cellStyleNumber.BorderRight = BorderStyle.Thin;
/*cellStyleCurrency（千位分割货币）CellStyle，水平居中，整数化*/
ICellStyle cellStyleCurrency = book.CreateCellStyle();
IDataFormat formatCurrency = book.CreateDataFormat();
cellStyleCurrency.DataFormat = formatCurrency.GetFormat("#,##0.00");
cellStyleCurrency.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleCurrency.BorderBottom = BorderStyle.Thin;
cellStyleCurrency.BorderTop = BorderStyle.Thin;
cellStyleCurrency.BorderLeft = BorderStyle.Thin;
cellStyleCurrency.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleDouble的（折扣用）CellStyle，水平居中，整数化*/
ICellStyle cellStyleDouble = book.CreateCellStyle();
IDataFormat formatDoubel = book.CreateDataFormat();
cellStyleDouble.DataFormat = formatDoubel.GetFormat("0.00");
cellStyleDouble.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleDouble.BorderBottom = BorderStyle.Thin;
cellStyleDouble.BorderTop = BorderStyle.Thin;
cellStyleDouble.BorderLeft = BorderStyle.Thin;
cellStyleDouble.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleNumber4LemonChiffon的（千位分割数字用）CellStyle，颜色LemonChiffon，水平剧中*/
ICellStyle cellStyleNumber4LemonChiffon = book.CreateCellStyle();
IDataFormat formatNumber4LemonChiffon = book.CreateDataFormat();
cellStyleNumber4LemonChiffon.DataFormat = formatNumber4LemonChiffon.GetFormat("#,##0");
cellStyleNumber4LemonChiffon.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleNumber4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
cellStyleNumber4LemonChiffon.FillPattern = FillPattern.SolidForeground;
cellStyleNumber4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
cellStyleNumber4LemonChiffon.BorderBottom = BorderStyle.Thin;
cellStyleNumber4LemonChiffon.BorderTop = BorderStyle.Thin;
cellStyleNumber4LemonChiffon.BorderLeft = BorderStyle.Thin;
cellStyleNumber4LemonChiffon.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleNumber4CornflowerBlue的（千位分割数字用）CellStyle，颜色CornflowerBlue，水平剧中*/
ICellStyle cellStyleNumber4CornflowerBlue = book.CreateCellStyle();
IDataFormat formatNumber4CornflowerBlue = book.CreateDataFormat();
cellStyleNumber4CornflowerBlue.DataFormat = formatNumber4CornflowerBlue.GetFormat("#,##0");
cellStyleNumber4CornflowerBlue.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleNumber4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyleNumber4CornflowerBlue.FillPattern = FillPattern.SolidForeground;
cellStyleNumber4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyleNumber4CornflowerBlue.BorderBottom = BorderStyle.Thin;
cellStyleNumber4CornflowerBlue.BorderTop = BorderStyle.Thin;
cellStyleNumber4CornflowerBlue.BorderLeft = BorderStyle.Thin;
cellStyleNumber4CornflowerBlue.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleNumber4LightGreen的（千位分割数字用）CellStyle，颜色LightGreen，水平剧中*/
ICellStyle cellStyleNumber4LightGreen = book.CreateCellStyle();
IDataFormat formatNumber4LightGreen = book.CreateDataFormat();
cellStyleNumber4LightGreen.DataFormat = formatNumber4LightGreen.GetFormat("#,##0");
cellStyleNumber4LightGreen.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleNumber4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
cellStyleNumber4LightGreen.FillPattern = FillPattern.SolidForeground;
cellStyleNumber4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
cellStyleNumber4LightGreen.BorderBottom = BorderStyle.Thin;
cellStyleNumber4LightGreen.BorderTop = BorderStyle.Thin;
cellStyleNumber4LightGreen.BorderLeft = BorderStyle.Thin;
cellStyleNumber4LightGreen.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleNumber4Lime的（千位分割数字用）CellStyle，颜色Lime，水平剧中*/
ICellStyle cellStyleNumber4Lime = book.CreateCellStyle();
IDataFormat formatNumber4Lime = book.CreateDataFormat();
cellStyleNumber4Lime.DataFormat = formatNumber4Lime.GetFormat("#,##0");
cellStyleNumber4Lime.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleNumber4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
cellStyleNumber4Lime.FillPattern = FillPattern.SolidForeground;
cellStyleNumber4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
cellStyleNumber4Lime.BorderBottom = BorderStyle.Thin;
cellStyleNumber4Lime.BorderTop = BorderStyle.Thin;
cellStyleNumber4Lime.BorderLeft = BorderStyle.Thin;
cellStyleNumber4Lime.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleCurrency4LemonChiffon的（千位分割数字用）CellStyle，颜色LemonChiffon，水平剧中*/
ICellStyle cellStyleCurrency4LemonChiffon = book.CreateCellStyle();
IDataFormat formatCurrency4LemonChiffon = book.CreateDataFormat();
cellStyleCurrency4LemonChiffon.DataFormat = formatCurrency4LemonChiffon.GetFormat("#,##0.00");
cellStyleCurrency4LemonChiffon.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleCurrency4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
cellStyleCurrency4LemonChiffon.FillPattern = FillPattern.SolidForeground;
cellStyleCurrency4LemonChiffon.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
cellStyleCurrency4LemonChiffon.BorderBottom = BorderStyle.Thin;
cellStyleCurrency4LemonChiffon.BorderTop = BorderStyle.Thin;
cellStyleCurrency4LemonChiffon.BorderLeft = BorderStyle.Thin;
cellStyleCurrency4LemonChiffon.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleCurrency4CornflowerBlue的（千位分割数字用）CellStyle，颜色CornflowerBlue，水平剧中*/
ICellStyle cellStyleCurrency4CornflowerBlue = book.CreateCellStyle();
IDataFormat formatCurrency4CornflowerBlue = book.CreateDataFormat();
cellStyleCurrency4CornflowerBlue.DataFormat = formatCurrency4CornflowerBlue.GetFormat("#,##0.00");
cellStyleCurrency4CornflowerBlue.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleCurrency4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyleCurrency4CornflowerBlue.FillPattern = FillPattern.SolidForeground;
cellStyleCurrency4CornflowerBlue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyleCurrency4CornflowerBlue.BorderBottom = BorderStyle.Thin;
cellStyleCurrency4CornflowerBlue.BorderTop = BorderStyle.Thin;
cellStyleCurrency4CornflowerBlue.BorderLeft = BorderStyle.Thin;
cellStyleCurrency4CornflowerBlue.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleCurrency4LightGreen的（千位分割数字用）CellStyle，颜色LightGreen，水平剧中*/
ICellStyle cellStyleCurrency4LightGreen = book.CreateCellStyle();
IDataFormat formatCurrency4LightGreen = book.CreateDataFormat();
cellStyleCurrency4LightGreen.DataFormat = formatCurrency4LightGreen.GetFormat("#,##0.00");
cellStyleCurrency4LightGreen.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleCurrency4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
cellStyleCurrency4LightGreen.FillPattern = FillPattern.SolidForeground;
cellStyleCurrency4LightGreen.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
cellStyleCurrency4LightGreen.BorderBottom = BorderStyle.Thin;
cellStyleCurrency4LightGreen.BorderTop = BorderStyle.Thin;
cellStyleCurrency4LightGreen.BorderLeft = BorderStyle.Thin;
cellStyleCurrency4LightGreen.BorderRight = BorderStyle.Thin;
/*新建名为cellStyleCurrency4Lime的（千位分割数字用）CellStyle，颜色Lime，水平剧中*/
ICellStyle cellStyleCurrency4Lime = book.CreateCellStyle();
IDataFormat formatCurrency4Lime = book.CreateDataFormat();
cellStyleCurrency4Lime.DataFormat = formatCurrency4Lime.GetFormat("#,##0.00");
cellStyleCurrency4Lime.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyleCurrency4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
cellStyleCurrency4Lime.FillPattern = FillPattern.SolidForeground;
cellStyleCurrency4Lime.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
cellStyleCurrency4Lime.BorderBottom = BorderStyle.Thin;
cellStyleCurrency4Lime.BorderTop = BorderStyle.Thin;
cellStyleCurrency4Lime.BorderLeft = BorderStyle.Thin;
cellStyleCurrency4Lime.BorderRight = BorderStyle.Thin;
}
/// <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);
}
}
/// <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();
}
}
}
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);
}
}
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 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());
}
}
}
