public static NPOI.SS.UserModel.ICellStyle GetCellStyle(this NPOI.SS.UserModel.IWorkbook workbook, short backColorIndex, short fontColorIndex, System.Drawing.Font font, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General, NPOI.SS.UserModel.VerticalAlignment verticalAlignment = NPOI.SS.UserModel.VerticalAlignment.None, NPOI.SS.UserModel.BorderStyle borderLeft = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderTop = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderRight = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderBottom = NPOI.SS.UserModel.BorderStyle.None)
{
NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle();
if (backColorIndex >= 8 && backColorIndex <= 63)
{
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.FillForegroundColor = backColorIndex;
}
cellStyle.Alignment = horizontalAlignment;
cellStyle.VerticalAlignment = verticalAlignment;
cellStyle.BorderLeft = borderLeft;
cellStyle.BorderTop = borderTop;
cellStyle.BorderRight = borderRight;
cellStyle.BorderBottom = borderBottom;
if (font != null)
{
NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont();
if (fontColorIndex >= 8 && fontColorIndex <= 63)
{
cellFont.Color = fontColorIndex;
}
cellFont.FontName = font.Name;
cellFont.FontHeight = font.Size;
cellFont.Boldweight = (short)(font.Bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : NPOI.SS.UserModel.FontBoldWeight.Normal);
cellStyle.SetFont(cellFont);
}
return(cellStyle);
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
/// <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
}
static void CopyStyleFromCell(NPOI.SS.UserModel.ICellStyle To, NPOI.SS.UserModel.ICell Cell)
{
To.Alignment = Cell.CellStyle.Alignment;
To.BorderBottom = Cell.CellStyle.BorderBottom;
To.BorderLeft = Cell.CellStyle.BorderLeft;
To.BorderRight = Cell.CellStyle.BorderRight;
To.BorderTop = Cell.CellStyle.BorderTop;
To.BottomBorderColor = Cell.CellStyle.BottomBorderColor;
To.FillBackgroundColor = Cell.CellStyle.FillBackgroundColor;
To.FillForegroundColor = Cell.CellStyle.FillForegroundColor;
To.FillPattern = Cell.CellStyle.FillPattern;
To.SetFont(Cell.CellStyle.GetFont(Cell.Row.Sheet.Workbook));
To.IsHidden = Cell.CellStyle.IsHidden;
To.IsLocked = Cell.CellStyle.IsLocked;
To.LeftBorderColor = Cell.CellStyle.LeftBorderColor;
To.RightBorderColor = Cell.CellStyle.RightBorderColor;
To.Rotation = Cell.CellStyle.Rotation;
To.ShrinkToFit = Cell.CellStyle.ShrinkToFit;
To.TopBorderColor = Cell.CellStyle.TopBorderColor;
To.VerticalAlignment = Cell.CellStyle.VerticalAlignment;
To.WrapText = Cell.CellStyle.WrapText;
To.DataFormat = Cell.CellStyle.DataFormat;
}
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName)
{
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName);
List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>();
foreach (DataGridViewColumn i in dataGridView1.Columns)
{
if (i.Visible == true)
{
ListColumns.Add(i);
}
}
if (dataGridView1.Rows.Count <= 0)
{
return(false);
}
foreach (DataGridViewColumn dc in ListColumns)
{
if (dc.Visible == false)
{
continue;
}
if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double))
{
c.SetColumnWidth(dc.Index, 10 * 256);
}
else
{
c.SetColumnWidth(dc.Index, 20 * 256);
}
}
#region 表头
NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0);
var FirstCell = RowHeader.CreateCell(0);
FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName);
NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle();
cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont();
CellFontHeader.FontName = "微软雅黑";
CellFontHeader.FontHeightInPoints = 16;
cellstyleHeader.SetFont(CellFontHeader);
cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
FirstCell.CellStyle = cellstyleHeader;
c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1));
#endregion
#region 标题行 居中并且有框线
NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle();
CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1);
int cindex = 0;
foreach (DataGridViewColumn hc in ListColumns)
{
if (!hc.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex);
cindex++;
cell.CellStyle = CellStyleTitles;
if (!string.IsNullOrEmpty(hc.HeaderText))
{
cell.SetCellValue(hc.HeaderText);
}
}
#endregion
#region 列表 有框线,默认左对齐
NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle();
CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
foreach (DataGridViewRow i in dataGridView1.Rows)
{
NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2);
cindex = 0;
foreach (DataGridViewCell col in i.Cells)
{
if (!col.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex);
cindex++;
//设置居中对齐，如果是string则左对齐
if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid))
{
xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格
}
else
{
xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格，与标题栏一致
}
if (col.Value == null)
{
continue;
}
if (col.ValueType == typeof(string))
{
xcell.SetCellValue(col.Value.ToString());
}
else
if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double))
{
xcell.SetCellValue(double.Parse(col.Value.ToString()));
}
else
{
xcell.SetCellValue(col.Value.ToString());
}
}
}
#endregion
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel电子表格|*.xls";
sfd.FileName = tableName + DateTime.Now.Ticks.ToString();
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName))
{
wb.Write(fs);
MessageBox.Show("导出成功！");
}
}
catch (System.IO.IOException ex)
{
MessageBox.Show("导出失败！\n" + ex.Message);
return(false);
}
}
}
return(true);
}
public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy)
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Plan 1");
int rowNumer = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumer);
NPOI.SS.UserModel.ICell cell;
NPOI.SS.UserModel.IFont hFont = workbook.CreateFont();
hFont.FontHeightInPoints = 12;
hFont.FontName = "Arial";
NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle();
styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleHeader.SetFont(hFont);
NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle();
styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleDisabled.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleDisabled.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var listColumns = new List <ExportColumnInfo>();
listColumns.Add(new ExportColumnInfo()
{
Name = groupBy.GetDescription()
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Quantidade"
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Preço Médio"
});
for (int i = 0; i < listColumns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(listColumns[i].Name);
cell.CellStyle = styleHeader;
}
//---- row
foreach (var item in list)
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType);
row.CreateCell(1).SetCellValue(item.Quantity);
row.CreateCell(2).SetCellValue((double)item.AveragePrice);
}
for (int i = 0; i < listColumns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
byte[] byteArray;
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
workbook.Write(stream);
byteArray = stream.ToArray();
}
return(byteArray);
}
/// <summary>
/// 获取EXCEL数组
/// </summary>
/// <param name="dt"></param>
public static byte[] GetExcelFileByte(DataSet ds)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//设置样式
NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle();
NPOI.SS.UserModel.IFont font1 = book.CreateFont();
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font1.IsBold = true;
style1.SetFont(font1);
NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
byte[] fileByte = null;
for (int k = 0; k < ds.Tables.Count; k++)
{
try
{
DataTable dt = ds.Tables[k];
if (null != dt && dt.Rows.Count > 0)
{
if (dt.TableName == null || dt.TableName == "")
{
dt.TableName = "Sheet1";
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
sheet.DefaultColumnWidth = 20;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row.Cells[i].CellStyle = style1;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
row2.Cells[j].CellStyle = style2;
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
try
{
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
fileByte = ms.GetBuffer();
//using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
}
}
catch (Exception)
{
throw;
}
finally
{
if (null != book)
{
book.Close();
book = null;
}
}
return(fileByte);
}
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());
}
}
}
/// <summary>
/// 根据用户设置的Style来决定是否要新建ExcelStyle
/// </summary>
/// <param name="style"></param>
/// <returns></returns>
private NPOI.SS.UserModel.ICellStyle GetExcelStyle(Style style)
{
int index = this.style.FindIndex(s =>
s.BackColor == style.BackColor && s.Border == style.Border &&
s.Font.FontFamily.Name == style.Font.FontFamily.Name &&
s.Font.Size == style.Font.Size && s.Font.Bold == style.Font.Bold &&
s.ForeColor == style.ForeColor && s.TextAlign == style.TextAlign);
if (index < 0)
{
this.style.Add(style.DeepClone());
NPOI.SS.UserModel.ICellStyle cellStyle = workBook.CreateCellStyle();
excelStyle.Add(excelStyle.Count, cellStyle);
//背景色
cellStyle.FillForegroundColor = (short)style.BackColor;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
//文本色
NPOI.SS.UserModel.IFont fontStyle = workBook.CreateFont();
fontStyle.Color = (short)style.ForeColor;
cellStyle.SetFont(fontStyle);
//字体
fontStyle.FontName = style.Font.FontFamily.Name;
fontStyle.FontHeightInPoints = (short)style.Font.Size;
fontStyle.IsBold = style.Font.Bold;
//fontStyle.
//文本位置
switch (style.TextAlign)
{
case System.Drawing.ContentAlignment.TopCenter:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
break;
case System.Drawing.ContentAlignment.TopLeft:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
break;
case System.Drawing.ContentAlignment.TopRight:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
break;
case System.Drawing.ContentAlignment.BottomCenter:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom;
break;
case System.Drawing.ContentAlignment.BottomLeft:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom;
break;
case System.Drawing.ContentAlignment.BottomRight:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom;
break;
case System.Drawing.ContentAlignment.MiddleCenter:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
break;
case System.Drawing.ContentAlignment.MiddleLeft:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
break;
case System.Drawing.ContentAlignment.MiddleRight:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
break;
}
//边框
if (style.Border)
{
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
}
//((NPOI.HSSF.UserModel.HSSFSheet)table).SetEnclosedBorderOfRegion(cra,
// style.Border ? NPOI.SS.UserModel.BorderStyle.Thin : NPOI.SS.UserModel.BorderStyle.None, (short)Color.Black);
return(cellStyle);
}
return(excelStyle[index]);
}
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());
}
}
}
