private static void OutputDateRow(ISheet sheet, IEnumerable<string> distinctDates, int rowIndex)
{
var colSpan = 3;
var startCol = 1;
var dateRow = sheet.CreateRow(rowIndex);
var headerRow = sheet.CreateRow(rowIndex + 1);
dateRow.CreateCell(0).SetCellValue("");
headerRow.CreateCell(0).SetCellValue("");
for (var i = 0; i < distinctDates.Count(); i++)
{
var cell = dateRow.CreateCell(startCol);
cell.SetCellValue(distinctDates.ElementAt(i));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, colSpan * (i + 1)));
headerRow.CreateCell(startCol).SetCellValue(ValueHeader);
headerRow.CreateCell(startCol + 1).SetCellValue(PrefixHeader);
headerRow.CreateCell(startCol + 2).SetCellValue(DlHeader);
startCol = startCol + colSpan;
}
}
internal static void columnMerge(ISheet sheet, ICellStyle cellStyle, int row, int startColumn, int endColumn)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
}
public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
{
if (sourceRowIndex == targetRowIndex)
{
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
}
// Get the source / new row
IRow newRow = sheet.GetRow(targetRowIndex);
IRow sourceRow = sheet.GetRow(sourceRowIndex);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
}
newRow = sheet.CreateRow(targetRowIndex);
newRow.Height = sourceRow.Height; //copy row height
// Loop through source columns to add to new row
for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
continue;
}
ICell newCell = newRow.CreateCell(i);
if (oldCell.CellStyle != null)
{
// apply style from old cell to new cell
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress != null && cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow - cellRangeAddress.FirstRow
)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
sheet.AddMergedRegion(newCellRangeAddress);
}
}
return(newRow);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
int colint = 0;
for (int i = 0; i < dtSource.Columns.Count;)
{
DataColumn column = dtSource.Columns[i];
if (excelConfig.ColumnEntity[colint].Column != column.ColumnName)
{
dtSource.Columns.Remove(column.ColumnName);
}
else
{
i++;
colint++;
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "XXX"; //填加xls文件作者信息
si.ApplicationName = "卓软信息"; //填加xls文件创建程序信息
si.LastAuthor = "XXX"; //填加xls文件最后保存者信息
si.Comments = "XXX"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTimeHelper.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public static MemoryStream Export(List <DataTable> dtSource, string strHeaderText, List <string> sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
for (int sheetIndex = 0; sheetIndex < sheetName.Count; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet(sheetName[sheetIndex]);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource[sheetIndex].Columns.Count];
foreach (DataColumn item in dtSource[sheetIndex].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource[sheetIndex].Rows.Count; i++)
{
for (int j = 0; j < dtSource[sheetIndex].Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[sheetIndex].Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
if (dtSource[sheetIndex].Rows.Count == 0)
{
try
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
bool hasHeader = false;
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrWhiteSpace(strHeaderText))
{
hasHeader = true;
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sheetName[sheetIndex] + strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 500;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[sheetIndex].Columns.Count - 1));
//headerRow.Dispose();
//headerRow.d
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(hasHeader?1:0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource[sheetIndex].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = hasHeader?2:1;
}
#endregion
}
catch
{ }
}
else
{
foreach (DataRow row in dtSource[sheetIndex].Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
bool hasHeader = false;
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrWhiteSpace(strHeaderText))
{
hasHeader = true;
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sheetName[sheetIndex] + strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[sheetIndex].Columns.Count - 1));
//headerRow.Dispose();
//headerRow.d
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(hasHeader?1:0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource[sheetIndex].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = hasHeader?2:1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource[sheetIndex].Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
}
/// <summary>
/// 为Excel添加数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="gv"></param>
/// <param name="colTypeList">GridView每一列的数据类型</param>
/// <param name="colCount">GridView的总列数</param>
/// <param name="rowInex">添加Excel数据行的起始索引号</param>
/// <param name="cellStyle">表格基础格式</param>
private void AddSheetBody(ISheet sheet, GridView gv, ICellStyle cellStyle, List<int> colTypeList, int colCount, int rowInex)
{
IRow row;
ICell cell;
ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);
int rowCount = gv.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(rowInex);
for (int j = 0; j < colCount; j++)
{
if (gv.Rows[i].Cells[j].Visible == false) continue;
string cellText = gv.Rows[i].Cells[j].Text.Trim();
cellText = cellText.Replace(" ", "");//替换空字符占位符
cellText = cellText.Replace(">", ">");//替换 > 占位符
if (string.IsNullOrEmpty(cellText)) continue;//单元格为空跳过
cell = row.CreateCell(j);
if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
{
cell.SetCellValue(cellText);
cell.CellStyle = cellStyle;
}
else
{
try
{
switch (colTypeList[j])
{
case 1: cell.SetCellValue(int.Parse(cellText));//int类型
cell.CellStyle = cellStyle;
break;
case 2: cell.SetCellValue(double.Parse(cellText));//decimal数据类型
cell.CellStyle = cellStyleDecimal;
break;
case 3: cell.SetCellValue(DateTime.Parse(cellText));//日期类型
cell.CellStyle = cellStyleDateTime;
break;
default: cell.SetCellValue(cellText);
cell.CellStyle = cellStyle;
break;
}
}
catch
{
cell.SetCellValue("单元格导出失败");
MCSFramework.Common.LogWriter.FILE_PATH = GetAttachmentDirectory();
MCSFramework.Common.LogWriter.WriteLog("\r\n第j=" + j + "类发生错误，数据类型为" + colTypeList[j].ToString() + "，数值为" + cellText + "，报表GUID=" + Request.QueryString["Report"] != null ? Request.QueryString["Report"] : "无GUID值" + "\r\n");
}
}
int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan > 0 ? gv.Rows[i].Cells[j].ColumnSpan - 1 : 0;//跨列，即合并的列数
int MergeDown = gv.Rows[i].Cells[j].RowSpan > 0 ? gv.Rows[i].Cells[j].RowSpan - 1 : 0;//跨行，即合并的行数
if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
j += MergeAcross;
}
}
rowInex++;
}
}
private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
{
var rn0 = ws.PhysicalNumberOfRows;
var rn = rn0;
for (var i = 0; i < names.Length; ++i)
{
var rnc = rn++;
var r = ws.CreateRow(rnc);
if (1 < names[i].Length)
{
r.RowStyle = cs3;
var vh = ws.GetDataValidationHelper();
var vl = names[i].Skip(1).ToList();
vl.Sort();
var vd = vh.CreateValidation(
vh.CreateExplicitListConstraint(vl.ToArray()),
new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
);
vd.ShowErrorBox = false;
vd.ShowPromptBox = true;
ws.AddValidationData(vd);
}
var c = r.CreateCell(0);
c.SetCellValue(names[i][0]);
if (0 == i)
{
c.CellStyle = cs1;
r.RowStyle = cs1;
}
else
{
c.CellStyle = cs2;
}
}
if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
//ws.SetRowGroupCollapsed(rn0 + 1, true);
ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
}
private void MergeColum(List<StockModel> stockModels, ISheet sheet)
{
var preModel = stockModels[0];
int row_start = 2;
int row_end = 2;
for (int i = 1; i < stockModels.Count; i++)
{
if (preModel.Type == stockModels[i].Type)
{
row_end++;
}
else
{
if (row_end > row_start)
{
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
row_start = row_end;
}
row_end++;
row_start++;
}
preModel = stockModels[i];
}
if (row_end > row_start)
{
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
}
}
public FileResult ExportQuestionary(string QuestionaryID)
{
CellRangeAddress cellRange;
//创建Excel文件的对象
HSSFWorkbook xSSFWorkbook = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = xSSFWorkbook.CreateSheet("Sheet1");
//获取list数据
List <Question> questions = EFQuestionary.Questions.Where(q => q.QuestionaryID == QuestionaryID).OrderBy(q => q.QuestionID).ToList();
List <Select> selects = EFQuestionary.Selects.ToList();
List <FillOut> fillOuts = EFQuestionary.FillOuts.Where(f => f.QuestionaryID == QuestionaryID).OrderBy(f => f.GameID).ToList();
List <GameMember> gameMembers = EFGameMember.gameMembers.ToList();
//给sheet1添加第一行的头部标题
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("游戏ID");
row1.CreateCell(1).SetCellValue("游戏昵称");
int count = 2;
foreach (var item in questions)
{
row1.CreateCell(count).SetCellValue(item.QuestionContext);
cellRange = new CellRangeAddress(0, 0, count, count + selects.Where(s => s.QuestionID == item.QuestionID).Count() - 1);
sheet1.AddMergedRegion(cellRange);
count += selects.Where(s => s.QuestionID == item.QuestionID).Count();
}
//将数据逐步写入sheet1各个行
IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("选项值");
cellRange = new CellRangeAddress(1, 1, 0, 1);
sheet1.AddMergedRegion(cellRange);
count = 2;
foreach (var item in questions)
{
foreach (var items in selects.Where(s => s.QuestionID == item.QuestionID).OrderBy(s => s.SelectID))
{
if (items.Type)
{
row2.CreateCell(count++).SetCellValue("其他");
}
else
{
row2.CreateCell(count++).SetCellValue(items.Value);
}
}
}
count = 2;
int counts;
foreach (var GameID in fillOuts.Select(f => f.GameID).Distinct())
{
counts = 0;
IRow row = sheet1.CreateRow(count++);
row.CreateCell(counts++).SetCellValue(GameID);
row.CreateCell(counts++).SetCellValue(gameMembers.Where(g => g.GameID == GameID).FirstOrDefault().GameName.ToString());
foreach (var item in questions)
{
foreach (var items in selects.Where(s => s.QuestionID == item.QuestionID).OrderBy(s => s.SelectID))
{
FillOut fillOut = fillOuts.Where(f => f.GameID == GameID && f.SelectID == items.SelectID).FirstOrDefault();
if (fillOut != null)
{
if (items.Type)
{
row.CreateCell(counts++).SetCellValue(fillOut.Value);
}
else
{
row.CreateCell(counts++).SetCellValue("√");
}
}
else
{
row.CreateCell(counts++).SetCellValue("×");
}
}
}
}
// 写入到客户端
MemoryStream ms = new MemoryStream();
xSSFWorkbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "编号" + QuestionaryID + "调查问卷结果.xls"));
}
/// <summary>
/// 将错误信息写回原始存档中，并返回文件流。
/// </summary>
public static MemoryStream SetError(MDataTable dt, bool isReturnStream = true)
{
if (dt != null && dt.Columns.Contains("错误信息") && dt.DynamicData != null && dt.DynamicData is ISheet)
{
try
{
ISheet sheet = dt.DynamicData as ISheet;
IWorkbook workBook = sheet.Workbook;
ICellStyle style = GetStyle(workBook, HSSFColor.Red.Index);
int dataRowStart = dt.RecordsAffected;
IRow excelRow = null;
excelRow = sheet.GetRow(0);//处理列头的前面有垃圾数据导致列头比下面的列头少的情况
IRow dataRow = sheet.GetRow(dataRowStart - 1);
int firstRow = 0;
if (excelRow.LastCellNum < dataRow.LastCellNum)
{
excelRow = dataRow;
firstRow = dataRowStart - 1;
}
//int cellCount = excelRow.Cells.Count;
int cellCount;
if (!int.TryParse(dt.Conn, out cellCount))
{
cellCount = excelRow.LastCellNum; //修复左上角第一个单元格为空时，错误信息错位的问题
}
if (cellCount > 255) //如果出现256列（后面全是空格列）
{
cellCount = dt.Columns.Count;
}
ICell errorCell = excelRow.CreateCell(cellCount, CellType.String);
sheet.SetColumnWidth(cellCount, 100 * 256);
if (dataRowStart > 1)
{
//添加错误信息列
CellRangeAddress cellAddress = new CellRangeAddress(firstRow, dataRowStart - 1, cellCount, cellCount);
sheet.AddMergedRegion(cellAddress);
}
errorCell.SetCellValue("错误信息");
bool hasError = false;
int errColumnIndex = dt.Columns.GetIndex("错误信息");
for (int i = 0; i < dt.Rows.Count; i++)
{
MDataRow dtRow = dt.Rows[i];
string value = dtRow[errColumnIndex].ToString();
if (!string.IsNullOrEmpty(value))
{
IRow row = sheet.GetRow(i + dataRowStart);
if (row == null)
{
break;
}
hasError = true;
ICell cell = row.CreateCell(cellCount, CellType.String);
cell.SetCellValue(value);
cell.CellStyle = style;
for (int j = 0; j < dt.Columns.Count; j++) //标识错误的格的背景色为红色。
{
if (dtRow[j].State == -1) // && j < row.Cells.Count
{
cell = row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell != null)
{
cell.CellStyle = style;
}
}
}
}
}
if (hasError && isReturnStream)
{
MemoryStream ms = new MemoryStream();
workBook.Write(ms);
return(ms);
}
return(null);
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(null);
}
public static void columnMerge(ISheet sheet, int row, int startColumn, int endColumn, bool center)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet"> 要合并单元格所在的sheet </param>
/// <param name="rowstart"> 开始行的索引 </param>
/// <param name="rowend"> 结束行的索引 </param>
/// <param name="colstart"> 开始列的索引 </param>
/// <param name="colend"> 结束列的索引 </param>
protected void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
// sheet.SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
}
/// <summary>
/// 为Excel添加表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRow">GridView的HeaderRow属性</param>
/// <param name="headerCellStyle">表头格式</param>
/// <param name="flagNewLine">转行标志</param>
/// <param name="colCount">Excel表列数</param>
/// <returns>Excel表格行数</returns>
private int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
{
//int
colCount = 0;//记录GridView列数
int rowInex = 0;//记录表头的行数
IRow row = sheet.CreateRow(0);
ICell cell;
int groupCount = 0;//记录分组数
int colIndex = 0;//记录列索引，并于结束表头遍历后记录总列数
for (int i = 0; i < headerRow.Cells.Count; i++)
{
if (rowInex != groupCount)//新增了标题行时重新创建
{
row = sheet.CreateRow(rowInex);
groupCount = rowInex;
}
#region 是否跳过当前单元格
for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
//当前单元格是处于合并区域内
if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex
&& a.FirstRow <= rowInex && a.LastRow >= rowInex)
{
colIndex++;
m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
}
}
#endregion
cell = row.CreateCell(colIndex);
cell.CellStyle = headerCellStyle;
TableCell tablecell = headerRow.Cells[i];
//跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
int colSpan = 0;
int rowSpan = 0;
#region 获取跨行跨列属性值
//跨列
if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
{
colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
colSpan--;
}
if (tablecell.ColumnSpan > 1)
{
colSpan = tablecell.ColumnSpan;
colSpan--;
}
//跨行
if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
{
rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
rowSpan--;
}
if (tablecell.RowSpan > 1)
{
rowSpan = tablecell.RowSpan;
rowSpan--;
}
#endregion
//添加excel合并区域
if (colSpan > 0 || rowSpan > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
colIndex += colSpan + 1;//重新设置列索引
}
else
{
colIndex++;
}
string strHeader = headerRow.Cells[i].Text;
if (strHeader.Contains(flagNewLine))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
{
rowInex++;
colCount = colIndex;
colIndex = 0;
strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
}
cell.SetCellValue(strHeader);
}
if (groupCount == 0)//只有一行标题时另外为colCount赋值
{
colCount = colIndex;
}
rowInex++;//表头结束后另起一行开始记录控件数据行索引
return rowInex;
}
private ISheet createSheet1(HSSFWorkbook workBook, string sheetName, ExcelModelAttribute ema)
{
ISheet sheet = workBook.CreateSheet(sheetName);
IRow RowHead = sheet.CreateRow(0);
//合并第一行单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ema.excelNoticeCols));
RowHead.CreateCell(0).SetCellValue(ema.excelNotice);
/*****单元格样式 start******/
var cellStyleHead = workBook.CreateCellStyle();
cellStyleHead.Alignment = HorizontalAlignment.Center; //居中显示
cellStyleHead.VerticalAlignment = VerticalAlignment.Top; //垂直居中
cellStyleHead.WrapText = true;
//高度
RowHead.HeightInPoints = 50; //2 * sheet.DefaultRowHeight / 10;
//字体设置,字体要调用CreateFont()
IFont fonthead = workBook.CreateFont();
fonthead.FontHeightInPoints = 10;
fonthead.Color = HSSFColor.OliveGreen.Red.Index;
cellStyleHead.SetFont(fonthead);
//这里调试出来的,样式一定要给到单元格才有效
RowHead.Cells[0].CellStyle = cellStyleHead;
/*****单元格样式 end******/
/*********添加表头 s********/
IRow RowBody = sheet.CreateRow(1);
for (int iColumnIndex = 0; iColumnIndex < ema.tableHeadList.Count(); iColumnIndex++)
{
RowBody.CreateCell(iColumnIndex).SetCellValue(ema.tableHeadList[iColumnIndex].ToString());
RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20;
//单元格样式
ICellStyle cellStyle = workBook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//是否换行
cellStyle.WrapText = true;
RowBody.Cells[iColumnIndex].CellStyle = cellStyle;
//字体大小
IFont cellfont = workBook.CreateFont();
cellfont.FontHeightInPoints = 14;
cellStyle.SetFont(cellfont);
sheet.SetColumnWidth(iColumnIndex, 20 * 256);
}
/*********添加表头 e********/
//设置下拉框
setSheet2(workBook, sheet, ema);
return(sheet);
}
/// <summary>
/// 根据传入数据新建sheet表单到指定workbook
/// </summary>
/// <param name="objectDatas"></param>
/// <param name="excelHeader"></param>
/// <param name="sheetName"></param>
/// <param name="regulars"></param>
/// <param name="workbook"></param>
private static void CreateExcelSheetByDatas <T>(List <T> objectDatas, string excelHeader, string sheetName, ref IWorkbook workbook, string xmlPath)
{
var regulars = GetExportRegulars(xmlPath);
// excel sheet表单
ISheet sheet = workbook.CreateSheet(sheetName);
// excel行数
int rows = 0;
#region 单元格 -表头格式
#region 表头字体
IFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = 12;
fontTitle.Boldweight = (short)FontBoldWeight.Bold;
#endregion
ICellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleTitle.SetFont(fontTitle);
styleTitle.VerticalAlignment = VerticalAlignment.Center;
//styleTitle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; //XSSFColor
//styleTitle.FillPattern = FillPattern.SolidForeground;
#endregion
#region 单元格 -表体格式
#region 表体字体
IFont fontMessage = workbook.CreateFont();
fontMessage.FontHeightInPoints = 10;
#endregion
ICellStyle styleMessage = workbook.CreateCellStyle();
styleMessage.Alignment = HorizontalAlignment.Center;
styleMessage.SetFont(fontMessage);
styleMessage.VerticalAlignment = VerticalAlignment.Center;
#endregion
if (!string.IsNullOrEmpty(excelHeader))//表头存在
{
// 创建表头并赋值 导出的对象列数为表头的合并列数
int firstRowCellCount = regulars.Count;//GetAttributeCount(objectDatas.First());
IRow headerRow = sheet.CreateRow(rows);
headerRow.HeightInPoints = 40;
var headerCell = headerRow.CreateCell(0);
headerCell.SetCellValue(excelHeader);
// 合并表头
var cellRangeAddress = new CellRangeAddress(rows, rows, 0, firstRowCellCount - 1);
sheet.AddMergedRegion(cellRangeAddress);
// 设置表头格式
headerCell.CellStyle = styleTitle;
rows++;
}
//生成表头(属性表头)
if (objectDatas.Any())
{
// excel列数
int cells = -1;
// 创建数据行
var firstRow = sheet.CreateRow(rows);
firstRow.HeightInPoints = 25;
var objectData = objectDatas.FirstOrDefault();
foreach (System.Reflection.PropertyInfo p in objectData.GetType().GetProperties())
{
var regular = regulars.Find(t => t.PropertyName == p.Name);
if (regular != null)
{
cells++;
//throw new Exception("导出excel时，出现未配置字段。表：" + objectData.GetType().Name + ",字段：" + p.Name);
var firstRowCell = firstRow.CreateCell(cells);
firstRowCell.SetCellValue(regular.ExportFieldName);
sheet.SetColumnWidth(cells, regular.ExportFieldName.Length * 256 * 4);
firstRowCell.CellStyle = styleMessage;
}
}
}
// 反射object对象，遍历字段
foreach (var objectData in objectDatas)
{
rows++;
// excel列数
int cells = -1;
// 创建数据行
var messageRow = sheet.CreateRow(rows);
messageRow.HeightInPoints = 16;
foreach (PropertyInfo p in objectData.GetType().GetProperties())
{
var regular = regulars.Find(t => t.PropertyName == p.Name);
if (regular != null)
{
cells++;
var messageCell = messageRow.CreateCell(cells);
var value = p.GetValue(objectData);
if (value == null)
{
messageCell.SetCellValue("");
}
else
{
switch (regular.DataType)
{
case "DateTime":
if (Convert.ToDateTime(value) == DateTime.MinValue)
{
messageCell.SetCellValue("");
}
else
{
messageCell.SetCellValue(
Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case "Int":
messageCell.SetCellValue(Convert.ToInt32(value));
break;
case "Double":
messageCell.SetCellValue(Convert.ToDouble(value));
break;
case "Decimal":
messageCell.SetCellValue(Convert.ToDouble(value));
break;
case "Bool":
var setValue = "是";
if (!(bool)value)
{
setValue = "否";
}
messageCell.SetCellValue(setValue);
break;
default:
messageCell.SetCellValue(value.ToString());
break;
}
}
messageCell.CellStyle = styleMessage;
}
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, DocumentSummaryInformation dsi, SummaryInformation si)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
if (dsi != null)
{
workbook.DocumentSummaryInformation = dsi;
}
if (si != null)
{
workbook.SummaryInformation = si;
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static bool ExportExcel(DataTable dt, string filePath, bool bMerge = false)
{
bool bRes = false;
try
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet(dt.TableName);
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].Caption);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
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]));
}
}
//列宽自适应，只对英文和数字有效
for (int i = 0; i <= dt.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = System.Text.Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
if (bMerge)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < dt.Columns.Count; i++)
{
int indexs = 0;
int indexe = 0;
string vtemp = dt.Rows[0][i].ToString();
for (int j = 0; j < dt.Rows.Count - 1; j++)
{
string temp = dt.Rows[j + 1][i].ToString();
if (temp != vtemp)
{
indexe = j + 1;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexs + 1, indexe, i, i));
ICell cell = sheet.GetRow(indexs + 1).GetCell(i);
if (cell != null)
{
cell.CellStyle = cellStyle;
}
indexs = j + 1;
vtemp = temp;
}
}
if (indexe < dt.Rows.Count)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexs + 1, dt.Rows.Count, i, i));
ICell cell = sheet.GetRow(indexs + 1).GetCell(i);
if (cell != null)
{
cell.CellStyle = cellStyle;
}
}
}
}
// 写入到客户端
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
bRes = true;
}
}
catch (Exception ex)
{
bRes = false;
}
return(bRes);
}
static void FillTheSoSheet(ISheet sheet,So so, List<SoItemsContentAndState> soitemList)
{
sheet.GetRow(0).CreateCell(2).SetCellValue(so.customerName);
sheet.GetRow(0).CreateCell(6).SetCellValue(so.contact);
sheet.GetRow(0).CreateCell(9).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.salesId));
if (so.approverId != null)
{
sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.approverId.Value) + "," + so.approveDate.Value.ToShortDateString());
}
sheet.GetRow(1).CreateCell(2).SetCellValue(so.salesOrderNo);
sheet.GetRow(1).CreateCell(6).SetCellValue(so.orderDate.ToShortDateString());
sheet.GetRow(1).CreateCell(9).SetCellValue(so.customerPo);
sheet.GetRow(1).CreateCell(14).SetCellValue(so.paymentTerm);
sheet.GetRow(2).CreateCell(2).SetCellValue(so.freightTerm);
sheet.GetRow(2).CreateCell(6).SetCellValue(so.customerAccount);
sheet.GetRow(2).CreateCell(9).SetCellValue(so.specialInstructions);
IRow row = sheet.CreateRow(4);
row.CreateCell(0).SetCellValue(so.billTo);
row.CreateCell(8).SetCellValue(so.shipTo);
int itemRowIndex = 9;
foreach (SoItemsContentAndState scs in soitemList)
{
IRow itemRow = sheet.CreateRow(itemRowIndex);
itemRow.CreateCell(0).SetCellValue(soitemList.IndexOf(scs)+1);
string strSaleType;
switch (scs.soitem.saleType)
{
case 0:
strSaleType = "OEM EXCESS";
break;
case 1:
strSaleType = "OWN STOCK";
break;
case 2:
strSaleType = "OTHERS";
break;
default:
strSaleType = "ERROR";
break;
}
itemRow.CreateCell(1).SetCellValue(strSaleType);
itemRow.CreateCell(2).SetCellValue(scs.soitem.partNo);
itemRow.CreateCell(3).SetCellValue(scs.soitem.mfg);
itemRow.CreateCell(4).SetCellValue(scs.soitem.rohs == 1 ? "Y" : "N");
itemRow.CreateCell(5).SetCellValue(scs.soitem.dc);
itemRow.CreateCell(6).SetCellValue(scs.soitem.intPartNo);
itemRow.CreateCell(7).SetCellValue(scs.soitem.shipFrom);
itemRow.CreateCell(8).SetCellValue(scs.soitem.shipMethod);
itemRow.CreateCell(9).SetCellValue(scs.soitem.trackingNo);
itemRow.CreateCell(10).SetCellValue(scs.soitem.qty);
if (scs.soitem.qtyshipped != null)
{
itemRow.CreateCell(11).SetCellValue(scs.soitem.qtyshipped.Value);
}
itemRow.CreateCell(12).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), scs.soitem.currencyType));
itemRow.CreateCell(13).SetCellValue(scs.soitem.unitPrice);
itemRow.CreateCell(14).SetCellValue(scs.soitem.unitPrice * scs.soitem.qty);
itemRow.CreateCell(15).SetCellValue(scs.soitem.dockDate.ToShortDateString());
if (scs.soitem.shippedDate != null)
{
itemRow.CreateCell(16).SetCellValue(scs.soitem.shippedDate.Value.ToShortDateString());
}
itemRowIndex++;
IRow infoRow=sheet.CreateRow(itemRowIndex);
infoRow.CreateCell(1).SetCellValue("Shipping Instructions >>" + scs.soitem.shippingInstruction);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));
itemRowIndex++;
infoRow=sheet.CreateRow(itemRowIndex);
infoRow.CreateCell(1).SetCellValue("Packing Instructions >>" + scs.soitem.packingInstruction);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));
itemRowIndex++;
}
}
private void Merge(int row, int column, int rowspan, int colspan)
{
_sheet.AddMergedRegion(new CellRangeAddress(row, row + rowspan - 1, column, column + colspan - 1));
}
private int MSWriteToSheet1(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
{
if (data == null || data.Count == 0)
return -1;
List<int> setRow = new List<int>();
setRow.Add(3);
setRow.Add(4);
setRow.Add(5);
setRow.Add(6);
setRow.Add(7);
ICellStyle fontBold = this.setFontBold();
ICellStyle cellBorder = this.setCellBorder();
foreach (var r in data)
{
IRow row = sheet.CreateRow(r.Key);
for (int col = 0; col < r.Value.Count(); col++)
{
ICell cell = row.CreateCell(col);
cell.SetCellValue(r.Value[col].CellValue);
////给加盟费等标签字体加粗
if (r.Value[col].IsFontBold)
{
cell.CellStyle = fontBold;
}
if (r.Value[col].HasCellBorder)
{
cell.CellStyle = cellBorder;
}
}
////合并单元格并设置边框
if (setRow.Contains(r.Key))
{
CellRangeAddress region1 = new CellRangeAddress(r.Key, r.Key, 1, 2);
CellRangeAddress region2 = new CellRangeAddress(r.Key, r.Key, 5, 6);
sheet.AddMergedRegion(region1);
sheet.AddMergedRegion(region2);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
}
}
//设置合并单元格的长度，需全部显示文字
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
return 0;
}
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//标题头
int icolIndex = 0;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
ICell celltitle = headerRow.CreateCell(0);
celltitle.SetCellValue(title);
celltitle.CellStyle = HeadercellStyle;
//用column name 作为列名
IRow headerRow1 = sheet.CreateRow(1);
foreach (DataColumn item in dt.Columns)
{
ICell cell1 = headerRow1.CreateCell(icolIndex);
cell1.SetCellValue(item.ColumnName);
cell1.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 2;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
workbook = null;
}
}
private void CopyRow(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
IRow destinationRow;
var getRows = destinationWorksheet.GetRow(destinationRowNum);
if (getRows == null)
{
destinationRow = destinationWorksheet.CreateRow(destinationRowNum);
}
else
{
destinationRow = getRows;
}
IRow sourceRow = sourceWorksheet.GetRow(sourceRowNum);
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
ICell newCell = destinationRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
ICellStyle newCellStyle = workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sourceWorksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sourceWorksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(destinationRow.RowNum,
destinationRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
destinationWorksheet.AddMergedRegion(newCellRangeAddress);
}
}
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//*
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataTable dT = (DataTable)Session["header_01"];
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
IRow row0 = sheet0.CreateRow(0);
#region tabela1
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
int rol = 3;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
#endregion tabela1
#region drugi arkusz
// druga tabela
view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
dT.Clear();
dT = (DataTable)Session["header_02"];
//===========
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
rol = 3;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
#endregion drugi arkusz
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
//table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="dt">源数据</param>
/// <param name="fields">字段名（与 names 列名顺序一致，含个数）</param>
/// <param name="names">列名（与 fields 字段名顺序一致，含个数）</param>
/// <param name="merger">合并列相同的值，用于上下行单元格重复合并（多个列间用半角逗号间隔）</param>
/// <param name="totalColumn">合计列的值，仅对数字、货币类型有效（在最后一行自动合计显示）</param>
/// <returns>返回生成后的URL下载地址</returns>
public string ToExcel(string fileName, DataTable dt, string[] fields, string[] names, int[] merger, int[] totalColumn = null, Dictionary <int, int> columnWidth = null)
{
if (dt != null && dt.Rows.Count > 0 && fields.Length == names.Length)
{
//创建Excel文件
string sheetName = fileName;
string headerText = fileName;
fileName = string.Format("{0}_{1}.xls", fileName, DateTime.Now.ToString("yyyy-MM-dd"));
HSSFWorkbook book = new HSSFWorkbook();
book.DocumentSummaryInformation = DSI;
book.SummaryInformation = SummaryInfo(fileName);
//创建表
ISheet iSheet = book.CreateSheet(sheetName);
//创建数据格式
IDataFormat iDataFormat = book.CreateDataFormat();
//设置日期格式
ICellStyle dataStyle = book.CreateCellStyle();
int namesCount = names.Length;
int fieldsCount = fields.Length;
#region //自适应列宽
int colCount = names.Length;
int[] colWidth = new int[colCount];
for (int i = 0; i < namesCount; i++)
{
//colWidth[i] = Encoding.GetEncoding(936).GetBytes(names[i]).Length;
//colWidth[i] = (colWidth[i] < 5) ? 5 : colWidth[i] + 2;
if (columnWidth != null && columnWidth.ContainsKey(i))
{
colWidth[i] = (columnWidth[i] < 5) ? 5 : columnWidth[i] + 2;
}
else
{
colWidth[i] = Encoding.GetEncoding(936).GetBytes(names[i]).Length;
colWidth[i] = (colWidth[i] < 5) ? 5 : colWidth[i] + 2;
}
}
DataRowCollection drc = dt.Rows;
int rowCount = drc.Count;
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
int tmp = Encoding.GetEncoding(936).GetBytes(drc[i][fields[j]].ToString()).Length;
if (tmp > colWidth[j])
{
colWidth[j] = tmp;
}
}
}
#endregion
IFont titleFont = book.CreateFont();
IFont headFont = book.CreateFont();
IFont textFont = book.CreateFont();
ICellStyle css = book.CreateCellStyle();
//
int cur = 0;
int tab = 1;
decimal dec = 0;
Dictionary <int, decimal> heji = new Dictionary <int, decimal>();
for (int index = 0; index < rowCount; index++)
{
#region 设置表头、列名、样式
if (index == 0 || index % this.MaxRows == 0)
{
if (cur != 0)
{
iSheet = book.CreateSheet(string.Format("{0}({1})", sheetName, tab));
tab++;
}
//表头
IRow header = iSheet.CreateRow(0);
header.HeightInPoints = 25;
//样式
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.BorderTop = BorderStyle.THIN;
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
titleFont.FontHeightInPoints = 18;
cellStyle.SetFont(titleFont);
header.CreateCell(0).SetCellValue(headerText);
header.GetCell(0).CellStyle = cellStyle;
CellRangeAddress ra = new CellRangeAddress(0, 0, 0, colCount - 1);
iSheet.AddMergedRegion(ra);
((HSSFSheet)iSheet).SetEnclosedBorderOfRegion(ra, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
//
//列头
header = iSheet.CreateRow(1);
header.HeightInPoints = 18;
cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.BorderTop = BorderStyle.THIN;
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
headFont.FontHeightInPoints = 12;
cellStyle.SetFont(headFont);
for (int i = 0; i < namesCount; i++)
{
header.CreateCell(i).SetCellValue(names[i]);
header.GetCell(i).CellStyle = cellStyle;
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));
}
cur = 2;
}
#endregion
#region 填充内容
IRow rows = iSheet.CreateRow(cur);
rows.HeightInPoints = 18;
for (int i = 0; i < fieldsCount; i++)
{
ICell cell = rows.CreateCell(i);
css.Alignment = HorizontalAlignment.LEFT;
css.VerticalAlignment = VerticalAlignment.CENTER;
css.WrapText = true;
css.BorderTop = BorderStyle.THIN;
css.BorderBottom = BorderStyle.THIN;
css.BorderLeft = BorderStyle.THIN;
css.BorderRight = BorderStyle.THIN;
textFont.FontHeightInPoints = 10;
css.SetFont(textFont);
cell.CellStyle = css;
if (index > 0 && IsExistMerger(merger, i) && (index % this.MaxRows != 0))
{
if (string.Compare(Convert.ToString(drc[index][fields[i]]), Convert.ToString(drc[index - 1][fields[i]])) == 0 &&
string.Compare(Convert.ToString(drc[index][fields[merger[0]]]), Convert.ToString(drc[index - 1][fields[merger[0]]])) == 0)
{
iSheet.AddMergedRegion(new CellRangeAddress(cur - 1, cur, i, i));
}
}
string val = Convert.ToString(drc[index][fields[i]]);
dec = 0;
this.SetCellValue(cell, iDataFormat, dataStyle, drc[index][fields[i]].GetType().ToString(), val, out dec);
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));
if (IsExistMerger(totalColumn, i))
{
if (!heji.ContainsKey(i))
{
heji.Add(i, dec);
}
else
{
dec += heji[i];
heji.Remove(i);
heji.Add(i, dec);
}
}
}
#endregion
cur++;
}
#region 对指定列进行合计
int beginColumn = 0;
if (totalColumn != null && totalColumn.Length > 0)
{
IRow rows = iSheet.CreateRow(cur);
rows.HeightInPoints = 18;
for (int i = 0; i < fieldsCount; i++)
{
ICell cell = rows.CreateCell(i);
css.Alignment = HorizontalAlignment.LEFT;
css.VerticalAlignment = VerticalAlignment.CENTER;
css.WrapText = true;
css.BorderTop = BorderStyle.THIN;
css.BorderBottom = BorderStyle.THIN;
css.BorderLeft = BorderStyle.THIN;
css.BorderRight = BorderStyle.THIN;
textFont.FontHeightInPoints = 10;
css.SetFont(textFont);
cell.CellStyle = css;
dec = 0;
if (IsExistMerger(totalColumn, i))
{
this.SetCellValue(cell, iDataFormat, dataStyle, heji[i].GetType().ToString(), heji[i].ToString(), out dec);
if (beginColumn == 0)
{
beginColumn = i;
}
}
else if (beginColumn > 0)
{
cell = rows.GetCell(0);
css.Alignment = HorizontalAlignment.RIGHT;
css.SetFont(textFont);
cell.CellStyle = css;
iSheet.AddMergedRegion(new CellRangeAddress(cur, cur, 0, beginColumn - 1));
this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "合计：", out dec);
beginColumn = -1;
}
else
{
this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "", out dec);
}
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 2) * 256)));
}
}
#endregion
//输出文件
return(this.OutputFile(book, fileName));
}
return(string.Empty);
}
/// <summary>
/// 操作已经生成的Excel
/// </summary>
/// <param name="fullPath"></param>
/// <param name="ivm"></param>
/// <returns></returns>
public static bool ExcelDraw(string fullPath, QueryDataInputVM ivm)
{
//需要绘制的记录
var needDraw = "DatabaseTableDesign,syslog".ToLower().Split(',');
if (!needDraw.Contains(ivm.tableName?.ToLower()))
{
return(true);
}
string strExtName = Path.GetExtension(fullPath);
IWorkbook workbook = null;
using (FileStream file = new FileStream(fullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
if (strExtName.Equals(".xls"))
{
workbook = new HSSFWorkbook(file);
}
if (strExtName.Equals(".xlsx"))
{
workbook = new XSSFWorkbook(file);
}
}
ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
switch (ivm.tableName?.ToLower())
{
//数据库表设计
case "databasetabledesign":
{
//冻结首行首列
sheet.CreateFreezePane(0, 1);
var rows = sheet.GetRowEnumerator();
var styleH = CreateCellStyle(workbook, StyleType.headLeft);
while (rows.MoveNext())
{
IRow row;
if (fullPath.Contains(".xlsx"))
{
row = (XSSFRow)rows.Current;
}
else
{
row = (HSSFRow)rows.Current;
}
var cc = row.GetCell(1);
if (string.IsNullOrWhiteSpace(cc.StringCellValue))
{
foreach (var cell in row.Cells)
{
cell.CellStyle = styleH;
}
//合并
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, row.Cells.Count - 1));
}
}
}
break;
default:
break;
}
using (FileStream file = new FileStream(fullPath, FileMode.OpenOrCreate))
{
workbook.Write(file);
workbook.Close();
}
return(false);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "";
//workbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = ""; //填加xls文件作者信息
//si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
//si.LastAuthor = ""; //填加xls文件最后保存者信息
//si.Comments = "说明信息"; //填加xls文件作者信息
//si.Title = "NPOI测试"; //填加xls文件标题信息
//si.Subject = "NPOI测试Demo";//填加文件主题信息
//si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
//长度大于255，后面省略
if (intTemp > 254)
{
int lh = dtSource.Rows[i][j].ToString().Length;
dtSource.Rows[i][j] = dtSource.Rows[i][j].ToString().Substring(0, lh - 4) + "…";
intTemp = 254;
}
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
bool hasHeader = false;
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrWhiteSpace(strHeaderText))
{
hasHeader = true;
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(hasHeader?1:0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = hasHeader?2:1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
//DateTime dateV;
//DateTime.TryParse(drValue, out dateV);
//newCell.SetCellValue(dateV);
//newCell.CellStyle = dateStyle;//格式化显示
newCell.SetCellValue(drValue);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
long intV = 0;
long.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
//row 0-4
protected static int SetBorangAHeader(IWorkbook workbook, ISheet sheet1, int bulan, int tahun)
{
var associativeArray = new Dictionary <int?, string>()
{
{ 1, "JANUARI" }, { 2, "FEBRUARI" }, { 3, "MAC" }, { 4, "APRIL" }, { 5, "MEI" }, { 6, "JUN" }, { 7, "JULAI" }, { 8, "OGOS" }, { 9, "SEPTEMBER" }, { 10, "OKTOBER" }, { 11, "NOVEMBER" }, { 12, "DISEMBER" }
};
string bulanString = "";
foreach (var m in associativeArray)
{
if (bulan == m.Key)
{
bulanString = m.Value;
}
}
string bulanTahunString = string.Format("{0} {1}", bulanString, tahun);
DateTime tarikhAkhir = new DateTime(tahun, bulan, 1);
tarikhAkhir = tarikhAkhir.AddMonths(1);
tarikhAkhir = tarikhAkhir.AddDays(-1);
ICellStyle alignCenter = AlignCenter(workbook);
int colWidth = 6;
for (int row = 0; row <= 4; row++)
{
IRow nRow = sheet1.CreateRow(row);
ICell nCell = nRow.CreateCell(0);
nCell.CellStyle = alignCenter;
switch (row)
{
case (0):
nCell.SetCellValue("PERTUBUHAN KESELEMATAN SOSIAL");
ICell cellBorang = nRow.CreateCell(colWidth);
cellBorang.SetCellValue("BORANG");
break;
case (1):
nCell.SetCellValue("JADUAL CARUMAN BULANAN");
ICell cell8a = nRow.CreateCell(colWidth);
cell8a.SetCellValue("8A");
break;
case (2):
nCell.SetCellValue("UNTUK CARUMAN BULAN " + bulanTahunString);
break;
case (3):
nCell.SetCellValue("JUMLAH CARUMAN UNTUK BULAN DI ATAS HENDAKLAH DIBAYAR");
break;
case (4):
nCell.SetCellValue("TIDAK LEWAT DARIPADA " + string.Format("{0:dd/MM/yyyy}", tarikhAkhir));
ICell cellLembaran = nRow.CreateCell(colWidth);
cellLembaran.SetCellValue("LEMBARAN: 1");
break;
default:
break;
}
//merge all column
var cra0 = new CellRangeAddress(row, row, 0, colWidth - 1);
sheet1.AddMergedRegion(cra0);
}
return(4);
}
public override void OnRenderMain(FlatProject project, ParsedLog log)
{
int rowPos = LG_ROW_POS_CONTENT_START;
foreach (ParsedLogLine line in log.LogLineList)
{
int colPos = LG_COL_POS_CONTENT_START;
if (project.ShowFileName)
{
ICell fileCell = GetLogContentStringCell(colPos, rowPos);
fileCell.SetCellValue(line.File.Name);
SetCellWidthToDictionary(LG_COL_NAME_FILE, line.File.Name);
colPos++;
}
ICell lineNumCell = GetLogContentCenterCell(colPos, rowPos);
lineNumCell.SetCellValue(line.LineNumber);
SetCellWidthToDictionary(LG_COL_NAME_LINE, line.LineNumber.ToString());
colPos++;
if (line.HasError)
{
ICell notParsedCell = GetLogContentStringCell(colPos, rowPos);
notParsedCell.SetCellValue(line.NotParsedLog);
if (line.ColumnList.Count > 1)
{
int cellRange = colPos;
foreach (ParsedLogColumn col in line.ColumnList)
{
if (col.ColumnDefinition.Visble)
{
cellRange++;
}
}
CellRangeAddress region = new CellRangeAddress(rowPos, rowPos, colPos, cellRange - 1);
logSheet.AddMergedRegion(region);
RegionUtil.SetBorderTop(BORDER_THIN, region, logSheet, workbook);
RegionUtil.SetBorderBottom(BORDER_THIN, region, logSheet, workbook);
RegionUtil.SetBorderRight(BORDER_THIN, region, logSheet, workbook);
RegionUtil.SetBorderLeft(BORDER_THIN, region, logSheet, workbook);
}
}
else
{
foreach (ParsedLogColumn col in line.ColumnList)
{
if (!col.ColumnDefinition.Visble)
{
continue;
}
ICell cell = null;
if (col.ColumnDefinition.IsDateTimeField)
{
DateTime time = new DateTime(long.Parse(col.Value));
cell = GetLogContentDateCell(colPos, rowPos);
cell.SetCellValue(time);
SetCellWidthToDictionary(col.ColumnDefinition.ColumnName, time.ToString(FORMAT_STR_DATE));
}
else
{
cell = GetLogContentStringCell(colPos, rowPos);
cell.SetCellValue(col.Value);
SetCellWidthToDictionary(col.ColumnDefinition.ColumnName, col.Value);
}
colPos++;
}
}
rowPos++;
}
ResizeLogColumns(project, project.PatternDefinition.ColumnDefinitionList);
}
//合并单元格
public static void MergedCell(ISheet curSheet, int BeginRowIndex, int EndRowIndex, int BeginColumnIndex, int EndColumnIndex)
{
curSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(BeginRowIndex, EndRowIndex, BeginColumnIndex, EndColumnIndex));
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
var workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
var headerRow = sheet.CreateRow(1);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
var maxCellWidth = 255 * 100;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
var colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth > maxCellWidth)
{
colWidth = maxCellWidth;
}
sheet.SetColumnWidth(column.Ordinal, colWidth);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
private void ExportDataItem(DataTable dt)
{
DataRow dr = dt.Rows[0];
string filename = "培训实施记录--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训实施记录.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
IRow row = sheet0.CreateRow(i + 2);
row.HeightInPoints = 20;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_BM"].ToString());
row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BH"].ToString());
row.CreateCell(3).SetCellValue(dt.Rows[i]["SPLX"].ToString() == "NDPXJH" ? "年度" : "临时");
row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部");
row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString());
row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJ"].ToString() == "1" ? "第一季度" : dt.Rows[i]["PX_SJ"].ToString() == "2" ? "第二季度" : dt.Rows[i]["PX_SJ"].ToString() == "3" ? "第三季度" : "第四季度");
row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_DD"].ToString());
row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString());
row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_DX"].ToString());
row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_RS"].ToString());
row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_XS"].ToString());
row.CreateCell(12).SetCellValue(dt.Rows[i]["PX_FYYS"].ToString());
row.CreateCell(13).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString());
row.CreateCell(14).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString());
row.CreateCell(15).SetCellValue(dt.Rows[i]["PX_SJRY"].ToString());
row.CreateCell(16).SetCellValue(dt.Rows[i]["PX_SJRS"].ToString());
row.CreateCell(17).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString());
row.CreateCell(18).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString());
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 18; j++)
{
row.Cells[j].CellStyle = cells;
}
}
double num = 0;
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
num += CommonFun.ComTryDouble(dt.Rows[i]["PX_SJXS"].ToString());
}
IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 2);
for (int i = 0; i <= 18; i++)
{
rowhz.CreateCell(i);
}
rowhz.GetCell(0).SetCellValue("人数合计");
rowhz.GetCell(17).SetCellValue(num);
rowhz.HeightInPoints = 20;
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i <= 18; i++)
{
rowhz.Cells[i].CellStyle = cells2;
}
CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16);
sheet0.AddMergedRegion(range1);
//CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17);
//sheet0.AddMergedRegion(range2);
//IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6);
//for (int i = 0; i <= 17; i++)
//{
// rowqz1.CreateCell(i);
//}
//rowqz1.GetCell(0).SetCellValue("编制负责人：");
//rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString());
//rowqz1.GetCell(5).SetCellValue("申报单位/部门主管：");
//rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString());
//rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : "");
//rowqz1.GetCell(12).SetCellValue("集团公司主管领导：");
//rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString());
//rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : "");
//for (int i = 0; i <= 17; i++)
//{
// rowqz1.Cells[i].CellStyle = cells2;
//}
//CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1);
//sheet0.AddMergedRegion(range3);
////CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4);
////sheet0.AddMergedRegion(range4);
//CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6);
//sheet0.AddMergedRegion(range5);
////CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11);
////sheet0.AddMergedRegion(range6);
//CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13);
//sheet0.AddMergedRegion(range7);
//IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7);
//for (int i = 0; i <= 17; i++)
//{
// rowqz2.CreateCell(i);
//}
//rowqz2.GetCell(1).SetCellValue("日期：");
//rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString());
//rowqz2.GetCell(6).SetCellValue("日期：");
//rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString());
//rowqz2.GetCell(13).SetCellValue("日期：");
//rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString());
//for (int i = 0; i <= 17; i++)
//{
// rowqz2.Cells[i].CellStyle = cells2;
//}
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (oldColumnNames.Length != newColumnNames.Length)
{
return(new MemoryStream());
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
* foreach (DataColumn item in dtSource.Columns)
* {
* arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
* }
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
* for (int j = 0; j < dtSource.Columns.Count; j++)
* {
* int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
* if (intTemp > arrColWidth[j])
* {
* arrColWidth[j] = intTemp;
* }
* }
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
* foreach (DataColumn column in dtSource.Columns)
* {
* headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
* headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
*
* //设置列宽
* sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
* }
* */
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void excel()
{
IWorkbook workbook = new HSSFWorkbook(); //döküman
ISheet sheet1 = workbook.CreateSheet("product 12"); //sayfa
List <V_sold_product> pm = db.sold_product_Read().ToList();
var style = workbook.CreateCellStyle(); //sitil dosyası
style.FillForegroundColor = HSSFColor.Blue.Index2; //renk
style.Alignment = HorizontalAlignment.Center; // metin hizalama
style.VerticalAlignment = VerticalAlignment.Center; //metin hzalama
style.FillPattern = FillPattern.LessDots; // renk doldurma
sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 8)); //birleştirme
var rowIndex = 0;
var row = sheet1.CreateRow(rowIndex).CreateCell(0);
row.CellStyle = style;
row.SetCellValue("deneme son vol:3");
rowIndex += 2;
var row2 = sheet1.CreateRow(rowIndex);
row2.CreateCell(0).SetCellValue("SIRA NO"); //hücre değerleri
sheet1.AutoSizeColumn(0); // hücre veriye göre boyutlandırma
row2.CreateCell(1).SetCellValue("ARAC");
sheet1.AutoSizeColumn(1);
row2.CreateCell(2).SetCellValue("DOKUMAN NO");
sheet1.AutoSizeColumn(8);
row2.CreateCell(3).SetCellValue("BIRIM");
sheet1.AutoSizeColumn(2);
row2.CreateCell(4).SetCellValue("ODEME PLANI");
sheet1.AutoSizeColumn(3);
row2.CreateCell(5).SetCellValue("SATILAN URUN");
sheet1.AutoSizeColumn(4);
row2.CreateCell(6).SetCellValue("MALIYET");
sheet1.AutoSizeColumn(5);
row2.CreateCell(7).SetCellValue("TOPLAM");
sheet1.AutoSizeColumn(6);
row2.CreateCell(8).SetCellValue("EKLEME TARIHI");
sheet1.AutoSizeColumn(7);
rowIndex++;
foreach (var item in pm)
{
var row1 = sheet1.CreateRow(rowIndex);
row1.CreateCell(0).SetCellValue(item.SIRA_NO); //hücre değerleri
sheet1.AutoSizeColumn(0); // hücre veriye göre boyutlandırma
row1.CreateCell(1).SetCellValue(item.ARAC);
sheet1.AutoSizeColumn(1);
row1.CreateCell(2).SetCellValue(item.DOKUMAN_NO);
sheet1.AutoSizeColumn(8);
row1.CreateCell(3).SetCellValue(item.BIRIM);
sheet1.AutoSizeColumn(2);
row1.CreateCell(4).SetCellValue(item.ODEME_PLANI);
sheet1.AutoSizeColumn(3);
row1.CreateCell(5).SetCellValue(item.SATILAN_URUN.ToString());
sheet1.AutoSizeColumn(4);
row1.CreateCell(6).SetCellValue(item.MALIYET.ToString());
sheet1.AutoSizeColumn(5);
row1.CreateCell(7).SetCellValue(item.TOPLAM.ToString());
sheet1.AutoSizeColumn(6);
row1.CreateCell(8).SetCellValue(item.EKLEME_TARIHI.Value);
sheet1.AutoSizeColumn(7);
rowIndex++;
}
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; producttable.xls");
Response.AddHeader("content-length", stream.Length.ToString());
Response.BinaryWrite(stream.ToArray());
Response.End();
}
public HSSFWorkbook DataTableToExcel(DataTable dt, string sheetName, string headerText)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet(sheetName);
#region 设置文件属性信息
ArrayList tagRed = new ArrayList();
//创建一个摘要信息实体。
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Title = headerText;
si.Subject = headerText;
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
ICellStyle dateStyle = hssfworkbook.CreateCellStyle();
IDataFormat format = hssfworkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#endregion
#region 获取列名
List <string> columnNameArry = new List <string>();
DataTable dt_TemptColumn = BLL.TField.Instance.GetTemptColumnNameByTableName(ttCode);
int tempt_Count = dt_TemptColumn.Columns.Count;
int excelColumn_Count = 0;
for (int k = 0; k < tempt_Count; k++)
{
string column_Name = dt_TemptColumn.Columns[k].ToString();
string[] columns = column_Name.Split('_');
System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"Tempf.*");
bool m = reg.IsMatch(column_Name);
if (m)
{
string tfDesName = string.Empty;//字段名
//需要特殊处理的列
if (columns.Length >= 2)
{
//如果是 推荐活动字段列 则不生成EXCEL模板列
if (columns[1] == "Activity")
{
BLL.Loger.Log4Net.Info("DataTable正在生成，推荐活动字段不生成列名!");
continue;
}
string addDes = string.Empty;
//如果为Province且有columns有三个，则表示是省份名；
if (columns[1] == "Province" && columns.Length == 3)
{
addDes = "（省）";
}
//如果为City且有columns有三个，则表示是城市名；
else if (columns[1] == "City" && columns.Length == 3)
{
addDes = "（市）";
}
//如果为Country且有columns有三个，则表示是县名；
else if (columns[1] == "Country" && columns.Length == 3)
{
addDes = "（县）";
}
//如果为startdata或starttime，则表示是开始日期/时间段；
else if (columns[1] == "startdata" || columns[1] == "starttime")
{
addDes = "（起）";
}
//如果为enddata或endtime，则表示是结束日期/时间段；
else if (columns[1] == "enddata" || columns[1] == "endtime")
{
addDes = "（止）";
}
else if (columns[1] == "XDBrand" && columns.Length == 3)
{
addDes = "（品牌）";
}
else if (columns[1] == "XDSerial" && columns.Length == 3)
{
addDes = "（车型）";
}
else if (columns[1] == "YXBrand" && columns.Length == 3)
{
addDes = "（品牌）";
}
else if (columns[1] == "YXSerial" && columns.Length == 3)
{
addDes = "（车型）";
}
else if (columns[1] == "CSBrand" && columns.Length == 3)
{
addDes = "（品牌）";
}
else if (columns[1] == "CSSerial" && columns.Length == 3)
{
addDes = "（车型）";
}
//columns.Length == 3：是判断是前面几项 还包括radio、check、select
//addDes != string.Empty：如果是时间段/点columns.Length =2，所以加上这个条件
if (columns.Length == 3 || addDes != string.Empty)
{
DataTable dt_TField = BLL.TField.Instance.GetTFieldTableByTFName(columns[0]);
tfDesName = dt_TField.Rows[0]["TFDesName"].ToString() + addDes;
columnNameArry.Add(tfDesName);
//如果导入字段不能为空，则插入tagRed中
if (dt_TField.Rows[0]["TFInportIsNull"].ToString() == "0")
{
tagRed.Add(tfDesName);
}
excelColumn_Count++;
}
}
else
{
DataTable dt_TField = BLL.TField.Instance.GetTFieldTableByTFName(columns[0]);
tfDesName = dt_TField.Rows[0]["TFDesName"].ToString();
columnNameArry.Add(tfDesName);
//如果导入字段不能为空，则插入tagRed中
if (dt_TField.Rows[0]["TFInportIsNull"].ToString() == "0")
{
tagRed.Add(tfDesName);
}
excelColumn_Count++;
BLL.Loger.Log4Net.Info("DataTable正在生成，列名：" + tfDesName);
}
}
}
#endregion
#region 设置列的宽度
int[] colWidth = new int[columnNameArry.Count];
for (int i = 0; i < columnNameArry.Count; i++)
{
colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnNameArry[i]).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < columnNameArry.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > colWidth[j])
{
colWidth[j] = intTemp;
}
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
//if (!string.IsNullOrEmpty(headerText))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(headerText);
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = 400;
font.Color = HSSFColor.RED.index;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow;
//if (!string.IsNullOrEmpty(headerText))
//{
// headerRow = sheet.CreateRow(0);
//}
//else
//{
headerRow = sheet.CreateRow(1);
//}
for (int i = 0; i < columnNameArry.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(columnNameArry[i]);
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
if (tagRed.Contains(columnNameArry[i]))
{
font.Color = HSSFColor.RED.index;
headStyle.SetFont(font);
}
else
{
headStyle.SetFont(font);
}
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
if ((colWidth[i] + 1) * 256 > 30000)
{
sheet.SetColumnWidth(i, 10000);
}
else
{
sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);
}
}
/*
* foreach (DataColumn column in dtSource.Columns)
* {
* headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
* headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
*
* //设置列宽
* sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
* }
* */
}
#endregion
//if (!string.IsNullOrEmpty(headerText))
//{
// rowIndex = 1;
//}
//else
//{
rowIndex = 2;
//}
}
#endregion
rowIndex++;
}
return(hssfworkbook);
}
//protected void ExportToExcel()
//{
// Response.Clear();
// Response.Buffer = true;
// Response.AddHeader("content-disposition", "attachment;filename=รายงานคลัง Barcode.xls");
// Response.Charset = "";
// Response.ContentType = "application/vnd.ms-excel";
// using (StringWriter sw = new StringWriter())
// {
// HtmlTextWriter hw = new HtmlTextWriter(sw);
// //To Export all pages
// gridStockBarcode.AllowPaging = false;
// gridStockBarcode.DataBind();
// gridStockBarcode.HeaderRow.BackColor = Color.White;
// foreach (TableCell cell in gridStockBarcode.HeaderRow.Cells)
// {
// cell.BackColor = gridStockBarcode.HeaderStyle.BackColor;
// }
// foreach (GridViewRow row in gridStockBarcode.Rows)
// {
// row.BackColor = Color.White;
// foreach (TableCell cell in row.Cells)
// {
// if (row.RowIndex % 2 == 0)
// {
// cell.BackColor = gridStockBarcode.AlternatingRowStyle.BackColor;
// }
// else
// {
// cell.BackColor = gridStockBarcode.RowStyle.BackColor;
// }
// cell.CssClass = "textmode";
// }
// }
// gridStockBarcode.RenderControl(hw);
// //style to format numbers to string
// string style = @"<style> .textmode { } </style>";
// Response.Write(style);
// Response.Output.Write(sw.ToString());
// Response.Flush();
// Response.End();
// }
//}
public void ExportToExcel(String extension, DataTable dt)
{
// dll refered NPOI.dll and NPOI.OOXML
IWorkbook workbook;
if (extension == "xlsx")
{
workbook = new XSSFWorkbook();
}
else if (extension == "xls")
{
workbook = new HSSFWorkbook();
}
else
{
throw new Exception("This format is not supported");
}
ISheet sheet1 = workbook.CreateSheet("StockBarcode");
var headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
headerCellStyle.FillPattern = FillPattern.SolidForeground;
var firstRow = 0;
IRow rowTitle = sheet1.CreateRow(firstRow);
ICell cellTitle = rowTitle.CreateCell(0);
firstRow++;
var fontTitle = workbook.CreateFont();
fontTitle.FontName = "Calibri";
fontTitle.Boldweight = (short)FontBoldWeight.Bold;
fontTitle.FontHeightInPoints = 16;
var cellStyleTitle = workbook.CreateCellStyle();
cellStyleTitle.Alignment = HorizontalAlignment.Center;
cellTitle.CellStyle = cellStyleTitle;
cellTitle.CellStyle.SetFont(fontTitle);
cellTitle.SetCellValue("รายงานคลัง Barcode");
IRow rowDate = sheet1.CreateRow(firstRow);
ICell cellDate = rowDate.CreateCell(0);
firstRow++;
var fontDate = workbook.CreateFont();
fontDate.FontName = "Calibri";
fontDate.Boldweight = (short)FontBoldWeight.Bold;
fontDate.FontHeightInPoints = 12;
var cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.Alignment = HorizontalAlignment.Center;
cellDate.CellStyle = cellStyleDate;
cellDate.CellStyle.SetFont(fontDate);
var strDate = "ระหว่างวันที่ " + txtEditStart.Text.Trim() + " ถึง " + txtEditEnd.Text.Trim();
cellDate.SetCellValue(strDate);
IRow rowPrintDate = sheet1.CreateRow(firstRow);
ICell cellPrintDate = rowPrintDate.CreateCell(0);
firstRow++;
var cellStylePrintDate = workbook.CreateCellStyle();
cellStylePrintDate.Alignment = HorizontalAlignment.Right;
cellPrintDate.CellStyle = cellStylePrintDate;
cellPrintDate.CellStyle.SetFont(fontDate);
cellPrintDate.SetCellValue("PrintDate " + DateTime.Now.ToString("dd-MM-yyyy"));
var range1 = new CellRangeAddress(0, 0, 0, 6);
sheet1.AddMergedRegion(range1);
var range2 = new CellRangeAddress(1, 1, 0, 6);
sheet1.AddMergedRegion(range2);
var range3 = new CellRangeAddress(2, 2, 0, 6);
sheet1.AddMergedRegion(range3);
var listHeader = new[] { "Barcode", "PO No,", "แผนก/คลัง", "วันที่เริ่มต้น", "วันที่แก้ไขล่าสุด", "ผู้แก้ไขล่าสุด", "สถานะ" };
//make a header row
IRow row1 = sheet1.CreateRow(firstRow);
for (int j = 0; j < listHeader.Length; j++)
{
ICell cell = row1.CreateCell(j);
String columnName = listHeader[j];
cell.SetCellValue(columnName);
cell.CellStyle = headerCellStyle;
}
//loops through data
// j = 1 ไม่เอา no
firstRow++;
int r = 0;
for (int i = firstRow; r < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i);
DataRow dr = dt.Rows[r];
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(dr["Barcode"].ToString());
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(dr["PONo"].ToString());
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(dr["Department"].ToString());
ICell cell4 = row.CreateCell(3);
cell4.SetCellValue(dr["CreateDate"].ToString());
ICell cell5 = row.CreateCell(4);
cell5.SetCellValue(dr["LastEditDate"].ToString());
ICell cell6 = row.CreateCell(5);
cell6.SetCellValue(dr["LastEditBy"].ToString());
ICell cell7 = row.CreateCell(6);
cell7.SetCellValue(dr["Status"].ToString());
//for (int j = 1; j < dt.Columns.Count; j++)
//{
// ICell cell = row.CreateCell(j);
// String columnName = dt.Columns[j].ToString();
// cell.SetCellValue(dt.Rows[i][columnName].ToString());
// sheet1.AutoSizeColumn(j);
//}
r++;
}
for (int j = 0; j <= 6; j++)
{
sheet1.AutoSizeColumn(j);
}
using (var exportData = new MemoryStream())
{
Response.Clear();
workbook.Write(exportData);
if (extension == "xlsx") //xlsx file format
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "รายงานคลัง StockBarcode.xlsx"));
Response.BinaryWrite(exportData.ToArray());
}
else if (extension == "xls") //xls file format
{
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "รายงานคลัง StockBarcode.xls"));
Response.BinaryWrite(exportData.GetBuffer());
}
Response.End();
}
}
/// <summary>
/// 导出Excel文件,并合并指定单元格
/// </summary>
/// <param name="dt"></param>
/// <param name="path"></param>
/// <param name="param"></param>
public static void ExportExcel(DataTable dt, string path, List <MergeCellParam> param)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
IRow rowTitle = sheet1.CreateRow(0);
ICell cellTitle;
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int m = 0; m < dt.Columns.Count; m++)
{
cellTitle = rowTitle.CreateCell(m);
cellTitle.CellStyle = style;
cellTitle.SetCellValue(dt.Columns[m].ColumnName);
}
IRow row;
ICell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
string obj = dt.Rows[i][j].ToString();
cell.SetCellValue(obj);
}
}
}
if (param.Count > 0)
{
for (int i = 0; i < param.Count; i++)
{
sheet1.AddMergedRegion(new CellRangeAddress(param[i].FirstRow, param[i].LastRow, param[i].FirstColumn, param[i].LastColumn));
}
}
var file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
private static void CopyRow <T>(ISheet sheet, int templateRowIndex, List <T> dataList, Dictionary <int, string> propertyMapperDict, dynamic propertyGetDict) where T : class
{
if (dataList == null || dataList.Count == 0)
{
return;
}
IRow templateDataRow = sheet.GetRow(templateRowIndex);
if (templateDataRow == null)
{
return;
}
List <ExcelMergeCell> mergeCellList = new List <ExcelMergeCell>();
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == templateDataRow.RowNum)
{
mergeCellList.Add(new ExcelMergeCell()
{
BeginColumn = cellRangeAddress.FirstColumn, EndColumn = cellRangeAddress.LastColumn
});
}
}
int templateDataCellCount = templateDataRow.Cells.Count;
if (dataList.Count > 1)
{
sheet.ShiftRows(templateRowIndex, //开始行
sheet.LastRowNum, //结束行
dataList.Count - 1, //插入行总数
true, //是否复制行高
false //是否重置行高
);
}
IRow dataRow = null;
ICell templateCell = null;
ICell dataCell = null;
int dataCount = dataList.Count;
int dataLastIndex = templateRowIndex + dataCount;
for (int i = templateRowIndex, j = 0; i < dataLastIndex && j < dataCount; i++, j++)
{
dataRow = sheet.CreateRow(i);
dataRow.Height = templateDataRow.Height;//复制行高
for (int colIndex = templateDataRow.FirstCellNum; colIndex < templateDataRow.LastCellNum; colIndex++)
{
templateCell = templateDataRow.GetCell(colIndex);
if (templateCell == null)
{
continue;
}
dataCell = dataRow.CreateCell(colIndex);
if (dataCell == null)
{
dataCell = templateDataRow.CreateCell(colIndex);
}
dataCell.CellStyle = templateCell.CellStyle;//赋值单元格格式
string templateCellText = templateCell.ToString();
if (!templateCellText.StartsWith("&="))
{
dataCell.SetCellType(templateCell.CellType);
if (propertyMapperDict.ContainsKey(colIndex))
{
object propertyValue = null;
if (propertyGetDict != null && propertyGetDict.ContainsKey(propertyMapperDict[colIndex]))
{
propertyValue = propertyGetDict[propertyMapperDict[colIndex]](dataList[j]);
}
else
{
propertyValue = ReflectionHelper.GetPropertyValue(dataList[j], propertyMapperDict[colIndex]);
}
if (propertyValue != null)
{
Type propertyType = typeof(T).GetProperty(propertyMapperDict[colIndex]).PropertyType;
if (propertyType == typeof(int))
{
dataCell.SetCellValue(int.Parse(propertyValue.ToString()));
}
else if (propertyType == typeof(float) || propertyType == typeof(double))
{
dataCell.SetCellValue(double.Parse(propertyValue.ToString()));
}
else if (propertyType == typeof(DateTime))
{
dataCell.SetCellValue(DateTime.Parse(propertyValue.ToString()));
}
else
{
dataCell.SetCellValue(propertyValue.ToString());
}
}
}
}
else
{
templateCellText = templateCellText.TrimStart(new char[] { '&', '=' });
templateCellText = templateCellText.Replace("i", (dataRow.RowNum + 1).ToString());
dataCell.SetCellType(CellType.Formula);
dataCell.SetCellFormula(templateCellText);
}
}
if (i != dataLastIndex - 1 && mergeCellList != null && mergeCellList.Count > 0)
{
foreach (ExcelMergeCell excelMergeCell in mergeCellList)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(dataRow.RowNum, dataRow.RowNum, excelMergeCell.BeginColumn, excelMergeCell.EndColumn);
sheet.AddMergedRegion(cellRangeAddress);
}
}
}
}
static void FillThePoSheet(ISheet sheet,po po, List<PoItemContentAndState> poitemList)
{
sheet.GetRow(0).CreateCell(2).SetCellValue(po.vendorName);
sheet.GetRow(0).CreateCell(9).SetCellValue(po.contact);
sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById((int)po.pa));
sheet.GetRow(1).CreateCell(2).SetCellValue(po.vendorNumber);
if (po.poDate != null)
{
sheet.GetRow(1).CreateCell(5).SetCellValue(po.poDate.ToShortDateString());
}
sheet.GetRow(1).CreateCell(9).SetCellValue(po.poNo);
sheet.GetRow(1).CreateCell(14).SetCellValue(po.paymentTerms);
sheet.GetRow(2).CreateCell(2).SetCellValue(po.shipMethod);
sheet.GetRow(2).CreateCell(9).SetCellValue(po.freight);
sheet.GetRow(2).CreateCell(14).SetCellValue(po.shipToLocation);
IRow row = sheet.CreateRow(4);
row.CreateCell(0).SetCellValue(po.billTo);
row.CreateCell(10).SetCellValue(po.shipTo);
int itemRowIndex = 9; int totalQty = 0; float totalTotal = 0;int totalQtyRecd = 0;
foreach (PoItemContentAndState pcs in poitemList)
{
IRow itemRow = sheet.CreateRow(itemRowIndex);
itemRow.CreateCell(0).SetCellValue(poitemList.IndexOf(pcs)+1);
itemRow.CreateCell(1).SetCellValue(pcs.poItem.partNo);
itemRow.CreateCell(2).SetCellValue(pcs.poItem.mfg);
itemRow.CreateCell(3).SetCellValue(pcs.poItem.dc);
itemRow.CreateCell(4).SetCellValue(pcs.poItem.vendorIntPartNo);
itemRow.CreateCell(5).SetCellValue(pcs.poItem.coo);
itemRow.CreateCell(6).SetCellValue(pcs.poItem.qty);
totalQty += pcs.poItem.qty;
if (pcs.poItem.qtyRecd != null)
{
itemRow.CreateCell(7).SetCellValue(pcs.poItem.qtyRecd.Value);
totalQtyRecd += pcs.poItem.qtyRecd.Value;
}
if(pcs.poItem.qtyCorrected!=null)
itemRow.CreateCell(8).SetCellValue(pcs.poItem.qtyCorrected.Value);
if(pcs.poItem.qtyAccept!=null)
itemRow.CreateCell(9).SetCellValue(pcs.poItem.qtyAccept.Value);
if(pcs.poItem.qtyRejected!=null)
itemRow.CreateCell(10).SetCellValue(pcs.poItem.qtyRejected.Value);
if(pcs.poItem.qtyRTV!=null)
itemRow.CreateCell(11).SetCellValue(pcs.poItem.qtyRTV.Value);
if(pcs.poItem.qcPending!=null)
itemRow.CreateCell(12).SetCellValue(pcs.poItem.qcPending.Value);
itemRow.CreateCell(13).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), pcs.poItem.currency));
itemRow.CreateCell(14).SetCellValue(pcs.poItem.unitPrice.Value);
itemRow.CreateCell(15).SetCellValue(pcs.poItem.qty*pcs.poItem.unitPrice.Value);
totalTotal += pcs.poItem.qty* pcs.poItem.unitPrice.Value;
itemRow.CreateCell(16).SetCellValue(pcs.poItem.dockDate.ToShortDateString());
if(pcs.poItem.receiveDate!=null)
itemRow.CreateCell(17).SetCellValue(pcs.poItem.receiveDate.Value.ToShortDateString());
itemRow.CreateCell(18).SetCellValue(pcs.poItem.stepCode);
itemRow.CreateCell(19).SetCellValue("Unknown");
itemRowIndex++;
IRow infoRow = sheet.CreateRow(itemRowIndex);
infoRow.CreateCell(1).SetCellValue("Note To Vendor >>" + pcs.poItem.noteToVendor);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex, itemRowIndex, 1, 16));
itemRowIndex++;
}
//add total bar
IRow totalRow=sheet.CreateRow(itemRowIndex);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,0,4));
totalRow.CreateCell(5).SetCellValue("Total");
totalRow.CreateCell(6).SetCellValue(totalQty);
totalRow.CreateCell(7).SetCellValue(totalQtyRecd);
totalRow.CreateCell(15).SetCellValue(totalTotal);
}
/// <summary>
/// 按模板生成 Excel 文档
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="templatePath">模板路径</param>
/// <param name="templateSheetName">模板表单名称</param>
/// <param name="excelPath">Excel 路径</param>
/// <param name="dataMatchList">数据匹配，Dictionary<string, object> 或 new {}</param>
/// <param name="dataList">数据列表</param>
/// <param name="mergeCellDataList">合并单元格数据列表</param>
/// <param name="reflectionType">反射类型</param>
public static void ToExcel <T>(string templatePath, string templateSheetName, string excelPath, object dataMatchList, List <T> dataList, List <ExcelMergeCell> mergeCellDataList = null, ReflectionTypeEnum reflectionType = ReflectionTypeEnum.Expression) where T : class
{
Dictionary <string, object> propertyDict = CommonHelper.GetParameterDict(dataMatchList);
FileStream fileStream = null;
IWorkbook workbook = null;
try
{
using (fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.ReadWrite))
{
workbook = ExcelHelper.ExecuteIWorkBookGet(fileStream);
if (workbook == null)
{
throw new Exception(ExcelHelper.ExcelWorkbookNullException);
}
}
Dictionary <int, string> propertyMapperDict = new Dictionary <int, string>();
List <ExcelTemplateFormulaItem> formulaItemList = new List <ExcelTemplateFormulaItem>();
int lastRowIndex = 0;
int insertRowIndex = -1;
ISheet sheet = workbook.GetSheet(templateSheetName);
if (sheet != null)
{
lastRowIndex = sheet.LastRowNum;
for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++)
{
IRow iRow = sheet.GetRow(rowIndex);
if (iRow != null)
{
for (int colIndex = 0; colIndex <= iRow.LastCellNum; colIndex++)
{
ICell iCell = iRow.GetCell(colIndex);
if (iCell != null && !string.IsNullOrEmpty(iCell.ToString()))
{
string cellText = iCell.ToString();
if (cellText.StartsWith("$"))
{
cellText = cellText.TrimStart(new char[] { '$' });
if (propertyDict != null && propertyDict.ContainsKey(cellText))
{
iCell.SetCellValue(propertyDict[cellText].ToString());
}
}
else if (cellText.StartsWith("#"))
{
if (insertRowIndex == -1)
{
insertRowIndex = rowIndex;
}
cellText = cellText.TrimStart(new char[] { '#' });
propertyMapperDict.Add(colIndex, cellText);
}
else if (cellText.StartsWith("&="))
{
if (rowIndex != insertRowIndex)
{
formulaItemList.Add(new ExcelTemplateFormulaItem()
{
Cell = iCell, FormulaText = cellText
});
}
}
}
}
}
}
}
if (propertyMapperDict != null && propertyMapperDict.Count > 0 && insertRowIndex != -1)
{
if (dataList != null && dataList.Count > 0)
{
dynamic propertyGetDict = null;
if (reflectionType != ReflectionTypeEnum.Original)
{
propertyGetDict = ReflectionExtendHelper.PropertyGetCallDict <T>(reflectionType);
}
CopyRow(sheet, insertRowIndex, dataList, propertyMapperDict, propertyGetDict);
if (formulaItemList != null && formulaItemList.Count > 0)
{
foreach (ExcelTemplateFormulaItem formulaItem in formulaItemList)
{
formulaItem.FormulaText = formulaItem.FormulaText.TrimStart(new char[] { '&', '=' });
formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataBegin", (insertRowIndex + 1).ToString());
formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataEnd", (insertRowIndex + dataList.Count).ToString());
formulaItem.Cell.SetCellFormula(formulaItem.FormulaText);
formulaItem.Cell.SetCellType(CellType.Formula);
}
}
}
}
sheet.ForceFormulaRecalculation = true;
if (mergeCellDataList != null && mergeCellDataList.Count > 0)
{
foreach (ExcelMergeCell cellItem in mergeCellDataList)
{
sheet.AddMergedRegion(new CellRangeAddress(insertRowIndex + cellItem.BeginRow, insertRowIndex + cellItem.EndRow, cellItem.BeginColumn, cellItem.EndColumn));
}
}
if (System.IO.File.Exists(excelPath))
{
System.IO.File.Delete(excelPath);
}
using (fileStream = new FileStream(excelPath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(fileStream);
}
workbook.Close();
}
catch
{
throw;
}
finally
{
if (fileStream != null)
{
fileStream.Close();
}
if (workbook != null)
{
workbook.Close();
}
}
}
private void MergeReportExcelColum(List<OutStoreModel> outStoreModels, ISheet sheet)
{
var preModel = outStoreModels[0];
int row_start = 2;
int row_end = 2;
for (int i = 1; i < outStoreModels.Count; i++)
{
if (preModel.Department == outStoreModels[i].Department)
{
row_end++;
}
else
{
if (row_end > row_start)
{
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));
row_start = row_end;
}
row_end++;
row_start++;
}
preModel = outStoreModels[i];
}
if (row_end > row_start)
{
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));
}
}
public void Main()
{
InitializeWorkbook();
Dictionary <String, ICellStyle> styles = CreateStyles(hssfworkbook);
ISheet sheet = hssfworkbook.CreateSheet("Timesheet");
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = true;
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
//title row
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = (45);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("Weekly Timesheet");
titleCell.CellStyle = (styles["title"]);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("$A$1:$L$1"));
//header row
IRow headerRow = sheet.CreateRow(1);
headerRow.HeightInPoints = (40);
ICell headerCell;
for (int i = 0; i < titles.Length; i++)
{
headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(titles[i]);
headerCell.CellStyle = (styles["header"]);
}
int rownum = 2;
for (int i = 0; i < 10; i++)
{
IRow row = sheet.CreateRow(rownum++);
for (int j = 0; j < titles.Length; j++)
{
ICell cell = row.CreateCell(j);
if (j == 9)
{
//the 10th cell contains sum over week days, e.g. SUM(C3:I3)
String reference = "C" + rownum + ":I" + rownum;
cell.CellFormula = ("SUM(" + reference + ")");
cell.CellStyle = (styles["formula"]);
}
else if (j == 11)
{
cell.CellFormula = ("J" + rownum + "-K" + rownum);
cell.CellStyle = (styles["formula"]);
}
else
{
cell.CellStyle = (styles["cell"]);
}
}
}
//row with totals below
IRow sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = (35);
ICell cell1 = sumRow.CreateCell(0);
cell1.CellStyle = (styles["formula"]);
ICell cell2 = sumRow.CreateCell(1);
cell2.SetCellValue("Total Hrs:");
cell2.CellStyle = (styles["formula"]);
for (int j = 2; j < 12; j++)
{
ICell cell = sumRow.CreateCell(j);
String reference = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.CellFormula = ("SUM(" + reference + ")");
if (j >= 9)
{
cell.CellStyle = (styles["formula_2"]);
}
else
{
cell.CellStyle = (styles["formula"]);
}
}
rownum++;
sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = 25;
ICell cell3 = sumRow.CreateCell(0);
cell3.SetCellValue("Total Regular Hours");
cell3.CellStyle = styles["formula"];
cell3 = sumRow.CreateCell(1);
cell3.CellFormula = ("L13");
cell3.CellStyle = styles["formula_2"];
sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = (25);
cell3 = sumRow.CreateCell(0);
cell3.SetCellValue("Total Overtime Hours");
cell3.CellStyle = styles["formula"];
cell3 = sumRow.CreateCell(1);
cell3.CellFormula = ("K13");
cell3.CellStyle = styles["formula_2"];
//set sample data
for (int i = 0; i < sample_data.GetLength(0); i++)
{
IRow row = sheet.GetRow(2 + i);
for (int j = 0; j < sample_data.GetLength(1); j++)
{
if (sample_data[i, j] == null)
{
continue;
}
if (sample_data[i, j] is String)
{
row.GetCell(j).SetCellValue((String)sample_data[i, j]);
}
else
{
row.GetCell(j).SetCellValue((Double)sample_data[i, j]);
}
}
}
//finally set column widths, the width is measured in units of 1/256th of a character width
sheet.SetColumnWidth(0, 30 * 256); //30 characters wide
for (int i = 2; i < 9; i++)
{
sheet.SetColumnWidth(i, 6 * 256); //6 characters wide
}
sheet.SetColumnWidth(10, 10 * 256); //10 characters wide
WriteToFile();
}
/// <summary>
/// 设置excel模版错误信息
/// </summary>
/// <param name="sheet">数据标签</param>
/// <param name="rowindex">错误信息显示行</param>
/// <param name="msg">错误信息</param>
public static void SetTemplateErrorMsg(ISheet sheet, int rowindex, string msg)
{
IRow row = sheet.GetRow(rowindex);
row = sheet.CreateRow(rowindex);
if (row != null && !string.IsNullOrEmpty(msg))
{
sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, row.LastCellNum));
ICell cell = row.GetCell(0);
if (cell == null)
{
cell = row.CreateCell(0);
}
ICellStyle cellStyle = sheet.Workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.Alignment = HorizontalAlignment.LEFT;
IFont font = sheet.Workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Color = HSSFColor.RED.index;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetCellValue(msg);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Yingmei"; //填加xls文件作者信息
si.ApplicationName = "映美Me云打印"; //填加xls文件创建程序信息
si.LastAuthor = "Yingmei"; //填加xls文件最后保存者信息
si.Comments = "Yingmei"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
{
if (sourceRowIndex == targetRowIndex)
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
// Get the source / new row
IRow newRow = sheet.GetRow(targetRowIndex);
IRow sourceRow = sheet.GetRow(sourceRowIndex);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
}
else
{
newRow = sheet.CreateRow(targetRowIndex);
}
// Loop through source columns to add to new row
for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
continue;
}
ICell newCell = newRow.CreateCell(i);
if (oldCell.CellStyle != null)
{
// apply style from old cell to new cell
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow - cellRangeAddress.FirstRow
)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
sheet.AddMergedRegion(newCellRangeAddress);
}
}
return newRow;
}
/// <summary>
/// 向EXCL文件写数据
/// </summary>
/// <param name="filePath"></param>
public static void WriteToExcl(string filePath)
{
//创建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的文件格式创建对应的类
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
}
ICellStyle style1 = wb.CreateCellStyle(); //样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true; //自动换行
ICellStyle style2 = wb.CreateCellStyle(); //样式
IFont font1 = wb.CreateFont(); //字体
font1.FontName = "楷体";
font1.Color = HSSFColor.Red.Index; //字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal; //字体加粗样式
style2.SetFont(font1); //样式里的字体设置具体的字体样式
//设置背景色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
ICellStyle dateStyle = wb.CreateCellStyle(); //样式
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置数据显示格式
IDataFormat dataFormatCustom = wb.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
//创建一个表单
ISheet sheet = wb.CreateSheet("Sheet0");
//设置列宽
int[] columnWidth = { 10, 10, 20, 10 };
for (int i = 0; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, 256 * columnWidth[i]);
}
//测试数据
int rowCount = 3, columnCount = 4;
object[,] data =
{
{ "列0", "列1", "列2", "列3" },
{ "", 400, 5.2, 6.01 },
{ "", true, "2014-07-02", DateTime.Now }
//日期可以直接传字符串，NPOI会自动识别
//如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显示为数字
};
IRow row;
ICell cell;
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
object obj = data[i, j];
SetCellValue(cell, data[i, j]);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
sheet.AddMergedRegion(region);
try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="tb">工作薄</param>
/// <param name="startRow">开始行</param>
/// <param name="endRow">结束行</param>
/// <param name="startCol">开始列</param>
/// <param name="endCol">结束列</param>
/// <returns></returns>
public static ICell MergedRegion(ISheet tb, int startRow, int endRow, int startCol, int endCol)
{
IRow row = tb.GetRow(startRow) == null
? tb.CreateRow(startRow)
: tb.GetRow(startRow);
ICell cell = row.GetCell(startCol) == null
? row.CreateCell(startCol)
: row.GetCell(startCol);
row.Height = 30 * 20;
if (startRow != endRow || startCol != endCol)
{
var i = tb.AddMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
}
return cell;
}