/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "lixun"; //填加xls文件作者信息
si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息
si.LastAuthor = "lixun"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
CellStyle dateStyle = workbook.CreateCellStyle();
DataFormat 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 表头及样式
{
if (strHeaderText.Length > 0)
{
Row headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
rowIndex++;
}
}
#endregion
#region 列头及样式
{
Row headerRow = sheet.CreateRow(rowIndex);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------
headStyle.BorderTop = CellBorderType.THIN;
headStyle.BorderBottom = CellBorderType.THIN;
headStyle.BorderLeft = CellBorderType.THIN;
headStyle.BorderRight = CellBorderType.THIN;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
string sName = column.ColumnName;
if (sName.Contains("剥离"))
{
sName = "剥离";
}
else if (sName.Contains("样品1"))
{
sName = "热合样品1";
}
else if (sName.Contains("样品2"))
{
sName = "热合样品2";
}
else if (sName.Contains("泡水"))
{
sName = "泡水";
}
headerRow.CreateCell(column.Ordinal).SetCellValue(sName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 350);
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 8, 10));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 11, 13));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 18, 19));
rowIndex++;
}
#endregion
}
#endregion
#region 填充内容
Row dataRow = sheet.CreateRow(rowIndex);
CellStyle mainStyle = workbook.CreateCellStyle();
mainStyle.BorderTop = CellBorderType.THIN;
mainStyle.BorderBottom = CellBorderType.THIN;
mainStyle.BorderLeft = CellBorderType.THIN;
mainStyle.BorderRight = CellBorderType.THIN;
foreach (DataColumn column in dtSource.Columns)
{
Cell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = mainStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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();
return(ms);
}
}
public void MergeRegion(int firstRow, int lastRow, int firstCol, int lastCol)
{
var range = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
Sheet.AddMergedRegion(range);
}
protected override void SetDataHeader(Sheet worksheet, DataFeedTemplate data)
{
int colindex = 0;
Row headerNameRow = worksheet.CreateRow(HeaderNameRowIndex);
headerNameRow.HeightInPoints = 30;
Row headerSymbolRow = worksheet.CreateRow(MustInputSymbolRowIndex);
//headerSymbolRow.HeightInPoints = 30;
Row headerMergeRow = worksheet.CreateRow(MergeHeaderRowIndex);
headerMergeRow.HeightInPoints = 30;
//int mustInputColIndex = 0;
//分组字典
Dictionary <string, List <int> > dicGroupList = new Dictionary <string, List <int> >();
foreach (var item in data.HeaderGroups)
{
dicGroupList.Add(item.Key, new List <int>());
}
//基本列
foreach (var item in data.BasicColumns)
{
HeaderGroup currentHeaderGroup = data.HeaderGroups.SingleOrDefault(p => p.Index == item.HeaderGroupIndex);
#region [处理列必填标志]
Cell cellSymbol = headerSymbolRow.CreateCell(colindex);
float cellHeight = cellSymbol.Row.HeightInPoints;
if (item.IsMustInput)
{
cellSymbol.SetCellValue(MustInputSymbol);
//mustInputColIndex++;
}
CellStyle style = cellSymbol.GetCommonCellStyle();
Font font = cellSymbol.Sheet.Workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = (short)FontBoldWeight.BOLD;
font.Color = HSSFColor.RED.index;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
if (item.Name == NotInputHeaderName_Item)
{
cellSymbol.SetCellValue(SystemFillSymbol);
font.FontHeightInPoints = 10;
font.Color = HSSFColor.BLACK.index;
}
style.SetFont(font);
cellSymbol.CellStyle = style;
cellSymbol.Row.HeightInPoints = cellHeight;
#endregion
#region [写列名称]
Cell cellHeaderName = headerNameRow.CreateCell(colindex);
cellHeaderName.SetCellType(CellType.STRING);
style = cellHeaderName.GetCommonCellStyle();
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.FillForegroundColor = short.Parse(currentHeaderGroup.Color);
if (item.Name == NotInputHeaderName_Item)
{
Font fontHeaderName = cellHeaderName.Sheet.Workbook.CreateFont();
style.FillForegroundColor = NotInputCellColor;
fontHeaderName.Color = HSSFColor.WHITE.index;
fontHeaderName.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(fontHeaderName);
}
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
cellHeaderName.CellStyle = style;
cellHeaderName.SetCellValue(item.Name);
#endregion
//填充分组字典
if (currentHeaderGroup != null)
{
dicGroupList[currentHeaderGroup.Key].Add(colindex);
}
colindex = colindex + 1;
}
//获取颜色
HeaderGroup currentDynamicHeader = data.HeaderGroups.SingleOrDefault(p => p.Name == HeaderGroupName);
if (data.Properties != null && data.Properties.Count > 0)
{
Font font = headerNameRow.Sheet.Workbook.CreateFont();
foreach (var item in data.Properties)
{
Cell cellHeaderNameDynamic = headerNameRow.CreateCell(colindex);
Cell cellSymbol = headerSymbolRow.CreateCell(colindex);
cellHeaderNameDynamic.SetCellValue(item.Name);
float cellHeight = cellSymbol.Row.HeightInPoints;
//if (item.IsMustInput)
//{
// font.Color = HSSFColor.RED.index;
// cellSymbol.SetCellValue(MustInputSymbol);
//}
if (item.IsInAdvSearch)
{
cellSymbol.SetCellValue(AdvSearchSymbol);
}
CellStyle styleHeaderNameDynamic = cellHeaderNameDynamic.GetCommonCellStyle();
CellStyle styleSymbol = cellSymbol.GetCommonCellStyle();
font.FontHeightInPoints = 18;
font.Boldweight = (short)FontBoldWeight.BOLD;
font.Color = HSSFColor.GREEN.index;
styleSymbol.SetFont(font);
cellSymbol.Row.HeightInPoints = cellHeight;
styleHeaderNameDynamic.BorderLeft = CellBorderType.THIN;
styleHeaderNameDynamic.BorderTop = CellBorderType.THIN;
styleHeaderNameDynamic.BorderRight = CellBorderType.THIN;
styleSymbol.BorderLeft = CellBorderType.THIN;
styleSymbol.BorderRight = CellBorderType.THIN;
//背景色
styleHeaderNameDynamic.FillPattern = FillPatternType.SOLID_FOREGROUND;
styleHeaderNameDynamic.FillForegroundColor = short.Parse(currentDynamicHeader.Color);
cellHeaderNameDynamic.CellStyle = styleHeaderNameDynamic;
cellSymbol.CellStyle = styleSymbol;
dicGroupList[HeaderGroupName].Add(colindex);
colindex = colindex + 1;
}
}
#region [处理列分类信息]
foreach (var item in dicGroupList)
{
if (item.Value != null && item.Value.Count > 0)
{
CellRangeAddress mergeArea = new CellRangeAddress(MergeHeaderRowIndex, MergeHeaderRowIndex, item.Value.First(), item.Value.Last());
worksheet.AddMergedRegion(mergeArea);
((HSSFSheet)worksheet).SetEnclosedBorderOfRegion(mergeArea, CellBorderType.MEDIUM, NPOI.HSSF.Util.HSSFColor.BLACK.index);
headerMergeRow.HeightInPoints = 30;
Cell currentGroupCell = headerMergeRow.GetCell(item.Value.First());
if (currentGroupCell == null)
{
currentGroupCell = headerMergeRow.CreateCell(item.Value.First());
}
if (item.Key == HeaderGroupName)
{
currentGroupCell.SetCellValue(C3Name + item.Key);
}
else
{
currentGroupCell.SetCellValue(item.Key);
}
CellStyle style = currentGroupCell.GetCommonCellStyle();
currentGroupCell.CellStyle = style;
}
}
#endregion
}
private static MemoryStream Export2Delivery(DeliveryNote note)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "ChuTian"; //填加xls文件作者信息
si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息
si.LastAuthor = "ChuTian"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//CellStyle dateStyle = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
sheet.SetColumnWidth(0, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(1, (int)((18.5 + 0.72) * 256));
sheet.SetColumnWidth(2, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(3, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(4, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(5, (int)((15.5 + 0.72) * 256));
sheet.SetColumnWidth(6, (int)((20 + 0.72) * 256));
//所有的字体
Font font18 = workbook.CreateFont();
font18.FontName = "华文彩云";
font18.FontHeightInPoints = 18;
font18.IsItalic = true;
Font font11Bold = workbook.CreateFont();
font11Bold.FontHeightInPoints = 11;
font11Bold.Boldweight = 700;
Font font11Normal = workbook.CreateFont();
font11Normal.FontHeightInPoints = 11;
Font font20Bold = workbook.CreateFont();
font20Bold.FontHeightInPoints = 20;
font20Bold.Boldweight = 700;
Font foot10Bold = workbook.CreateFont();
foot10Bold.FontHeightInPoints = 10;
foot10Bold.Boldweight = 700;
CellStyle companyStyle = workbook.CreateCellStyle();
companyStyle.Alignment = HorizontalAlignment.CENTER;
companyStyle.SetFont(font18);
Row companyRow = sheet.CreateRow(rowIndex);
companyRow.HeightInPoints = 30;
companyRow.CreateCell(0).SetCellValue(" 湖北楚天通讯材料有限公司");
companyRow.GetCell(0).CellStyle = companyStyle;
rowIndex++;
//创建表头
CellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
titleStyle.SetFont(font20Bold);
Row titleRow = sheet.CreateRow(rowIndex);
titleRow.HeightInPoints = 29;
titleRow.CreateCell(0).SetCellValue(" 送 货 单");
titleRow.GetCell(0).CellStyle = titleStyle;
//创建Logo
string strLogoPath = AppDomain.CurrentDomain.BaseDirectory + "ctlogo.png";
byte[] bLogo = System.IO.File.ReadAllBytes(strLogoPath);
int pictureIdx = workbook.AddPicture(bLogo, PictureType.PNG);
var patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 82, 39, 0, 2, 0, 2);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex++;
rowIndex++;
//创建父表内容
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.LEFT;
headStyle.SetFont(font11Bold);
CellStyle headDescStyle = workbook.CreateCellStyle();
headDescStyle.Alignment = HorizontalAlignment.RIGHT;
headDescStyle.SetFont(font11Normal);
Row parentRow1 = sheet.CreateRow(rowIndex);
parentRow1.CreateCell(0).SetCellValue("客户:");
parentRow1.CreateCell(1).SetCellValue(note.customer);
parentRow1.CreateCell(4).SetCellValue("送货单号：");
parentRow1.CreateCell(5).SetCellValue(note.deliverid.ToString().PadLeft(3, '0'));
parentRow1.CreateCell(6).SetCellValue(note.description);
parentRow1.GetCell(0).CellStyle = headStyle;
parentRow1.GetCell(1).CellStyle = headStyle;
parentRow1.GetCell(4).CellStyle = headStyle;
parentRow1.GetCell(5).CellStyle = headStyle;
parentRow1.GetCell(6).CellStyle = headDescStyle;
rowIndex++;
Row parentRow2 = sheet.CreateRow(rowIndex);
parentRow2.CreateCell(0).SetCellValue("型号:");
parentRow2.CreateCell(1).SetCellValue(note.model);
parentRow2.CreateCell(4).SetCellValue("发货时间：");
parentRow2.CreateCell(5).SetCellValue(note.deliverdate.ToString("yyyy.MM.dd"));
parentRow2.GetCell(0).CellStyle = headStyle;
parentRow2.GetCell(1).CellStyle = headStyle;
parentRow2.GetCell(4).CellStyle = headStyle;
parentRow2.GetCell(5).CellStyle = headStyle;
rowIndex++;
Row parentRow3 = sheet.CreateRow(rowIndex);
parentRow3.CreateCell(0).SetCellValue("货物名称:");
parentRow3.CreateCell(1).SetCellValue(note.goodname);
parentRow3.CreateCell(4).SetCellValue("出厂批号：");
parentRow3.CreateCell(5).SetCellValue(note.batch);
parentRow3.GetCell(0).CellStyle = headStyle;
parentRow3.GetCell(1).CellStyle = headStyle;
parentRow3.GetCell(4).CellStyle = headStyle;
parentRow3.GetCell(5).CellStyle = headStyle;
rowIndex++;
//表格样式
CellStyle tableStyle = workbook.CreateCellStyle();
CellStyle tableLeftStyle = workbook.CreateCellStyle();
tableStyle.Alignment = HorizontalAlignment.CENTER;
tableLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableStyle.SetFont(font11Normal);
tableLeftStyle.SetFont(font11Normal);
tableStyle.BorderTop = CellBorderType.THIN;
tableStyle.BorderBottom = CellBorderType.THIN;
tableStyle.BorderLeft = CellBorderType.THIN;
tableStyle.BorderRight = CellBorderType.THIN;
tableLeftStyle.BorderTop = CellBorderType.THIN;
tableLeftStyle.BorderBottom = CellBorderType.THIN;
tableLeftStyle.BorderLeft = CellBorderType.THIN;
tableLeftStyle.BorderRight = CellBorderType.THIN;
//小计和合计样式
CellStyle tableSumStyle = workbook.CreateCellStyle();
CellStyle tableSumLeftStyle = workbook.CreateCellStyle();
tableSumStyle.Alignment = HorizontalAlignment.CENTER;
tableSumLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableSumStyle.SetFont(font11Bold);
tableSumLeftStyle.SetFont(font11Bold);
tableSumStyle.BorderTop = CellBorderType.THIN;
tableSumStyle.BorderBottom = CellBorderType.THIN;
tableSumStyle.BorderLeft = CellBorderType.THIN;
tableSumStyle.BorderRight = CellBorderType.THIN;
tableSumLeftStyle.BorderTop = CellBorderType.THIN;
tableSumLeftStyle.BorderBottom = CellBorderType.THIN;
tableSumLeftStyle.BorderLeft = CellBorderType.THIN;
tableSumLeftStyle.BorderRight = CellBorderType.THIN;
Row colHeaderRow = sheet.CreateRow(rowIndex);
colHeaderRow.CreateCell(0).SetCellValue("件号");
colHeaderRow.CreateCell(1).SetCellValue("规格");
colHeaderRow.CreateCell(2).SetCellValue("盘数");
colHeaderRow.CreateCell(3).SetCellValue("净重（KG)");
colHeaderRow.CreateCell(4).SetCellValue("单价");
colHeaderRow.CreateCell(5).SetCellValue("金额");
colHeaderRow.CreateCell(6).SetCellValue("合同号");
colHeaderRow.CreateCell(7).SetCellValue("毛重");
colHeaderRow.CreateCell(8).SetCellValue("管芯重量");
colHeaderRow.GetCell(0).CellStyle = tableLeftStyle;
colHeaderRow.GetCell(1).CellStyle = tableStyle;
colHeaderRow.GetCell(2).CellStyle = tableStyle;
colHeaderRow.GetCell(3).CellStyle = tableStyle;
colHeaderRow.GetCell(4).CellStyle = tableStyle;
colHeaderRow.GetCell(5).CellStyle = tableStyle;
colHeaderRow.GetCell(6).CellStyle = tableStyle;
colHeaderRow.GetCell(7).CellStyle = tableStyle;
colHeaderRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
Dictionary <string, List <DeliveryItem> > dicDelivery = DeliveryItemGroup(note.items);
int iDisoTotal = 0;
double dWeightTotal = 0;
double dPriceTotal = 0;
foreach (string delivertyId in dicDelivery.Keys)
{
int iDisoSum = 0;
double dWeightSum = 0;
double dPriceSum = 0;
List <DeliveryItem> noteItems = dicDelivery[delivertyId];
foreach (DeliveryItem item in noteItems)
{
iDisoSum = iDisoSum + item.discnum;
dWeightSum = dWeightSum + item.weight;
dPriceSum = dPriceSum + item.totalprice;
iDisoTotal = iDisoTotal + item.discnum;
dWeightTotal = dWeightTotal + item.weight;
dPriceTotal = dPriceTotal + item.totalprice;
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue(item.jiannum);
colItemRow.GetCell(0).CellStyle = tableLeftStyle;
colItemRow.CreateCell(1).SetCellValue(item.specifications + "*" + item.lenght.ToString());
colItemRow.GetCell(1).CellStyle = tableStyle;
colItemRow.CreateCell(2).SetCellValue(item.discnum);
colItemRow.GetCell(2).CellStyle = tableStyle;
colItemRow.CreateCell(3).SetCellValue(item.weight.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableStyle;
colItemRow.CreateCell(4).SetCellValue(item.price.ToString("f5"));
colItemRow.GetCell(4).CellStyle = tableStyle;
colItemRow.CreateCell(5).SetCellValue(item.totalprice.ToString("f5"));
colItemRow.GetCell(5).CellStyle = tableStyle;
colItemRow.CreateCell(6).SetCellValue(item.contractno);
colItemRow.GetCell(6).CellStyle = tableStyle;
colItemRow.CreateCell(7).SetCellValue(item.netweight);
colItemRow.GetCell(7).CellStyle = tableStyle;
colItemRow.CreateCell(8).SetCellValue(item.coreweight);
colItemRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
}
if (noteItems.Count > 1 && dicDelivery.Count > 1)
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("小计");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue(iDisoSum.ToString());
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue(dWeightSum.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue(dPriceSum.ToString("f2"));
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
else
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue("");
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue("");
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue("");
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
}
//空行
Row colBlankRow = sheet.CreateRow(rowIndex);
colBlankRow.CreateCell(0).SetCellValue("");
colBlankRow.GetCell(0).CellStyle = tableLeftStyle;
colBlankRow.CreateCell(1).SetCellValue("");
colBlankRow.GetCell(1).CellStyle = tableStyle;
colBlankRow.CreateCell(2).SetCellValue("");
colBlankRow.GetCell(2).CellStyle = tableStyle;
colBlankRow.CreateCell(3).SetCellValue("");
colBlankRow.GetCell(3).CellStyle = tableStyle;
colBlankRow.CreateCell(4).SetCellValue("");
colBlankRow.GetCell(4).CellStyle = tableStyle;
colBlankRow.CreateCell(5).SetCellValue("");
colBlankRow.GetCell(5).CellStyle = tableStyle;
colBlankRow.CreateCell(6).SetCellValue("");
colBlankRow.GetCell(6).CellStyle = tableStyle;
colBlankRow.CreateCell(7).SetCellValue("");
colBlankRow.GetCell(7).CellStyle = tableStyle;
colBlankRow.CreateCell(8).SetCellValue("");
colBlankRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
//合计
Row colTotalRow = sheet.CreateRow(rowIndex);
colTotalRow.CreateCell(0).SetCellValue("合计");
colTotalRow.GetCell(0).CellStyle = tableSumLeftStyle;
colTotalRow.CreateCell(1).SetCellValue("");
colTotalRow.GetCell(1).CellStyle = tableSumStyle;
colTotalRow.CreateCell(2).SetCellValue(iDisoTotal.ToString());
colTotalRow.GetCell(2).CellStyle = tableSumStyle;
colTotalRow.CreateCell(3).SetCellValue(dWeightTotal.ToString("f2"));
colTotalRow.GetCell(3).CellStyle = tableSumStyle;
colTotalRow.CreateCell(4).SetCellValue("");
colTotalRow.GetCell(4).CellStyle = tableSumStyle;
colTotalRow.CreateCell(5).SetCellValue(dPriceTotal.ToString("f2"));
colTotalRow.GetCell(5).CellStyle = tableSumStyle;
colTotalRow.CreateCell(6).SetCellValue("");
colTotalRow.GetCell(6).CellStyle = tableSumStyle;
colTotalRow.CreateCell(7).SetCellValue("");
colTotalRow.GetCell(7).CellStyle = tableSumStyle;
colTotalRow.CreateCell(8).SetCellValue("");
colTotalRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
CellStyle footStyle = workbook.CreateCellStyle();
footStyle.Alignment = HorizontalAlignment.LEFT;
footStyle.SetFont(font11Normal);
CellStyle footBoldStyle = workbook.CreateCellStyle();
footBoldStyle.Alignment = HorizontalAlignment.LEFT;
footBoldStyle.SetFont(font11Bold);
CellStyle footRightStyle = workbook.CreateCellStyle();
footRightStyle.Alignment = HorizontalAlignment.RIGHT;
footRightStyle.SetFont(font11Normal);
Row descRow = sheet.CreateRow(rowIndex);
descRow.CreateCell(0).SetCellValue("备注：");
descRow.GetCell(0).CellStyle = footBoldStyle;
descRow.CreateCell(1).SetCellValue(note.description1);
descRow.GetCell(1).CellStyle = footBoldStyle;
rowIndex++;
Row footerRow = sheet.CreateRow(rowIndex);
footerRow.CreateCell(0).SetCellValue("请按上列货验收");
footerRow.GetCell(0).CellStyle = footStyle;
rowIndex++;
Row footerRow1 = sheet.CreateRow(rowIndex);
footerRow1.CreateCell(0).SetCellValue("收货人:");
footerRow1.GetCell(0).CellStyle = footStyle;
footerRow1.CreateCell(1).SetCellValue("");
footerRow1.GetCell(1).CellStyle = footStyle;
footerRow1.CreateCell(2).SetCellValue("送货人:");
footerRow1.GetCell(2).CellStyle = footStyle;
footerRow1.CreateCell(3).SetCellValue("");
footerRow1.GetCell(3).CellStyle = footRightStyle;
footerRow1.CreateCell(4).SetCellValue("制单:");
footerRow1.GetCell(4).CellStyle = footRightStyle;
footerRow1.CreateCell(5).SetCellValue(note.loginid);
footerRow1.GetCell(5).CellStyle = footStyle;
footerRow1.CreateCell(6).SetCellValue("审核：");
footerRow1.GetCell(6).CellStyle = footStyle;
rowIndex++;
CellStyle footStyle1 = workbook.CreateCellStyle();
footStyle1.Alignment = HorizontalAlignment.LEFT;
footStyle1.SetFont(foot10Bold);
Row footerRow2 = sheet.CreateRow(rowIndex);
footerRow2.CreateCell(0).SetCellValue("地址：湖北省汉川市马口工业园区楚天路");
footerRow2.GetCell(0).CellStyle = footStyle1;
footerRow2.CreateCell(1).SetCellValue("");
footerRow2.GetCell(1).CellStyle = footStyle1;
footerRow2.CreateCell(2).SetCellValue("");
footerRow2.GetCell(2).CellStyle = footStyle1;
footerRow2.CreateCell(3).SetCellValue(" 电话：0712-8521088 传真：0712-8512311");
footerRow2.GetCell(3).CellStyle = footStyle1;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
/// <summary>
/// 普通单表导出npoi（集合数据+样式）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
string filename = "test.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称");
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.PINK.index;
var row0 = sheet1.CreateRow(0).CreateCell(0);
row0.SetCellValue("This is a Sample");//sheet标题
row0.CellStyle = style;
var j = 17;
#region 居中/自动换行
CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式
styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式
styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式
styleCenter.WrapText = true; //自动换行
sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter;
var cell17 = sheet1.CreateRow(j).CreateCell(j);
var cell172 = sheet1.CreateRow(j).CreateCell(j + 1);
cell17.CellStyle = styleCenter;
cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
//cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
j++;
#endregion
#region 设置宽高度
sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。
var rowwh = sheet1.CreateRow(j);
rowwh.HeightInPoints = 50; //高度
rowwh.CreateCell(j).SetCellValue("宽高度");
j++;
#endregion
#region 自适应宽度（对中文不友好）+自动换行
/*场景：
* 12林学1班
* 12林学1班
*/
CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式
autoAndWrap.WrapText = true; //自动换行
var rowwhauto = sheet1.CreateRow(j);
var cellauto = rowwhauto.CreateCell(j);
cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度");
sheet1.AutoSizeColumn(j);
cellauto.CellStyle = autoAndWrap;
j++;
#endregion
#region 设置背景色
CellStyle style1 = hssfworkbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
sheet1.CreateRow(j).CreateCell(j).CellStyle = style1;
j++;
#endregion
#region 自定义背景色
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228);
HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228);
CellStyle style2 = hssfworkbook.CreateCellStyle();
style2.FillPattern = FillPatternType.SOLID_FOREGROUND;
style2.FillForegroundColor = hssFColor.GetIndex();
sheet1.CreateRow(j).CreateCell(j).CellStyle = style2;
j++;
#endregion
#region 设置字体颜色
CellStyle style3 = hssfworkbook.CreateCellStyle();
Font font1 = hssfworkbook.CreateFont();
font1.Color = hssFColor.GetIndex();//颜色
style3.SetFont(font1);
var cell20 = sheet1.CreateRow(j).CreateCell(j);
cell20.CellStyle = style3;
cell20.SetCellValue("666666666");
j++;
#endregion
List <ModelStu> data = StuDaTa.GetData();
string[] arrthead = { "ID", "name", "age", "pc" };
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1));
Row row1 = sheet1.CreateRow(1);
for (int i = 0; i < arrthead.Length; i++)
{
row1.CreateCell(i).SetCellValue(arrthead[i]);
}
for (int i = 0; i < data.Count; i++)
{
Row row = sheet1.CreateRow(i + 2);
var colIndex = 0;
row.CreateCell(colIndex++).SetCellValue(data[i].id);
row.CreateCell(colIndex++).SetCellValue(data[i].name);
row.CreateCell(colIndex++).SetCellValue(data[i].age);
row.CreateCell(colIndex).SetCellValue(data[i].pc);
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
Response.BinaryWrite(file.GetBuffer());
Response.End();
hssfworkbook = null;
file.Close();
file.Dispose();
}
public object getExcel(TransportationBill tb, IList <TransportationBillDetail> detailList, bool isExportExcel)
{
string path = Server.MapPath(".") + @"\Reports\Templates\YFKExcelTemplates\TransportationBill.xls";
if (File.Exists(path))
{
string filename = @"/Reports/Templates/TempFiles/temp_" + DateTime.Now.ToString("yyyyMMddhhmmss") + tb.BillNo + ".xls";
string _wpath = Server.MapPath(".") + filename;
File.Copy(path, _wpath);
FileStream file = new FileStream(_wpath, FileMode.Open, FileAccess.ReadWrite, FileShare.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
Sheet sheet = hssfworkbook.GetSheet("sheet1");
NPOI.SS.UserModel.CellStyle normalStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center" });
NPOI.SS.UserModel.CellStyle dateStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center", "DateTime" });
Cell cell = sheet.GetRow(8).GetCell(2);
cell.SetCellValue(tb.BillAddress.Party.Name);
cell = sheet.GetRow(8).GetCell(8);
cell.SetCellValue(tb.BillNo);
int i = 10;
decimal cnt = 0;
foreach (TransportationBillDetail tbd in detailList)
{
Row row = sheet.CreateRow(i);
TransportationOrder tord = TheTransportationOrderMgr.LoadTransportationOrder(tbd.ActBill.OrderNo);
row.CreateCell(0).SetCellValue(tord.CreateDate); //运输日期
row.CreateCell(1).SetCellValue(tord.TransportationRoute != null ? tord.TransportationRoute.Description : ""); //运输路线
row.CreateCell(2).SetCellValue(tbd.ActBill.PricingMethod != null ? tbd.ActBill.PricingMethod : ""); //运输形式
row.CreateCell(3).SetCellValue(tord.OrderNo); //运单号码
row.CreateCell(4).SetCellValue(tbd.ActBill.EffectiveDate); //生效日期
row.CreateCell(5).SetCellValue(tbd.ActBill.UnitPrice.ToString("F2")); //单价
row.CreateCell(6).SetCellValue(tbd.ActBill.Currency.Name); //币种
row.CreateCell(7).SetCellValue(tbd.ActBill.BillQty.ToString("F0")); //开票数
row.CreateCell(8).SetCellValue(tbd.ActBill.BillAmount.ToString("F2")); //金额
cnt = Convert.ToInt32(tbd.ActBill.BillAmount) + cnt;
for (int y = 0; y < 9; y++)
{
row.GetCell(y).CellStyle = normalStyle;
}
row.GetCell(0).CellStyle = dateStyle;
row.GetCell(4).CellStyle = dateStyle;
i++;
}
if (i <= 20)
{
for (int j = i; j < 21; j++)
{
Row row = sheet.CreateRow(j);
for (int y = 0; y < 9; y++)
{
row.CreateCell(y).CellStyle = normalStyle;
}
}
i = 20;
}
Row _row = sheet.CreateRow(i + 1);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 1, i + 1, 6, 7));
_row.CreateCell(6).SetCellValue("合计发票金额：");
_row.GetCell(6).CellStyle.Alignment = HorizontalAlignment.RIGHT;
_row.CreateCell(8).SetCellValue(cnt.ToString("F2"));
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
if (!isExportExcel)
{
FileStream f = new FileStream(_wpath, FileMode.Open, FileAccess.Write);
byte[] data = ms.ToArray();
f.Write(data, 0, data.Length);
f.Close();
f.Dispose();
hssfworkbook = null;
ms.Close();
ms.Dispose();
return("http://" + Request.Url.Authority + filename);
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TBillResult.xls"));
Response.BinaryWrite(ms.ToArray());
hssfworkbook = null;
ms.Close();
ms.Dispose();
return(null);
}
}
else
{
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
private void LoadPremiumInfo(Sheet sheet,Sheet contentSheet)
{
Row titleOne = CreateCell(sheet,contentSheet,0,0);
contentSheet.AddMergedRegion(new CellRangeAddress(0,0,0,9));
}
private void SetHead(Sheet sheet)
{
//sheet = hssWorkBook.CreateSheet("CustomerInfo");
Row rowHead = sheet.CreateRow(0);
Cell cell = rowHead.CreateCell(0);
cell.SetCellValue("新客户注册统计表");
Cell cellhead1 = rowHead.CreateCell(1);
Cell cellhead2 = rowHead.CreateCell(2);
Cell cellhead3 = rowHead.CreateCell(3);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
NPOI.SS.UserModel.CellStyle style = hssWorkBook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font font = hssWorkBook.CreateFont();
font.FontName = "黑体";
font.Boldweight = 700;
font.FontHeightInPoints = 14;
style.SetFont(font);
//style.BorderBottom = CellBorderType.THIN;
//style.BorderLeft = CellBorderType.THIN;
//style.BorderRight = CellBorderType.THIN;
//style.BorderTop = CellBorderType.THIN;
cell.CellStyle = style;
cellhead1.CellStyle = style;
cellhead2.CellStyle = style;
cellhead3.CellStyle = style;
rowHead.Height = 2 * 256;
NPOI.SS.UserModel.CellStyle stylefirst = hssWorkBook.CreateCellStyle();
stylefirst.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font fontfirst = hssWorkBook.CreateFont();
fontfirst.FontName = "宋体";
fontfirst.FontHeightInPoints = 10;
fontfirst.Boldweight = 700;
stylefirst.SetFont(fontfirst);
//stylefirst.BorderBottom = CellBorderType.THIN;
//stylefirst.BorderLeft = CellBorderType.THIN;
//stylefirst.BorderRight = CellBorderType.THIN;
//stylefirst.BorderTop = CellBorderType.THIN;
// rowHead.Height = 1 * 256;
Row rowfirst = sheet.CreateRow(1);
rowfirst.CreateCell(0).SetCellValue("");
Cell celfirst = rowfirst.CreateCell(1);
DateTime dateTiem = DateTime.Now.Date.AddDays(-7);
string dateBegin = dateTiem.Year.ToString() + "-" + dateTiem.Month.ToString() + "-" + dateTiem.Day.ToString();
DateTime dateEnd = DateTime.Now.AddDays(-1);
string date = dateEnd.Year.ToString() + "-" + dateEnd.Month.ToString() + "-" + dateEnd.Day.ToString();
celfirst.SetCellValue(dateBegin + " 00:00——" + date + " 24:00");
celfirst.CellStyle = stylefirst;
rowfirst.CreateCell(2).SetCellValue("");
Cell cellTitle3 = rowfirst.CreateCell(3);
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));
NPOI.SS.UserModel.CellStyle styleTitle = hssWorkBook.CreateCellStyle();
styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font fontTitle = hssWorkBook.CreateFont();
fontTitle.Boldweight = 700;
styleTitle.SetFont(fontTitle);
styleTitle.BorderBottom = CellBorderType.THIN;
styleTitle.BorderLeft = CellBorderType.THIN;
styleTitle.BorderRight = CellBorderType.THIN;
styleTitle.BorderTop = CellBorderType.THIN;
// cellTitle3.CellStyle = styleTitle;
Row row = sheet.CreateRow(2);
Cell cell0 = row.CreateCell(0);
cell0.SetCellValue("序号");
cell0.CellStyle = styleTitle;
Cell cell1 = row.CreateCell(1);
cell1.SetCellValue("顾客ID");
cell1.CellStyle = styleTitle;
Cell cell2 = row.CreateCell(2);
cell2.SetCellValue("电子邮件");
cell2.CellStyle = styleTitle;
Cell cell3 = row.CreateCell(3);
cell3.SetCellValue("状态");
cell3.CellStyle = styleTitle;
sheet.SetColumnWidth(0, 5 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 50 * 256);
sheet.SetColumnWidth(3, 5 * 256);
}
/*private void DrawBorders(TableCellBorder borders)
* {
* var style = Style;
* style.Borders = borders;
* SetStyle(style);
* }*/
public void AddCell(int colSpan = 0, int rowSpan = 0)
{
if (CurrentRow == null)
{
AddRow(); //CurrentRow = Sheet.CreateRow(CurrentRowIndex);
}
CurrentColIndex = NextColIndex++;
CurrentCell = CurrentRow.GetCell(CurrentColIndex) ?? CurrentRow.CreateCell(CurrentColIndex);
var regionColIndex = CurrentColIndex + (colSpan > 1 ? colSpan - 1 : 0);
var regionRowIndex = CurrentRowIndex + (rowSpan > 1 ? rowSpan - 1 : 0);
CellRangeAddress region = null;
if (colSpan > 1 || rowSpan > 1)
{
region = new CellRangeAddress(CurrentRowIndex, regionRowIndex, CurrentColIndex, regionColIndex);
Sheet.AddMergedRegion(region);
if (colSpan > 1)
{
NextColIndex += colSpan - 1;
}
if (rowSpan > 1)
{
NextRowIndex = Math.Max(NextRowIndex, regionRowIndex + 1);
}
}
// Style the cell with borders all around.
// if (Style.HasValues()/* || ((int)Borders) != 0*/)
// (BorderTop ?? false) || (BorderLeft ?? false) || (BorderRight ?? false) || (BorderBottom ?? false))
{
//var style = Workbook.CreateCellStyle();
var style = GetStyle(Style);
if (style == null)
{
style = region == null?CreateCellStyle(CurrentCell) : CreateCellStyle(region);
SetStyle(style, Style);
}
if (region == null)
{
SetCellStyle(CurrentCell, style);
}
else
{
SetCellStyle(region, style);
}
/*
* if (Borders.HasFlag(TableCellBorder.Top)/* ?? false♥1♥)
* {
* style.BorderTop = CellBorderType.THIN;
* style.TopBorderColor = HSSFColor.BLACK.index;
* }
* if (Borders.HasFlag(TableCellBorder.Left))
* {
* style.BorderLeft = CellBorderType.THIN;
* style.LeftBorderColor = HSSFColor.BLACK.index;
* }
* if (Borders.HasFlag(TableCellBorder.Right))
* {
* style.BorderRight = CellBorderType.THIN;
* style.RightBorderColor = HSSFColor.BLACK.index;
* }
* if (Borders.HasFlag(TableCellBorder.Bottom))
* {
* style.BorderBottom = CellBorderType.THIN;
* style.BottomBorderColor = HSSFColor.BLACK.index;
* }
*/
// style.WrapText = WrapText;
// style.ShrinkToFit = ShrinkToFit;
/*if (region == null)
* CurrentCell.CellStyle = style;
* else
* {
* for (int iRow = region.FirstRow; iRow <= region.LastRow; iRow++)
* {
* var row = Sheet.GetRow(iRow) ?? Sheet.CreateRow(iRow);
* for (int iCol = region.FirstColumn; iCol <= region.LastColumn; iCol++)
* {
* var cell = row.GetCell(iCol) ?? row.CreateCell(iCol);
* if (cell != null) cell.CellStyle = style;
* }
* }
* }*/
}
}
/// <summary>
/// 放映表导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 放映表ToolStripMenuItem_Click(object sender, EventArgs e)
{
//创建排片信息集合对象,用以保存信息
List <IMovieShowList.MovieShow> listMovie = new List <IMovieShowList.MovieShow>();
try
{
if (newExcel)
{
ExcelSource ex = new ExcelSource();
listMovie = ex.GetList4Excel(newFileName);
if (!ex.isOk)
{
MessageBox.Show(ex.Msg);
return;
}
}
else
{
//排片信息读取
listMovie = movieList;
}
if (listMovie.Count == 0)
{
MessageBox.Show("没有排片信息");
return;
}
}
catch
{
MessageBox.Show("未知错误,请重试");
return;
}
MovieEndTime end = new MovieEndTime();
listMovie = end.GetMovieEndTimeList(listMovie);
string headeValue = string.Empty;
//是否Excel源或Api源
bool ok = IsExcelOrApi(out headeValue, listMovie);
if (!ok)
{
return;
}
//让排片信息以厅号来排序
var iList = listMovie.OrderBy(i => i.Room[0]);
//提取所有厅号
var a = from room in iList select room.Room;
//去重之后放映厅的集合
List <string> RoomInfo = a.Distinct().ToList();
//创建放映表
hssfworkbook = new HSSFWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("放映表");
sheet.PrintSetup.Landscape = true;//设置为横向
//设定列宽
for (int i = 0; i < 12; i++)
{
if ((i + 1) % 3 == 0)
{
//将包含电影名称的列设置列宽
sheet.SetColumnWidth(i, 23 * 256 + 200);
}
else
{
//其它的列设置列宽
sheet.SetColumnWidth(i, 6 * 256 + 200);
}
}
//创建第一行
CreateR1(sheet, 0, 0, 0, 11, headeValue);
//行标识从第2行开始
int roomCout = 1;
Dictionary <string, int> roomIndex = new Dictionary <string, int>();
//创建当前行
Row row = sheet.CreateRow(roomCout);
row.HeightInPoints = 30;
int j = -1;
//保存厅号的位置信息
List <Cell> listCell = new List <Cell>();
//创建其它字段
for (int i = 0; i < RoomInfo.Count; i++)
{
//创建第四个厅的时候转折
if ((i + 1) % 5 == 0)
{
//并且前面空余8格
roomCout += 9;
row = sheet.CreateRow(roomCout);
row.HeightInPoints = 30;
j = -1;
}
j++;
//创建单元格
//创建字体样式
NPOI.SS.UserModel.Font fontHeader = hssfworkbook.CreateFont();
fontHeader.FontHeightInPoints = 12;
fontHeader.Boldweight = 700;
int count = j * 3;
Cell cell = row.CreateCell(count);
cell.SetCellValue(RoomInfo[i]);
cell.CellStyle = SetCellAllCenterBorder();
cell.CellStyle.SetFont(fontHeader);
//将后面的两个单元格设置出来
SetCell(row, count + 1, string.Empty);
SetCell(row, count + 2, string.Empty);
//进行单元格合并
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(roomCout, roomCout, count, count + 2));
listCell.Add(cell);
}
//将影片信息按厅排列
List <IMovieShowList.MovieShow> li = listMovie.OrderBy(x => x.Room[0]).ToList <IMovieShowList.MovieShow>();
int rowIndex = 0;
int indexCol;
foreach (var item in listCell)
{
//当前厅名
string room = item.StringCellValue;
//找到当前厅的所有子集并且进行排序
List <IMovieShowList.MovieShow> s = (from i in li where i.Room == room select i).OrderBy(x => x.BeginTime).ToList <IMovieShowList.MovieShow>();
//如果rowIndex与上次一致,则不创建行,直接在原有的行上创建单元格
//保存之前的rowIndex;
rowIndex = item.RowIndex + 1; //3
indexCol = item.ColumnIndex; //0
//循环子集添加
foreach (var movie in s)
{
//获得下一行
Row rowCell = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
//设置行高
rowCell.HeightInPoints = 20;
//在行下面创建三个单元格填充值
SetCell(rowCell, indexCol, movie.BeginTime);
SetCell(rowCell, indexCol + 1, movie.EndTime);
//单独处理电影名称,让其左对齐
//SetCell(rowCell, indexCol+2, movie.Name);
Cell cellName = rowCell.CreateCell(indexCol + 2);
cellName.CellStyle = cellLeftAllBorder();
cellName.SetCellValue(movie.Name);
rowIndex++;
}
}
//打开一个保存对话框,让用户保存数据
SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel|*.xls";
save.FileName = headeValue + "--放映 ";
DialogResult res = save.ShowDialog();
if (res == DialogResult.OK)
{
string path = save.FileName;
try
{
FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(file);
file.Close();
MessageBox.Show("保存成功");
}
catch
{
MessageBox.Show("文件已打开,请先关闭文件");
return;
}
}
else
{
return;
}
}
/// <summary>
/// Método genérico para imprimir la tabla multiescenario
/// </summary>
/// <param name="sheet">Hoja donde se escribirá la tabla</param>
/// <param name="row_ini">Fila inicial</param>
/// <param name="col_ini">Columna inicial</param>
/// <param name="nombreTabla">Título de la tabla</param>
private void ImprimirTabla(Sheet sheet, int row_ini, int col_ini, string nombreTabla)
{
Cell cell = sheet.CreateRow(row_ini + 1).CreateCell(col_ini + 2);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Bueno");
cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 3);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Normal");
cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 4);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Malo");
cell = sheet.CreateRow(row_ini + 2).CreateCell(col_ini + 1);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Bueno");
cell = sheet.CreateRow(row_ini + 3).CreateCell(col_ini + 1);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Normal");
cell = sheet.CreateRow(row_ini + 4).CreateCell(col_ini + 1);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Malo");
cell = sheet.CreateRow(row_ini).CreateCell(col_ini + 2);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Escenarios Mantto");
cell = sheet.GetRow(row_ini).CreateCell(col_ini + 3);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell = sheet.GetRow(row_ini).CreateCell(col_ini + 4);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
sheet.AddMergedRegion(new CellRangeAddress(row_ini, row_ini, col_ini + 2, col_ini + 4));
cell = sheet.GetRow(row_ini + 2).CreateCell(col_ini);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue("Escenarios WXS");
cell = sheet.GetRow(row_ini + 3).CreateCell(col_ini);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell = sheet.GetRow(row_ini + 4).CreateCell(col_ini);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
sheet.AddMergedRegion(new CellRangeAddress(row_ini + 2, row_ini + 4, col_ini, col_ini));
cell = sheet.GetRow(row_ini).CreateCell(col_ini);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(nombreTabla);
cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 1);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
cell = sheet.GetRow(row_ini).CreateCell(col_ini + 1);
cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas);
sheet.AddMergedRegion(new CellRangeAddress(row_ini, row_ini + 1, col_ini, col_ini + 1));
}
/**
* Copy the cells from srcRow to this row
* If this row is not a blank row, this will merge the two rows, overwriting
* the cells in this row with the cells in srcRow
* If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy
* srcRow may be from a different sheet in the same workbook
* @param srcRow the rows to copy from
* @param policy the policy to determine what gets copied
*/
public void CopyRowFrom(IRow srcRow, CellCopyPolicy policy)
{
if (srcRow == null)
{
// srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy
foreach (ICell destCell in this)
{
XSSFCell srcCell = null;
// FIXME: remove type casting when copyCellFrom(Cell, CellCopyPolicy) is added to Cell interface
((XSSFCell)destCell).CopyCellFrom(srcCell, policy);
}
if (policy.IsCopyMergedRegions)
{
// Remove MergedRegions in dest row
int destRowNum = RowNum;
int index = 0;
HashSet <int> indices = new HashSet <int>();
foreach (CellRangeAddress destRegion in Sheet.MergedRegions)
{
if (destRowNum == destRegion.FirstRow && destRowNum == destRegion.LastRow)
{
indices.Add(index);
}
index++;
}
(Sheet as XSSFSheet).RemoveMergedRegions(indices.ToList());
}
if (policy.IsCopyRowHeight)
{
// clear row height
Height = ((short)-1);
}
}
else
{
foreach (ICell c in srcRow)
{
XSSFCell srcCell = (XSSFCell)c;
XSSFCell destCell = CreateCell(srcCell.ColumnIndex, srcCell.CellType) as XSSFCell;
destCell.CopyCellFrom(srcCell, policy);
}
XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
int sheetIndex = _sheet.Workbook.GetSheetIndex(_sheet);
String sheetName = _sheet.Workbook.GetSheetName(sheetIndex);
int srcRowNum = srcRow.RowNum;
int destRowNum = RowNum;
int rowDifference = destRowNum - srcRowNum;
FormulaShifter shifter = FormulaShifter.CreateForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
rowShifter.UpdateRowFormulas(this, shifter);
// Copy merged regions that are fully contained on the row
// FIXME: is this something that rowShifter could be doing?
if (policy.IsCopyMergedRegions)
{
foreach (CellRangeAddress srcRegion in srcRow.Sheet.MergedRegions)
{
if (srcRowNum == srcRegion.FirstRow && srcRowNum == srcRegion.LastRow)
{
CellRangeAddress destRegion = srcRegion.Copy();
destRegion.FirstRow = (destRowNum);
destRegion.LastRow = (destRowNum);
Sheet.AddMergedRegion(destRegion);
}
}
}
if (policy.IsCopyRowHeight)
{
Height = (srcRow.Height);
}
}
}
/// <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();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
CellStyle dateStyle = workbook.CreateCellStyle();
DataFormat 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 表头及样式
{
Row headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
Row headerRow = sheet.CreateRow(1);
CellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
Font 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 = 2;
}
#endregion
#region 填充内容
Row dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
Cell 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;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
