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
}
public void SaveAsXls(string xlsFilepath)
{
HSSFWorkbook workbook = new HSSFWorkbook();
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Hever Translations";
workbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Statistics - auto generated file";
workbook.SummaryInformation = si;
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("statistics");
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Text");
headerRow.CreateCell(1).SetCellValue("Words in Text");
headerRow.CreateCell(2).SetCellValue("Instances of Text");
//HSSFFont headerFont = workbook.CreateFont();
NPOI.SS.UserModel.IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headerRow.Cells[0].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[0].CellStyle.SetFont(headerFont);
headerRow.Cells[1].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[1].CellStyle.SetFont(headerFont);
headerRow.Cells[2].CellStyle = workbook.CreateCellStyle();
headerRow.Cells[2].CellStyle.SetFont(headerFont);
int totalWordsInText = 0;
int totalInstances = 0;
int rowNumber = 0;
foreach (KeyValuePair <string, int> kvp in m_elementsCount)
{
string text = kvp.Key;
int count = kvp.Value;
int wordsCount = m_elementsWordCount[kvp.Key];
totalWordsInText += wordsCount;
totalInstances += count;
rowNumber++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumber);
row.CreateCell(0).SetCellValue(text);
row.CreateCell(1).SetCellValue(wordsCount);
row.CreateCell(2).SetCellValue(count);
}
rowNumber++;
rowNumber++;
sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total words: " + totalWordsInText.ToString());
rowNumber++;
sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total instances: " + totalInstances.ToString());
sheet.CreateFreezePane(0, 1, 0, 1);
FileStream file = new FileStream(xlsFilepath, FileMode.Create);
workbook.Write(file);
file.Close();
}
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="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void DOWNLOADEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
string strName = string.Empty;
if (P1 == "KHGL")
{
strName = "CRM_客户_导入模板.xls";
}
else if (P1 == "KHLXR")
{
strName = "CRM_客户联系人_导入模板.xls";
}
else if (P1 == "HTGL")
{
strName = "CRM_合同_导入模板.xls";
}
object curContext = object.Current;
string headrow = context.Request["headrow"] ?? "0";//头部开始行下标
string path = curContext.Server.MapPath(@"/ViewV5/base/" + strName);
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
string suffix = path.Substring(path.LastIndexOf(".") + 1).ToLower();
IWorkbook workbook = null;
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet = workbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(int.Parse(headrow));
IRow oneRow = sheet.GetRow(int.Parse(headrow) + 1);
int icolIndex = headerRow.Cells.Count;
DataTable dtExtColumn = new JH_Auth_ExtendModeB().GetExtColumnAll(UserInfo.QYinfo.ComId, P1);
foreach (DataRow drExt in dtExtColumn.Rows)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(drExt["TableFiledName"].ToString());
cell.CellStyle = headerRow.Cells[icolIndex - 1].CellStyle;
ICell onecell = oneRow.CreateCell(icolIndex);
onecell.SetCellValue("");
onecell.CellStyle = oneRow.Cells[icolIndex - 1].CellStyle;
icolIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
if (P1 == "KHGL")
{
//表头样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
headerfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
for (int i = 10; i < 15; i++)
{
string strZTName = string.Empty;
if (i == 10)
{
strZTName = "客户类型";
}
if (i == 11)
{
strZTName = "跟进状态";
}
if (i == 12)
{
strZTName = "客户来源";
}
if (i == 13)
{
strZTName = "所属行业";
}
if (i == 14)
{
strZTName = "人员规模";
}
ISheet sheet1 = workbook.CreateSheet(strZTName);
IRow headerRow1 = sheet1.CreateRow(0);
ICell cell1 = headerRow1.CreateCell(0);
cell1.SetCellValue(strZTName);
cell1.CellStyle = HeadercellStyle;
int rowindex1 = 1;
foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i))
{
IRow DataRow = sheet1.CreateRow(rowindex1);
ICell cell = DataRow.CreateCell(0);
cell.SetCellValue(l.TypeName);
cell.CellStyle = cellStyle;
rowindex1++;
}
sheet1.AutoSizeColumn(0);
}
}
if (P1 == "HTGL")
{
//表头样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
headerfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
for (int i = 16; i < 18; i++)
{
string strZTName = string.Empty;
if (i == 16)
{
strZTName = "合同类型";
}
if (i == 17)
{
strZTName = "付款方式";
}
ISheet sheet1 = workbook.CreateSheet(strZTName);
IRow headerRow1 = sheet1.CreateRow(0);
ICell cell1 = headerRow1.CreateCell(0);
cell1.SetCellValue(strZTName);
cell1.CellStyle = HeadercellStyle;
int rowindex1 = 1;
foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i))
{
IRow DataRow = sheet1.CreateRow(rowindex1);
ICell cell = DataRow.CreateCell(0);
cell.SetCellValue(l.TypeName);
cell.CellStyle = cellStyle;
rowindex1++;
}
sheet1.AutoSizeColumn(0);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//object curContext = object.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strName, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
workbook = null;
ms.Close();
ms.Dispose();
}
}
catch
{
msg.ErrorMsg = "下载失败！";
}
}
/// <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]);
}
/// <summary>
/// Applies a font to the specified Chars of a string.
/// </summary>
/// <param name="startIndex">The start index to apply the font to (inclusive).</param>
/// <param name="endIndex"> The end index to apply to font to (exclusive).</param>
/// <param name="font">The index of the font to use.</param>
public void ApplyFont(int startIndex, int endIndex, NPOI.SS.UserModel.IFont font)
{
ApplyFont(startIndex, endIndex, font.Index);
}
public void GenerateExcelReport(string exportFileName)
{
DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource();
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report");
// Date format
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Numeric format
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String left format
NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String center format
NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 0 style
NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 10, 11 style
NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 4 style
NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 8 style
NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column8Style.DataFormat = format.GetFormat("yyyy-MM-dd");
column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Align right
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Bottom border
NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
// Bold style
NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont();
boldFont.Boldweight = 700;
boldStyle.SetFont(boldFont);
// Header Border
NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerBorderStyle.SetFont(boldFont);
headerBorderStyle.WrapText = true;
headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Left Border
NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerLeftBorderStyle.SetFont(boldFont);
headerLeftBorderStyle.WrapText = true;
headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Right Border
NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.SetFont(boldFont);
headerRightBorderStyle.WrapText = true;
headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Total numeric format
NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
// Grey color
NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
grey25Style.SetFont(boldFont);
// Yellow color
NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
yellowStyle.SetFont(boldFont);
// Set column width
worksheet.SetColumnWidth(0, 12 * 256);
worksheet.SetColumnWidth(1, 20 * 256);
worksheet.SetColumnWidth(2, 15 * 256);
worksheet.SetColumnWidth(3, 15 * 256);
worksheet.SetColumnWidth(4, 15 * 256);
worksheet.SetColumnWidth(5, 7 * 256);
worksheet.SetColumnWidth(6, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 15 * 256);
worksheet.SetColumnWidth(11, 25 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("KTPF Contribution Report");
headerCell.CellStyle = boldStyle;
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
if (dataSet.ORSOPlan.Rows.Count > 0)
{
Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0];
headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName);
}
headerCell.CellStyle = boldStyle;
string m_reportPeriod = "";
if (_payPeriodFr.Month != _payPeriodTo.Month)
{
m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy");
}
else
{
m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy");
}
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue(m_reportPeriod);
headerCell.CellStyle = boldStyle;
// Merge header
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3);
// Merge cell from 5-8
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("For New Joiner");
headerCell.CellStyle = yellowStyle;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
// Merge cell from 9-10
cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("For Resigned Staff");
headerCell.CellStyle = grey25Style;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4);
headerRow.HeightInPoints = 40;
// column A
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("Member ID");
headerCell.CellStyle = headerLeftBorderStyle;
// column B
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1);
headerCell.SetCellValue("Employee Name");
headerCell.CellStyle = headerBorderStyle;
// column C
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2);
headerCell.SetCellValue("Basic Salary");
headerCell.CellStyle = headerBorderStyle;
// column D
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3);
headerCell.SetCellValue("KTPF Contribution");
headerCell.CellStyle = headerBorderStyle;
// column E
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4);
headerCell.SetCellValue("Employer MPF Contribution");
headerCell.CellStyle = headerRightBorderStyle;
// column F
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("Sex");
headerCell.CellStyle = headerBorderStyle;
// column G
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6);
headerCell.SetCellValue("Date Of Birth");
headerCell.CellStyle = headerBorderStyle;
// column H
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7);
headerCell.SetCellValue("Date Join");
headerCell.CellStyle = headerBorderStyle;
// column I
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8);
headerCell.SetCellValue("Effective Date");
headerCell.CellStyle = headerRightBorderStyle;
// column J
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("Termination Date");
headerCell.CellStyle = headerBorderStyle;
// column K
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10);
headerCell.SetCellValue("Termination Mode");
headerCell.CellStyle = headerRightBorderStyle;
// column L
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11);
headerCell.SetCellValue("Remarks");
headerCell.CellStyle = headerRightBorderStyle;
// Create total
int length = dataSet.ExistingMember.Rows.Count + 5;
NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length);
NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1);
totalCell.SetCellValue("Total");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2);
totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3);
totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4);
totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
int rowLength = 0;
// Set value for every row
dataSet.ExistingMember.DefaultView.Sort = "EmpName";
DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable();
foreach (DataRow m_row in m_table.Rows)
//foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5);
rowLength++;
if (lastRowIndex == (m_table.Rows.Count))
{
detailRow.RowStyle = bottomBorderStyle;
}
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0);
cell.SetCellValue(m_row["MemberID"].ToString());
cell.CellStyle = column0Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1);
cell.SetCellValue(m_row["EmpName"].ToString());
cell.CellStyle = stringLeftStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2);
cell.SetCellValue((double)m_row["RelevantIncome"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3);
cell.SetCellValue((double)m_row["ER"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4);
cell.SetCellValue((double)m_row["MpfMCER"]);
cell.CellStyle = column4Style;
DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"];
DateTime m_effDate = (DateTime)m_row["OrsoEffDate"];
{
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5);
cell.CellStyle = stringCenterStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
cell.SetCellValue(m_row["EmpSex"].ToString());
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDOB"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDateJoin"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.CellStyle = column8Style;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["OrsoEffDate"]);
}
catch { }
}
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.CellStyle = dateCellStyle;
cell.SetCellValue("");// put something into the cell and so as the border line can be shown
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10);
cell.SetCellValue(m_row["TermCode"].ToString());
cell.CellStyle = column10To11Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
//cell.SetCellValue(row[FIELD_REMARKS].ToString());
cell.CellStyle = column10To11Style;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
private static int PreencherLinhaTotalizacaoGeral(List <ColunasGridKendo> colunasGrid, NPOI.SS.UserModel.ISheet sheet, ref int indiceLinha, ref List <KeyValuePair <string, object> > listaAggregates)
{
NPOI.SS.UserModel.IFont font = sheet.Workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var rowTotal = sheet.CreateRow(indiceLinha++);
if (colunasGrid.Count > 0 && colunasGrid[0].field == null)
{
colunasGrid.RemoveAt(0);
}
foreach (var coluna in colunasGrid)
{
//Lista com os aggregates da coluna no grid
List <KeyValuePair <string, object> > listaValoresColuna =
listaAggregates
.Where(it => it.Key == coluna.field)
.ToList();
decimal valorTotalColuna = 0;
if (listaValoresColuna.Count > 0)
{
string tipoAggregate = ((Dictionary <string, object>)listaValoresColuna.FirstOrDefault().Value).FirstOrDefault().Key.ToString();
//Lista de todos os valores das linhas do grid para a coluna atual
List <decimal> listaTotal = listaValoresColuna
.Select(it => Convert.ToDecimal(((Dictionary <string, object>)it.Value).FirstOrDefault().Value))
.ToList();
switch (tipoAggregate)
{
case "Sum":
valorTotalColuna = listaTotal.Sum();
break;
case "Average":
valorTotalColuna = listaTotal.Average();
break;
case "Min":
valorTotalColuna = listaTotal.Min();
break;
case "Max":
valorTotalColuna = listaTotal.Max();
break;
case "Count":
valorTotalColuna = listaTotal.Count();
break;
default:
valorTotalColuna = 0;
break;
}
int indiceColuna = colunasGrid.IndexOf(colunasGrid.Where(it => it.field == coluna.field).FirstOrDefault());
var celula = rowTotal.CreateCell(indiceColuna);
if (valorTotalColuna.GetType() == typeof(decimal))
{
valorTotalColuna = System.Math.Round((decimal)valorTotalColuna, 2);
}
celula.SetCellValue(System.Convert.ToDouble(valorTotalColuna));
celula.CellStyle = sheet.Workbook.CreateCellStyle();
celula.CellStyle.SetFont(font);
}
}
return(indiceLinha);
}
private static void MontarAgrupamentos(List <ColunasGridKendo> colunasGrid, NPOI.SS.UserModel.ISheet sheet, ref int indiceLinha, IEnumerable grupos, ref List <KeyValuePair <string, object> > listaAggregates)
{
NPOI.SS.UserModel.IFont font = sheet.Workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
foreach (Kendo.Mvc.Infrastructure.AggregateFunctionsGroup grupo in grupos)
{
//Titulo do Grupo
var row = sheet.CreateRow(indiceLinha++);
var valorChaveGrupo = grupo.Member + ": " + (grupo.Key == null ? String.Empty : grupo.Key).ToString();
row.CreateCell(0).SetCellValue(valorChaveGrupo.ToString());
//Marca o título do grupo com negrito
row.Cells[0].CellStyle = sheet.Workbook.CreateCellStyle();
row.Cells[0].CellStyle.SetFont(font);
if (colunasGrid.Count > 0 && colunasGrid[0].field == null)
{
colunasGrid.RemoveAt(0);
}
if (grupo.HasSubgroups)
{
//Mapeia os subgrupos recursivamente
MontarAgrupamentos(colunasGrid, sheet, ref indiceLinha, grupo.Items, ref listaAggregates);
}
else
{
PreencherDadosPlanilha(colunasGrid, grupo.Items, sheet, ref indiceLinha);
//Se há linha de totalização do grupo
if (grupo.Aggregates.Count > 0)
{
row = sheet.CreateRow(indiceLinha++);
listaAggregates.AddRange(grupo.Aggregates);
foreach (var total in grupo.Aggregates)
{
var valor = ((Dictionary <string, object>)total.Value).FirstOrDefault().Value;
if (valor.GetType() == typeof(decimal))
{
valor = System.Math.Round((decimal)valor, 2);
}
int indiceColuna = colunasGrid.IndexOf(colunasGrid.Where(it => it.field == total.Key).FirstOrDefault());
var celula = row.CreateCell(indiceColuna);
celula.SetCellValue(Convert.ToDouble(valor));
celula.CellStyle = sheet.Workbook.CreateCellStyle();
celula.CellStyle.SetFont(font);
}
row = sheet.CreateRow(indiceLinha++);
}
}
}
return;
}
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());
}
}
}
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);
}
/// <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);
}
/// <summary>
/// 导出模板excel
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void EXPORTTOEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
List <JH_Auth_ExtendDataB.IMPORTYZ> yz = new List <JH_Auth_ExtendDataB.IMPORTYZ>();
yz = new JH_Auth_ExtendDataB().GetTable(P1, UserInfo.QYinfo.ComId);//获取字段
if (yz.Count > 0)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (var l in yz)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(l.Name);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
string strDataJson = new JH_Auth_ExtendDataB().GetExcelData(P1);
if (strDataJson != "")
{
string[] strs = strDataJson.Split(',');
//建立内容行
int iCellIndex = 0;
IRow DataRow = sheet.CreateRow(1);
for (int i = 0; i < strs.Length; i++)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(strs[i]);
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
object curContext = object.Current;
string strName = string.Empty;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("CRM_" + strName + "_模板文件.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
workbook = null;
ms.Close();
ms.Dispose();
}
}
}
catch
{
msg.ErrorMsg = "导入失败！";
}
}
/// <summary>
/// Sets the font of the entire string.
/// </summary>
/// <param name="font">The font to use.</param>
public void ApplyFont(NPOI.SS.UserModel.IFont font)
{
ApplyFont(0, _string.CharCount, font);
}
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());
}
}
}
