protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetString("txtCusName");
_cid = DTRequest.GetString("hCusId");
_method = DTRequest.GetFormString("ddlmethod");
_isconfirm = DTRequest.GetFormString("ddlisConfirm");
_sforedate = DTRequest.GetFormString("txtsforedate");
_eforedate = DTRequest.GetFormString("txteforedate");
_sdate = DTRequest.GetFormString("txtsdate");
_edate = DTRequest.GetFormString("txtedate");
_num = DTRequest.GetFormString("txtNum");
_chk = DTRequest.GetFormString("txtChk");
BLL.ReceiptPay bll = new BLL.ReceiptPay();
DataTable dt = bll.GetList(this.pageSize, this.page, "rp_type=1 " + CombSqlTxt(), "isnull(rp_date,'3000-01-01') desc,isnull(pm_sort,-1) asc,rp_id desc", out this.totalCount, out _tmoney, out _tunmoney, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=收款通知列表.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("收款对象");
headRow.CreateCell(1).SetCellValue("凭证");
headRow.CreateCell(2).SetCellValue("收款内容");
headRow.CreateCell(3).SetCellValue("收款金额");
headRow.CreateCell(4).SetCellValue("未分配金额");
headRow.CreateCell(5).SetCellValue("预收日期");
headRow.CreateCell(6).SetCellValue("收款方式");
headRow.CreateCell(7).SetCellValue("实收日期");
headRow.CreateCell(8).SetCellValue("申请人");
headRow.CreateCell(9).SetCellValue("确认收款");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 15 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString() + (Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isExpect"]), false) ? "[预]" : ""));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["ce_num"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_content"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_money"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["undistribute"]));
row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_foredate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
row.CreateCell(7).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_personName"]));
row.CreateCell(9).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isConfirm"]), false) ? "已收款" : "待收款");
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dtSource"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="writeColumnName"></param>
/// <param name="excelType"></param>
public static void WriteInToExcel <T>(this IEnumerable <T> dtSource, string fileName, string sheetName, bool writeColumnName = true, ExcelType excelType = ExcelType.XLS)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
try
{
#region 初始化
if (excelType == ExcelType.XLS)
{
workbook = new HSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xls";
}
}
else
{
workbook = new XSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xlsx";
}
}
if (workbook != null)
{
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
#endregion
#region 写列名称
if (writeColumnName)
{
IRow row = sheet.CreateRow(0);
int columnIndex = 0;
typeof(T).GetProperties().AsEnumerable().ToList().ForEach(x =>
{
row.CreateCell(columnIndex).SetCellValue(x.Name != null ? x.Name : string.Empty);
columnIndex++;
});
}
#endregion
#region 写内容
int i = writeColumnName ? 1 : 0;
dtSource.ToList().ForEach(x =>
{
IRow row = sheet.CreateRow(i);
int m = 0;
typeof(T).GetProperties().AsEnumerable().ToList().ForEach(p =>
{
var value = x.GetType().GetProperty(p.Name).GetValue(x, null);
row.CreateCell(m).SetCellValue(TypeConvert.ToString(p.PropertyType, value != null ? value : string.Empty));
m++;
});
i++;
});
#endregion
workbook.Write(fs); //写入到excel
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Dispose();
fs.Close();
}
}
/// <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++;
if (colint < excelConfig.ColumnEntity.Count - 1)
{
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 = "刘晓雷"; //填加xls文件作者信息
si.ApplicationName = "力软信息"; //填加xls文件创建程序信息
si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息
si.Comments = "刘晓雷"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.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)
{
ColumnModel 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
int rowIndex = 0;
#region 表头及样式
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
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(rowIndex);
rowIndex++;
#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);
int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(column.Ordinal, 6000);
}
}
#endregion
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
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(rowIndex);
rowIndex++;
#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
}
#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 ActionResult XLS(string List)
{
var stream = new MemoryStream();
var workbook = new HSSFWorkbook();
var ids = List.Split <int>(";");
var orders = DB.Orders.Where(x => ids.Contains(x.ID) && x.Shop != null && x.Shop.Owner == CurrentUser.ShopOwnerID).ToList();
foreach (var order in orders)
{
var worksheet = workbook.CreateSheet("Заказ №" + order.OrderNumberOrID.Replace("[", "@").Replace("]", "@"));
var header = worksheet.CreateRow(0);
header.CreateCell(0).SetCellValue("Информация о заказе:");
header = worksheet.CreateRow(1);
header.CreateCell(0).SetCellValue("Номер заказа");
header.CreateCell(1).SetCellValue(order.OrderNumberOrID);
header = worksheet.CreateRow(2);
header.CreateCell(0).SetCellValue("Статус заказа");
header.CreateCell(1).SetCellValue(order.StatusText);
header = worksheet.CreateRow(3);
header.CreateCell(0).SetCellValue("Пометка \"Важный\"");
header.CreateCell(1).SetCellValue(order.IsImportant.ToYesNoStatus());
header = worksheet.CreateRow(4);
header.CreateCell(0).SetCellValue("Дата создания");
header.CreateCell(1).SetCellValue(order.CreateDate.ToString("dd.MM.yyyy HH:mm:ss"));
header = worksheet.CreateRow(5);
header.CreateCell(0).SetCellValue("Комментарий");
header.CreateCell(1).SetCellValue(order.Warning);
header = worksheet.CreateRow(6);
header.CreateCell(0).SetCellValue("");
if (order.Consumer != null)
{
header = worksheet.CreateRow(7);
header.CreateCell(0).SetCellValue("Информация о покупателе:");
header = worksheet.CreateRow(8);
header.CreateCell(0).SetCellValue("ФИО");
header.CreateCell(1).SetCellValue(order.Consumer.FullName);
header = worksheet.CreateRow(9);
header.CreateCell(0).SetCellValue("Телефон");
header.CreateCell(1).SetCellValue(order.Consumer.Phone);
header = worksheet.CreateRow(10);
header.CreateCell(0).SetCellValue("");
}
if (order.OrderedProducts.Any())
{
header = worksheet.CreateRow(11);
header.CreateCell(0).SetCellValue("Заказанные товары:");
header = worksheet.CreateRow(12);
header.CreateCell(0).SetCellValue("Название");
header.CreateCell(1).SetCellValue("Артикул");
header.CreateCell(2).SetCellValue("Цена");
header.CreateCell(3).SetCellValue("Количество");
header.CreateCell(4).SetCellValue("Вес");
int index = 5;
foreach (var ch in order.CharList)
{
header.CreateCell(index).SetCellValue(ch.Name);
index++;
}
int msi = 13;
foreach (var product in order.OrderedProducts)
{
header = worksheet.CreateRow(msi);
header.CreateCell(0).SetCellValue(product.Product.Name);
header.CreateCell(1).SetCellValue(product.Product.Article);
header.CreateCell(2).SetCellValue(product.Price.ToString());
header.CreateCell(3).SetCellValue(product.Amount);
header.CreateCell(4).SetCellValue(product.Product.Weight.ToString());
index = 5;
foreach (var ch in order.CharList)
{
header.CreateCell(index)
.SetCellValue(product.ProductChars.Any(x => x.Name == ch.Name)
? product.ProductChars.First(x => x.Name == ch.Name).Value
: "");
index++;
}
msi++;
}
}
}
workbook.Write(stream);
return(File(stream.ToArray(), MIMETypeWrapper.GetMIME("xls"),
"Order_" + List.Trim(';').Replace(";", "_") + ".xls"));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="html">表头html结构</param>
/// <param name="dateFormat">时间格式（默认：yyyy-MM-dd）</param>
private static MemoryStream DataTableToComplexExcel(DataTable dtSource, string html, string dateFormat)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
//设置单元格样式及字体
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10; //字体大小
font.Boldweight = 700; //字体粗细
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//取得列宽
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
//获取页面html并对tr进行筛选
MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
//写在tr循环中
for (int i = 0; i < rowCollection.Count; i++)
{
HSSFRow rowTitle = (HSSFRow)sheet.CreateRow(i);
string rowContent = rowCollection[i].Value;
//对td进行筛选
MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
//遍历td
for (int j = 0; j < columnCollection.Count; j++)
{
var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\".*?class=\"(?<class>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
if (match.Success)
{
int rowspan = Convert.ToInt32(match.Groups["row"].Value); //表格跨行
int colspan = Convert.ToInt32(match.Groups["col"].Value); //表格跨列
int rowNo = Convert.ToInt32(match.Groups["row1"].Value); //所在行
int colNo = Convert.ToInt32(match.Groups["col1"].Value); //所在列
string value = match.Groups["value"].Value;
if (colspan == 1) //判断是否跨列
{
var cell = rowTitle.CreateCell(colNo); //创建列
cell.SetCellValue(value); //设置列的值
cell.CellStyle = cellStyle;
if (value.Length > 0)
{
int width = value.Length * 25 / 6;
if (width > 255)
{
width = 250;
}
sheet.SetColumnWidth(colNo, width * 256);
}
}
//判断是否跨行、跨列
if (rowspan > 1 || colspan > 1)
{
int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0;
if (rowspan > 1)//跨行
{
firstRow = rowNo;
lastRow = firstRow + rowspan - 1;
}
else
{
firstRow = lastRow = i;
}
if (colspan > 1)//跨列
{
firstCol = colNo;
int cols = colNo + colspan;
for (; colNo < cols; colNo++)
{
var cell = rowTitle.CreateCell(colNo);
cell.SetCellValue(value);
cell.CellStyle = cellStyle;
}
lastCol = colNo - 1;
}
else
{
firstCol = lastCol = colNo;
}
//设置起始行数，结束行数，起始列数，结束列数
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
}
}
}
rowIndex = rowCollection.Count;
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle.Alignment = HorizontalAlignment.Center; //水平居中;
newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
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 Init(object data)
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
}
//导入
protected void btnimport_Click(object sender, EventArgs e)
{
List <string> list = new List <string>();
string sqlifsc = "select * from OM_GZHSB where GZ_YEARMONTH='" + dplYear.SelectedValue.ToString().Trim() + "-" + dplMoth.SelectedValue.ToString().Trim() + "' and OM_GZSCBZ='1'";
System.Data.DataTable dtifsc = DBCallCommon.GetDTUsingSqlText(sqlifsc);
if (dtifsc.Rows.Count > 0)
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('该月已生成工资，不能导入！！！');", true);
ModalPopupExtenderSearch.Hide();
return;
}
string FilePath = @"E:\考核成绩记录表\";
if (!Directory.Exists(FilePath))
{
Directory.CreateDirectory(FilePath);
}
//将文件上传到服务器
HttpPostedFile UserHPF = FileUpload1.PostedFile;
try
{
string fileContentType = UserHPF.ContentType;// 获取客户端发送的文件的 MIME 内容类型
if (fileContentType == "application/vnd.ms-excel")
{
if (UserHPF.ContentLength > 0)
{
UserHPF.SaveAs(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName));//将上传的文件存放在指定的文件夹中
}
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('文件类型不符合要求，请您核对后重新上传！');", true);
ModalPopupExtenderSearch.Hide();
return;
}
}
catch
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('文件上传过程中出现错误！');", true);
ModalPopupExtenderSearch.Hide();
return;
}
using (FileStream fs = File.OpenRead(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName)))
{
//根据文件流创建一个workbook
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet1 = wk.GetSheetAt(0);
if (tb_Time.Text.ToString().Trim() != "")
{
for (int i = 1; i < sheet1.LastRowNum + 1; i++)
{
string sh_stid = "";
string stdepid = "";
string strcell01 = "";
IRow row = sheet1.GetRow(i);
ICell cell01 = row.GetCell(1);
try
{
strcell01 = cell01.ToString().Trim();
}
catch
{
strcell01 = "";
}
if (strcell01 != "")
{
ICell cell1 = row.GetCell(1);
string strcell1 = cell1.ToString().Trim();
string sqltext = "select ST_ID,ST_DEPID from TBDS_STAFFINFO where ST_WORKNO='" + strcell1 + "'";
System.Data.DataTable dttext = DBCallCommon.GetDTUsingSqlText(sqltext);
if (dttext.Rows.Count > 0)
{
ICell cell3 = row.GetCell(3);
ICell cell4 = row.GetCell(4);
string strcell3 = "";
string strcell4 = "";
try
{
strcell3 = cell3.NumericCellValue.ToString().Trim();
strcell4 = cell4.NumericCellValue.ToString().Trim();
}
catch
{
try
{
strcell3 = cell3.ToString().Trim();
strcell4 = cell4.ToString().Trim();
}
catch
{
strcell3 = "";
strcell4 = "";
}
}
string sqlupdate = "";
sh_stid = dttext.Rows[0]["ST_ID"].ToString().Trim();
stdepid = dttext.Rows[0]["ST_DEPID"].ToString().Trim();
if (stdepid == "13" || stdepid == "08" || stdepid == "09")
{
sqlupdate = "update TBDS_KaoHeTotal set Kh_ScoreHP='" + CommonFun.ComTryDecimal(strcell3) + "',Kh_ScoreLD='" + CommonFun.ComTryDecimal(strcell4) + "' where Kh_Id='" + sh_stid + "' and Kh_Year='" + tb_Time.Text.ToString().Trim().Substring(0, 4) + "' and Kh_Month='" + tb_Time.Text.ToString().Trim().Substring(5, 2) + "'";
}
else
{
sqlupdate = "update TBDS_KaoHeTotal set Kh_ScoreHP='" + CommonFun.ComTryDecimal(strcell3) + "' where Kh_Id='" + sh_stid + "' and Kh_Year='" + tb_Time.Text.ToString().Trim().Substring(0, 4) + "' and Kh_Month='" + tb_Time.Text.ToString().Trim().Substring(5, 2) + "'";
}
list.Add(sqlupdate);
}
}
else
{
break;
}
}
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('请选择年月！！！');", true);
ModalPopupExtenderSearch.Hide();
return;
}
}
DBCallCommon.ExecuteTrans(list);
updatetotalscore(); //更新总分数
foreach (string fileName in Directory.GetFiles(FilePath)) //清空该文件夹下的文件
{
string newName = fileName.Substring(fileName.LastIndexOf("\\") + 1);
System.IO.File.Delete(FilePath + "\\" + newName);//删除文件下储存的文件
}
ModalPopupExtenderSearch.Hide();
UCPaging1.CurrentPage = 1;
this.bindGrid();
}
private void savePf_MenuItem_Click(object sender, RoutedEventArgs e)
{
if (docPfdataGrid.ItemsSource == null)
{
return;
}
HSSFWorkbook wk;
string z_name = "";
string z_pfdate = "";
using (FileStream f = new FileStream(@"temp1.xls", FileMode.OpenOrCreate))
{
wk = new HSSFWorkbook(f);
ISheet sh = wk.GetSheet("Sheet1");
List <PF> x = (List <PF>)docPfdataGrid.ItemsSource;
var zn = (from g in x select g.站名).First();
var pftime = (from dd in x select dd.派发日期).First();
z_name = zn;
z_pfdate = pftime.HasValue == true?pftime.Value.ToString("yyyy年M月dd") : "unknown";
IRow r = sh.GetRow(1);
ICell c = r.GetCell(1);
string note =
string
.Format(" {0}，经对{1}站结合部设备问题收集，存在下列问题，计划下周整治,请按计划时间准备人力、料具到现场共同配合",
pftime.Value.ToString("yyyy年M月d日"), zn);
c.SetCellValue(note);
// 序号(3,0),设备名称(3,1),存在问题(3,2),整治方案(3,6),计划时间(3,7),整治情况(3.8),负责人(3.9).
for (int i = 0; i < x.Count(); i++)
{
IRow row = sh.GetRow(3 + i);
row.GetCell(0).SetCellValue(i + 1);
row.GetCell(1).SetCellValue(x[i].设备名称);
row.GetCell(2).SetCellValue(x[i].存在问题);
row.GetCell(6).SetCellValue(x[i].保留2);
row.GetCell(7).SetCellValue(x[i].计划时间.Value);
}
}
string tmpfn = System.IO.Path.GetTempFileName();
string fn = System.IO.Path.ChangeExtension(tmpfn, "xls");
FileInfo finfo = new FileInfo(tmpfn);
finfo.MoveTo(fn);
TempFile.Add(fn);
using (FileStream s = new FileStream(fn, FileMode.OpenOrCreate))
{
wk.Write(s);
}
string curDir = System.Environment.CurrentDirectory;
string saveName = string.Format(@"{0}/user/{1}站{2}整治表.xls", curDir, z_name, z_pfdate);
using (FileStream savefn = new FileStream(saveName, FileMode.OpenOrCreate))
{
wk.Write(savefn);
}
Process.Start(fn);
//string fn = g_var.getSaveDialog();
//if (fn==null)
//{
// return;
//}
//
//
//XElement AllDoc = new XElement("记录集");
//XElement doc = new XElement("re", "re");
//foreach (var it in x)
//{
// string dt1 = string.Empty;
// if (it.计划时间!=null)
// {
// dt1 = it.计划时间.Value.ToString("yyyy年M月d日");
// }
// doc =
// new XElement("Record",
// new XElement("设备名称", it.设备名称),
// new XElement("存在问题", it.存在问题),
// new XElement("整改方案", it.保留2),
// new XElement("计划时间", dt1),
// new XElement("整治情况" ),
// new XElement("整治负责人")
// );
// AllDoc.Add(doc);
//}
//AllDoc.Save(fn);
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
{
EvaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb));
}
/// <summary>
/// 分表导出到多个Sheet
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strHeaderText"></param>
/// <param name="hidText"></param>
/// <param name="FileName"></param>
/// <returns></returns>
public static MemoryStream ExportMultipleSheet(List <DataTable> dtSource, List <string> strHeaderText, List <string> hidText, string FileName = null)
{
IWorkbook workbook = null;
if (!string.IsNullOrWhiteSpace(FileName))
{
if (FileName.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook();
}
// 2003版本
else if (FileName.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook();
}
}
else
{
workbook = new HSSFWorkbook();
}
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = CompanyName;
//workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = CompanyName; //填加xls文件作者信息
si.ApplicationName = CompanyName; //填加xls文件创建程序信息
si.LastAuthor = CompanyName; //填加xls文件最后保存者信息
si.Comments = CompanyName; //填加xls文件作者信息
si.Title = CompanyName; //填加xls文件标题信息
si.Subject = CompanyName; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
//表头样式
ICellStyle headStyleOne = workbook.CreateCellStyle();
headStyleOne.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = 700;
headStyleOne.SetFont(font);
//表头1
ICellStyle headStyleTwo = workbook.CreateCellStyle();
headStyleTwo.Alignment = HorizontalAlignment.Right;
headStyleTwo.Indention = 50;
//列头样式
ICellStyle headStyleThree = workbook.CreateCellStyle();
headStyleThree.Alignment = HorizontalAlignment.Left;
headStyleThree.VerticalAlignment = VerticalAlignment.Center;
IFont fonts = workbook.CreateFont();
fonts.FontHeightInPoints = 10;
fonts.Boldweight = 700;
headStyleThree.SetFont(fonts);
//每个表头名
var HeadText = string.Empty;
//每个表头二
var HeadTextTwo = string.Empty;
//遍历多张表(每一张表创建一个Sheet)
for (int i = 0; i < dtSource.Count; i++)
{
HeadText = strHeaderText[i];
HeadTextTwo = hidText[i];
#region 处理cell样式和大小
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource[i].Columns.Count];
foreach (DataColumn item in dtSource[i].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int r = 0; r < dtSource[i].Rows.Count; r++)
{
for (int j = 0; j < dtSource[i].Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[i].Rows[r][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
ICellStyle tdStyle = null;
if (!string.IsNullOrWhiteSpace(FileName))
{
//2007版本
if (FileName.IndexOf(".xlsx") > 0)
{
tdStyle = (XSSFCellStyle)workbook.CreateCellStyle();
}
// 2003版本
else if (FileName.IndexOf(".xls") > 0)
{
tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
}
}
else
{
tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
}
#endregion
int rowIndex = 0;
ISheet sheet = workbook.CreateSheet();
foreach (DataRow row in dtSource[i].Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(HeadText);
headerRow.GetCell(0).CellStyle = headStyleOne;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[i].Columns.Count - 1));
}
#endregion
{
IRow headerRow = sheet.CreateRow(1);
headerRow.CreateCell(0).SetCellValue(HeadTextTwo + "| 导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
headerRow.HeightInPoints = 18;
headerRow.GetCell(0).CellStyle = headStyleTwo;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtSource[i].Columns.Count - 1));
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(2);
headerRow.HeightInPoints = 18;
foreach (DataColumn column in dtSource[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyleThree;
//设置列宽
if (column.ColumnName == "款号图片")
{
sheet.SetColumnWidth(column.Ordinal, 20 * 256);
}
else
{
var columnWidth = (arrColWidth[column.Ordinal] + 1) * 256;
sheet.SetColumnWidth(column.Ordinal, columnWidth > (255 * 256) ? 6000 : columnWidth);
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
rowIndex = 3;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 18;
//if (rowIndex % 2 == 0)
// tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
//else
// tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//tdStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
////设置单元格边框
//tdStyle.BorderBottom = BorderStyle.THIN;
//tdStyle.BorderLeft = BorderStyle.THIN;
//tdStyle.BorderRight = BorderStyle.THIN;
//tdStyle.BorderTop = BorderStyle.THIN;
tdStyle.VerticalAlignment = VerticalAlignment.Center;
short datet = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
bool isCell = dtSource[i].Rows.Count < 4000; //4000行bug
InitRowData(dtSource[i], tdStyle, row, dataRow, isCell, workbook, rowIndex, HeadText);
#endregion
rowIndex++;
}
}
ExcelStream stream = new ExcelStream();
workbook.Write(stream);
stream.CanDispose = true;
return(stream);
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
Entity_Job data = (Entity_Job)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_Job));
if (data == null)
{
data = ScriptableObject.CreateInstance <Entity_Job> ();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.sheets.Clear();
using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) {
IWorkbook book = new HSSFWorkbook(stream);
foreach (string sheetName in sheetNames)
{
ISheet sheet = book.GetSheet(sheetName);
if (sheet == null)
{
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
Entity_Job.Sheet s = new Entity_Job.Sheet();
s.name = sheetName;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
Entity_Job.Param p = new Entity_Job.Param();
cell = row.GetCell(0); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(1); p.name = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.lv = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(3); p.difficult = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.attack = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(5); p.defens = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(6); p.sp = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(7); p.cooltime = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(8); p.casttime = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(9); p.effect = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(10); p.point = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(11); p.bonus = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(12); p.type = (int)(cell == null ? 0 : cell.NumericCellValue);
s.list.Add(p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
/// <summary>
/// 更新excel
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public bool ReadFromExcelFile(string filePath)
{
bool b = false;
int startRow = 3;
int startCell = 6;
int endCell = 3;
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(filePath);
try
{
using (var fs = File.OpenRead(filePath))
{
//FileStream fs = File.Open(filePath,FileMode.OpenOrCreate, FileAccess.ReadWrite))
//FileStream fs = File.OpenRead(filePath); OpenWrite
if (extension != null && extension.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
fs.Close();
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
sheet.ForceFormulaRecalculation = true;
for (int i = startRow; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i); //读取当前行数据
if (row != null)
{
//项目
if (row.GetCell(0) == null || string.IsNullOrWhiteSpace(row.GetCell(0).ToString()) || row.GetCell(0).ToString().ToUpper().Contains("PM") == false)
{
continue;
}
Console.Write(i + "行:");
var gs = row.GetCell(14);
if (gs == null)
{
Console.Write(" O列的公式 =ROUND(100/项目总天数,0) 没有值,报表格式异常跳过 ");
return(b);
}
if (gs.NumericCellValue == 0)
{
Console.Write(gs.NumericCellValue + " O列的公式没有值,报表格式异常跳过 ");
return(b);
}
//工序计划交期
DateTime overdt = DateTime.MaxValue;
string overDT = row.GetCell(5).ToString();
if (string.IsNullOrWhiteSpace(overDT))
{
Console.Write("工序计划交期 没有值,报表格式异常跳过 ");
return(b);
}
else
{
overdt = Convert.ToDateTime(overDT);
}
int len = startCell + endCell;
//LastCellNum 是当前行的总列数row.LastCellNum
for (int j = startCell; j < len; j++)
{
Console.Write(j + "列= ");
//读取该行的第j列数据
if (row.GetCell(j + 1) != null && row.GetCell(j + 1).NumericCellValue > 0)
{
double value = row.GetCell(j).NumericCellValue;
Console.Write(value + "改为");
double d = 0;
if (j == 8)
{
//=IF((I5*100-O5)/100<0,0,((I5*100-O5)/100))
d = (row.GetCell(j).NumericCellValue * 100 - gs.NumericCellValue) / 100;
}
else
{
d = row.GetCell(j + 1).NumericCellValue;
}
Console.Write(d + " | ");
row.GetCell(j).SetCellValue(d > 0 ? d : 0);
}
else
{
row.GetCell(j).SetCellValue(0);
Console.Write("0 | ");
}
}
if (row.GetCell(6).NumericCellValue <= 0)
{
if (overdt < DateTime.Now)
{
row.GetCell(10).SetCellValue("测试中");
}
//else
// row.GetCell(10).SetCellValue("已完成");
}
Console.WriteLine("\n");
}
}
b = true;
//模板自动公式需要激活
sheet.ForceFormulaRecalculation = true;
try
{
//转为字节数组
var stream = new MemoryStream();
wk.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs5 = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
fs5.Write(buf, 0, buf.Length);
fs5.Flush();
}
Console.Write("保存OK=" + filePath);
}
catch (Exception e)
{
b = false;
Logs.WriteLog(e);
}
}
}
catch (Exception e)
{
b = false;
Logs.WriteLog(e);
}
return(b);
}
public JsonResult ExportAdd(EditViewModel model)
{
CustomJsonResult r = new CustomJsonResult();
r.ContentType = "text/html";
try
{
HttpPostedFileBase file_upload = Request.Files[0];
if (file_upload == null)
{
return(Json("text/html", ResultType.Failure, "找不到上传的对象"));
}
if (file_upload.ContentLength == 0)
{
return(Json("text/html", ResultType.Failure, "文件内容为空,请重新选择"));
}
System.IO.FileInfo file = new System.IO.FileInfo(file_upload.FileName);
string ext = file.Extension.ToLower();
if (ext != ".xls")
{
return(Json("text/html", ResultType.Failure, "上传的文件不是excel格式(xls)"));
}
HSSFWorkbook workbook = new HSSFWorkbook(file_upload.InputStream);
ISheet sheet = workbook.GetSheetAt(0);
ExportCheckErrorPoint exportCheckErrorPoint = new ExportCheckErrorPoint();
IRow rowTitle = sheet.GetRow(0);
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(0), "设备ID");
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(1), "机身号");
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(2), "终端号");
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(3), "版本号");
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(4), "押金");
exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(5), "租金");
if (exportCheckErrorPoint.TitleHasError)
{
return(Json("text/html", ResultType.Failure, "上传的文件模板错误，请点击下载的文件模板"));
}
int rowCount = sheet.LastRowNum + 1;
if (rowCount == 1)
{
return(Json("text/html", ResultType.Failure, "上传的文件数据为空，请检查后再次上传"));
}
List <PosMachine> posMachines = new List <PosMachine>();
for (int i = 1; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
string deviceId = row.GetCell(0).ToString();
string fuselageNumber = row.GetCell(1) == null ? "" : row.GetCell(1).ToString();
string terminalNumber = row.GetCell(2) == null ? "" : row.GetCell(2).ToString();
string version = row.GetCell(3) == null ? "" : row.GetCell(3).ToString();
string deposit = row.GetCell(4) == null ? "" : row.GetCell(4).ToString();
string rent = row.GetCell(5) == null ? "" : row.GetCell(5).ToString();
if (string.IsNullOrEmpty(deviceId))
{
return(Json("text/html", ResultType.Failure, "检查到有为空的设备ID，请完善后再次上传"));
}
if (string.IsNullOrEmpty(deviceId) || deviceId.Length > 100)
{
exportCheckErrorPoint.AddPoint(deviceId, "设备ID不能为空，且不能超过100个字符");
}
if (string.IsNullOrEmpty(fuselageNumber) || fuselageNumber.Length > 100)
{
exportCheckErrorPoint.AddPoint(deviceId, "机身号不能为空，且不能超过100个字符");
}
if (string.IsNullOrEmpty(terminalNumber) || terminalNumber.Length > 100)
{
exportCheckErrorPoint.AddPoint(deviceId, "终端号不能为空，且不能超过100个字符");
}
if (string.IsNullOrEmpty(version) || version.Length > 100)
{
exportCheckErrorPoint.AddPoint(deviceId, "版本号不能为空，且不能超过100个字符");
}
if (CommonUtils.IsDecimal(deposit))
{
exportCheckErrorPoint.AddPoint(deviceId, "押金必须数字格式,且整数位最多16位,小数位最多2位");
}
if (CommonUtils.IsDecimal(rent))
{
exportCheckErrorPoint.AddPoint(deviceId, "租金必须数字格式,且整数位最多16位,小数位最多2位");
}
PosMachine posMachine = new PosMachine();
posMachine.DeviceId = deviceId;
posMachine.FuselageNumber = fuselageNumber;
posMachine.TerminalNumber = terminalNumber;
posMachine.Version = version;
posMachines.Add(posMachine);
}
if (exportCheckErrorPoint.ErrorPoint.Count > 0)
{
return(Json("text/html", ResultType.Failure, exportCheckErrorPoint.ErrorPoint, "更新数据失败，检查到无效的数据"));
}
using (TransactionScope ts = new TransactionScope())
{
foreach (var posMachine in posMachines)
{
var old = CurrentDb.PosMachine.Where(m => m.DeviceId == posMachine.DeviceId).FirstOrDefault();
if (old != null)
{
exportCheckErrorPoint.AddPoint(old.DeviceId, "设备ID号:" + posMachine.DeviceId + "，已经存在");
}
}
if (exportCheckErrorPoint.ErrorPoint.Count > 0)
{
return(Json("text/html", ResultType.Failure, exportCheckErrorPoint.ErrorPoint, "更新数据失败，检查到无效的数据"));
}
DateTime dateNow = DateTime.Now;
foreach (var posMachine in posMachines)
{
posMachine.CreateTime = dateNow;
posMachine.Creator = this.CurrentUserId;
CurrentDb.PosMachine.Add(posMachine);
}
CurrentDb.SaveChanges();
ts.Complete();
}
return(Json("text/html", ResultType.Success, "上传成功"));
}
catch (Exception ex)
{
Log.Error("导入POS机信息", ex);
return(Json("text/html", ResultType.Exception, "上传失败，系统出现异常"));
}
}
/// <summary>
/// 读取excel
/// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1；
/// headerRow.LastCellNum属性获取的是Excel中该行的列数
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetNum">工作表索引，以0开始</param>
/// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param>
/// <param name="endRowNum">结束行号</param>
/// <param name="startColNum">起始列号</param>
/// <param name="endColNum">结束列号</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
int sheetCount = hssfworkbook.NumberOfSheets;
sheetNum = sheetNum < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum;
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum);
HSSFRow headerRow = null;
#region 行列号范围验证
startColNum = startColNum < 0 ? 0 : startColNum;
startRowNum = startRowNum < 1 ? 1 : startRowNum;
headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1);
endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum;
endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum;
#endregion
//添加列
for (int j = startColNum - 1; j < endColNum; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
//添加行
for (int i = startRowNum; i <= endRowNum - 1; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = startColNum - 1; j < endColNum; j++)
{
if (row.GetCell(j) != null)
dataRow[j - startColNum + 1] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
/**
* create a rectangle, save the workbook, read back and verify that all shape properties are there
*/
public void TestReadWriteRectangle()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch drawing = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short)2, 2, (short)4, 4);
anchor.AnchorType = (AnchorType)(2);
Assert.AreEqual(anchor.AnchorType, 2);
HSSFSimpleShape rectangle = drawing.CreateSimpleShape(anchor);
rectangle.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
rectangle.LineWidth = (10000);
rectangle.FillColor = (777);
Assert.AreEqual(rectangle.FillColor, 777);
Assert.AreEqual(10000, rectangle.LineWidth);
rectangle.LineStyle = (LineStyle)(10);
Assert.AreEqual(10, rectangle.LineStyle);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_SQUARE);
rectangle.LineStyleColor = (1111);
rectangle.IsNoFill = (true);
rectangle.WrapText = (HSSFSimpleShape.WRAP_NONE);
rectangle.String = (new HSSFRichTextString("teeeest"));
Assert.AreEqual(rectangle.LineStyleColor, 1111);
Assert.AreEqual(((EscherSimpleProperty)((EscherOptRecord)HSSFTestHelper.GetEscherContainer(rectangle).GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.TEXT__TEXTID)).PropertyValue, "teeeest".GetHashCode());
Assert.AreEqual(rectangle.IsNoFill, true);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
Assert.AreEqual(rectangle.String.String, "teeeest");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, drawing.Children.Count);
HSSFSimpleShape rectangle2 =
(HSSFSimpleShape)drawing.Children[0];
Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE,
rectangle2.ShapeType);
Assert.AreEqual(10000, rectangle2.LineWidth);
Assert.AreEqual(10, (int)rectangle2.LineStyle);
Assert.AreEqual(anchor, rectangle2.Anchor);
Assert.AreEqual(rectangle2.LineStyleColor, 1111);
Assert.AreEqual(rectangle2.FillColor, 777);
Assert.AreEqual(rectangle2.IsNoFill, true);
Assert.AreEqual(rectangle2.String.String, "teeeest");
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
rectangle2.FillColor = (3333);
rectangle2.LineStyle = (LineStyle)(9);
rectangle2.LineStyleColor = (4444);
rectangle2.IsNoFill = (false);
rectangle2.LineWidth = (77);
rectangle2.Anchor.Dx1 = 2;
rectangle2.Anchor.Dx2 = 3;
rectangle2.Anchor.Dy1 = (4);
rectangle2.Anchor.Dy2 = (5);
rectangle.WrapText = (HSSFSimpleShape.WRAP_BY_POINTS);
rectangle2.String = (new HSSFRichTextString("test22"));
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, drawing.Children.Count);
rectangle2 = (HSSFSimpleShape)drawing.Children[0];
Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.ShapeType);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_BY_POINTS);
Assert.AreEqual(77, rectangle2.LineWidth);
Assert.AreEqual(9, rectangle2.LineStyle);
Assert.AreEqual(rectangle2.LineStyleColor, 4444);
Assert.AreEqual(rectangle2.FillColor, 3333);
Assert.AreEqual(rectangle2.Anchor.Dx1, 2);
Assert.AreEqual(rectangle2.Anchor.Dx2, 3);
Assert.AreEqual(rectangle2.Anchor.Dy1, 4);
Assert.AreEqual(rectangle2.Anchor.Dy2, 5);
Assert.AreEqual(rectangle2.IsNoFill, false);
Assert.AreEqual(rectangle2.String.String, "test22");
HSSFSimpleShape rect3 = drawing.CreateSimpleShape(new HSSFClientAnchor());
rect3.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
drawing = (wb.GetSheetAt(0) as HSSFSheet).DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(drawing.Children.Count, 2);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
public MemoryStream Export(DataTable dtSource, List<String> headerTextList)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
//设置Excel文件属性信息
SetFileProperty(workbook);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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 表头及样式
for (int i = 0; i < headerTextList.Count; i++)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
headerRow.HeightInPoints = 18;
headerRow.CreateCell(0).SetCellValue(headerTextList[i]);
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Left;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
//font.Boldweight = 700;
headerStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)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 = headerTextList.Count + 1;
}
#endregion
#region 填充表格内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)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;
return ms;
}
}
public void TestRemoveShapes()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor());
rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG);
patriarch.CreatePicture(new HSSFClientAnchor(), idx);
patriarch.CreateCellComment(new HSSFClientAnchor());
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 });
patriarch.CreateTextbox(new HSSFClientAnchor());
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
group.CreateTextbox(new HSSFChildAnchor());
group.CreatePicture(new HSSFChildAnchor(), idx);
Assert.AreEqual(patriarch.Children.Count, 6);
Assert.AreEqual(group.Children.Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 6);
group = (HSSFShapeGroup)patriarch.Children[5];
group.RemoveShape(group.Children[0]);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
group = (HSSFShapeGroup)patriarch.Children[(5)];
patriarch.RemoveShape(group);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 5);
HSSFShape shape = patriarch.Children[0];
patriarch.RemoveShape(shape);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 4);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 4);
HSSFPicture picture = (HSSFPicture)patriarch.Children[0];
patriarch.RemoveShape(picture);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 3);
HSSFComment comment = (HSSFComment)patriarch.Children[0];
patriarch.RemoveShape(comment);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 2);
polygon = (HSSFPolygon)patriarch.Children[0];
patriarch.RemoveShape(polygon);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 1);
HSSFTextbox textbox = (HSSFTextbox)patriarch.Children[0];
patriarch.RemoveShape(textbox);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 0);
}
/// <summary>
/// 导出加工工艺表
/// </summary>
/// <param name="partIds"></param>
/// <returns></returns>
public virtual async Task <string> ExportPartProcessInfo(int[] partIds)
{
var parts = await Manager.GetAll()
.Include(o => o.Project).ThenInclude(o => o.Unit)
.Include("ProcessTasks.ProcessType")
.Where(o => partIds.Contains(o.Id))
.Select(o => new {
o.Project.ProjectSN,
o.Project.ProjectName,
ProjectCharger = o.Project.GetPropertyValue <string>("ProjectCharger"),
UnitName = o.Project.Unit != null?o.Project.Unit.UnitName:"",
o.PartName,
o.PartImg,
o.PartSpecification,
Tasks = o.ProcessTasks.Select(t => new {
t.ProcessType.ProcessTypeName,
t.AppointDate,
t.RequireDate,
t.StartDate,
t.EndDate
})
})
.ToListAsync();
#region 导出方法
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("模具制作工艺单");
sheet.DefaultColumnWidth = 300 * 256;//貌似没有作用
var rowIndex = 0;
var cellIndex = 0;
//模具信息行
IRow row = sheet.CreateRow(rowIndex++);
parts.ForEach(o => {
row.CreateCell(cellIndex++).SetCellValue(o.ProjectSN);
row.CreateCell(cellIndex++).SetCellValue(o.ProjectName);
row.CreateCell(cellIndex++).SetCellValue(o.ProjectCharger);
row.CreateCell(cellIndex++).SetCellValue(o.UnitName);
});
//零件信息行
row = sheet.CreateRow(rowIndex++);
cellIndex = 0;
parts.ForEach(o => {
row.CreateCell(cellIndex++).SetCellValue(o.PartName);
row.CreateCell(cellIndex++).SetCellValue(o.PartSpecification);
row.CreateCell(cellIndex++).SetCellValue("");
row.CreateCell(cellIndex++).SetCellValue("");
});
//图片行
row = sheet.CreateRow(rowIndex++);
row.Height = 80 * 20;
cellIndex = 0;
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
parts.ForEach(o => {
sheet.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, cellIndex, cellIndex + 3));
if (!string.IsNullOrEmpty(o.PartImg))
{
try
{
byte[] bytes = System.IO.File.ReadAllBytes(Common.PathHelper.VirtualPathToAbsolutePath(o.PartImg));
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cellIndex, rowIndex - 1, cellIndex + 4, rowIndex);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch (Exception ex)
{
}
}
cellIndex += 4;
});
//工序表头
row = sheet.CreateRow(rowIndex++);
cellIndex = 0;
parts.ForEach(o =>
{
row.CreateCell(cellIndex++).SetCellValue("序号");
row.CreateCell(cellIndex++).SetCellValue("加工流程");
row.CreateCell(cellIndex++).SetCellValue("计划时间");
row.CreateCell(cellIndex++).SetCellValue("实际时间");
});
//工序内容
var maxProcessTypeCount = parts.Max(o => o.Tasks.Count());
for (var i = 0; i < maxProcessTypeCount; i++)
{
//先构建下面所有行
sheet.CreateRow(rowIndex + i);
}
cellIndex = 0;
for (var i = 0; i < parts.Count; i++)
{
var part = parts[i];
for (var j = 0; j < part.Tasks.Count(); j++)
{
var task = part.Tasks.ElementAt(j);
row = sheet.GetRow(rowIndex + j);
row.CreateCell(i * 4).SetCellValue(j + 1);
row.CreateCell(i * 4 + 1).SetCellValue(task.ProcessTypeName);
row.CreateCell(i * 4 + 2).SetCellValue(task.AppointDate?.ToString("MM-dd") + "-" + task.RequireDate?.ToString("MM-dd"));
row.CreateCell(i * 4 + 3).SetCellValue(task.StartDate?.ToString("MM-dd") + "-" + task.EndDate?.ToString("MM-dd"));
}
}
//边框
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int j = 0; j <= row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell != null)
{
cell.CellStyle = style;
}
}
}
#endregion
var filePath = $"/Temp/{Guid.NewGuid()}.xls";
System.IO.Directory.CreateDirectory(Common.PathHelper.VirtualPathToAbsolutePath("/Temp/"));
using (var fs = new FileStream(Common.PathHelper.VirtualPathToAbsolutePath(filePath), FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(fs); //写入到excel
}
workbook = null;
return(filePath);
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
for (int i = 21; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveExFormatRecord(
xfrs[i]
);
}
}
// Finally, update the cells to point at
// their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource"></param>
/// <param name="dateFormat"></param>
/// <returns></returns>
private static MemoryStream DataTableToExcel(DataTable dtSource, string dateFormat)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
//取得列宽
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
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 = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle.Alignment = HorizontalAlignment.Center;
newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
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);
}
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4)
{
continue;
}
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4)
{
continue;
}
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
Zephyr.Utils.NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
if (cell.CellType == Zephyr.Utils.NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}
public static HSSFWorkbook ExportToExcel(DataTable dt)
{
HSSFWorkbook workbook = new HSSFWorkbook();
if (dt.Rows.Count > 0)
{
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 = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//using (MemoryStream ms = new MemoryStream())
//{
// workbook.Write(ms);
// HttpContext curContext = HttpContext.Current;
// // 设置编码和附件格式
// curContext.Response.ContentType = "application/vnd.ms-excel";
// curContext.Response.ContentEncoding = Encoding.UTF8;
// curContext.Response.Charset = "";
// curContext.Response.AppendHeader("Content-Disposition",
// "attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
// curContext.Response.BinaryWrite(ms.GetBuffer());
// workbook = null;
// ms.Close();
// ms.Dispose();
// curContext.Response.End();
//}
}
return(workbook);
}
public static void OutputExcel(DataGridView dgv, string Title, string savePath, string sSTName, bool IsConver)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
{
int num = 5;
int num2 = 0x106;
DocumentSummaryInformation information = PropertySetFactory.CreateDocumentSummaryInformation();
information.Company = "";
workbook.DocumentSummaryInformation = information;
SummaryInformation information2 = PropertySetFactory.CreateSummaryInformation();
information2.Subject = "";
workbook.SummaryInformation = information2;
if (sSTName.Length > 0x1f)
{
sSTName = "报表";
}
ISheet sheet = workbook.CreateSheet(sSTName);
int rownum = 3;
int num4 = 0;
int num5 = 0;
int num6 = 1;
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.ShrinkToFit = true;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.CreateRow(rownum);
num5 = num4;
foreach (DataGridViewColumn column in dgv.Columns)
{
if (column.Visible && (column.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
ICell cell = row.CreateCell(num5);
cell.SetCellValue(column.HeaderText);
cell.CellStyle = style;
style.WrapText = true;
sheet.AutoSizeColumn(num5);
}
}
if (IsConver)
{
for (int i = dgv.Rows.Count - 1; i >= 0; i--)
{
DataGridViewRow row2 = dgv.Rows[i];
if (!row2.IsNewRow)
{
rownum++;
num5 = num4;
IRow row3 = sheet.CreateRow(rownum);
foreach (DataGridViewColumn column2 in dgv.Columns)
{
if (column2.Visible && (column2.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
if ((row2.Cells[column2.Index].Value != null) && !row2.IsNewRow)
{
row3.CreateCell(num5).SetCellValue(row2.Cells[column2.Index].Value.ToString());
int num8 = sheet.GetColumnWidth(row3.GetCell(num5).ColumnIndex);
int num9 = (Encoding.GetEncoding("gb2312").GetBytes(row2.Cells[column2.Index].Value.ToString()).Length + num) * num2;
num8 = (num9 < num8) ? num8 : num9;
sheet.SetColumnWidth(num5, num8);
if (column2.CellType == typeof(DataGridViewTextBoxCell))
{
sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
}
}
}
}
}
}
}
else
{
foreach (DataGridViewRow row4 in (IEnumerable)dgv.Rows)
{
if (!row4.IsNewRow)
{
rownum++;
num5 = num4;
IRow row5 = sheet.CreateRow(rownum);
foreach (DataGridViewColumn column3 in dgv.Columns)
{
if (column3.Visible && (column3.CellType != typeof(DataGridViewCheckBoxCell)))
{
num5++;
if ((row4.Cells[column3.Index].Value != null) && !row4.IsNewRow)
{
row5.CreateCell(num5).SetCellValue(row4.Cells[column3.Index].Value.ToString());
int num10 = sheet.GetColumnWidth(row5.GetCell(num5).ColumnIndex);
int num11 = (Encoding.GetEncoding("gb2312").GetBytes(row4.Cells[column3.Index].Value.ToString()).Length + num) * num2;
num10 = (num11 < num10) ? num10 : num11;
sheet.SetColumnWidth(num5, num10);
if (column3.CellType == typeof(DataGridViewTextBoxCell))
{
sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
}
}
}
}
continue;
}
}
}
int num12 = rownum + 1;
num5 = num4;
foreach (DataGridViewColumn column4 in dgv.Columns)
{
if (column4.Visible)
{
num5++;
}
}
num12 = rownum + 2;
IRow row6 = sheet.CreateRow(num12);
ICell cell2 = row6.CreateCell(1);
cell2.SetCellValue("合计:");
ICellStyle style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style2.VerticalAlignment = VerticalAlignment.CENTER;
cell2.CellStyle = style2;
int columnWidth = sheet.GetColumnWidth(1);
int num14 = (Encoding.GetEncoding("gb2312").GetBytes("合计:").Length + num) * num2;
columnWidth = (num14 < columnWidth) ? columnWidth : num14;
sheet.SetColumnWidth(1, columnWidth);
cell2 = row6.CreateCell(2);
cell2.SetCellValue(dgv.Rows.Count.ToString() + "条记录");
style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
style2.VerticalAlignment = VerticalAlignment.CENTER;
cell2.CellStyle = style2;
int width = sheet.GetColumnWidth(2);
int num16 = (Encoding.GetEncoding("gb2312").GetBytes(dgv.Rows.Count.ToString() + "条记录").Length + num) * num2;
width = (num16 < columnWidth) ? width : num16;
sheet.SetColumnWidth(2, width);
num6 = 1;
ICell cell3 = sheet.CreateRow(1).CreateCell(num6);
cell3.SetCellValue(Title);
ICellStyle style3 = workbook.CreateCellStyle();
style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style3.VerticalAlignment = VerticalAlignment.CENTER;
NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
font2.FontName = "宋体";
font2.FontHeightInPoints = 11;
style3.SetFont(font2);
cell3.CellStyle = style3;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, num6, num5));
sheet.GetRow(1).HeightInPoints = 35f;
if (string.IsNullOrEmpty(savePath))
{
savePath = @"C:\Documents and Settings\Administrator\My Documents\Report.xls";
}
FileStream stream = new FileStream(savePath, FileMode.Create);
workbook.Write(stream);
stream.Close();
MessageBox.Show("数据已经成功导出到：" + savePath, "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
Record.execFileRecord("导出报表", exception.ToString());
}
}
public ActionResult Torg12(string List)
{
var stream = new MemoryStream();
var ids = List.Split <int>(";");
var orders = DB.Orders.Where(x => ids.Contains(x.ID) && x.Shop != null && x.Shop.Owner == CurrentUser.ShopOwnerID).ToList();
var workbook = new HSSFWorkbook();
using (var fs = new FileStream(Server.MapPath("~/Content/TORG12.xls"), FileMode.Open))
{
var example = new HSSFWorkbook(fs);
int index = 0;
foreach (var order in orders)
{
var sheet = (HSSFSheet)example.GetSheetAt(0);
sheet.CopyTo(workbook, "Заказ №" + order.OrderNumberOrID.Replace("'", "").Replace("[", "@").Replace("]", "@"), true, false);
var copied = (HSSFSheet)workbook.GetSheetAt(index);
copied.GetRow(2).GetCell(1).SetCellValue(order.Requisites);
copied.GetRow(9).GetCell(3).SetCellValue(order.Requisites);
copied.GetRow(7).GetCell(3).SetCellValue(order.Receiver);
copied.GetRow(11).GetCell(3).SetCellValue(order.Receiver);
copied.GetRow(3).GetCell(37).SetCellValue(order.Shop.GetSetting("OKPO"));
copied.GetRow(8).GetCell(37).SetCellValue(order.Shop.GetSetting("OKPO"));
copied.GetRow(16).GetCell(10).SetCellValue(order.OrderNumberOrID);
copied.GetRow(16).GetCell(14).SetCellValue(DateTime.Now.ToString("dd.MM.yyyy"));
if (order.OrderedProducts.Count > 1)
{
for (int i = 1; i < order.OrderedProducts.Count; i++)
{
copied.GetRow(22).CopyRowTo(23);
}
}
int oi = 0;
foreach (var product in order.OrderedProducts)
{
var row = copied.GetRow(22 + oi);
row.GetCell(1).SetCellValue(oi + 1);
row.GetCell(2).SetCellValue(product.Product.Name);
row.GetCell(6).SetCellValue(product.Product.Article);
row.GetCell(7).SetCellValue(product.Product.AmountUnitName);
row.GetCell(11).SetCellValue(product.Product.UnitCode);
row.GetCell(21).SetCellValue(product.Amount);
row.GetCell(23).SetCellValue((product.PriceWithoutNDS).ToString("f2"));
row.GetCell(25).SetCellValue((product.PriceWithoutNDS * product.Amount).ToString("f2"));
row.GetCell(30).SetCellValue("18%");
row.GetCell(33).SetCellValue((product.NDS * product.Amount).ToString("f2"));
row.GetCell(36).SetCellValue((product.Price * product.Amount).ToString("f2"));
oi++;
}
var rws1 = copied.GetRow(22 + oi);
var rws2 = copied.GetRow(22 + oi + 1);
rws1.GetCell(21).SetCellValue(order.OrderedProducts.Sum(x => x.Amount));
rws2.GetCell(21).SetCellValue(order.OrderedProducts.Sum(x => x.Amount));
rws1.GetCell(23).SetCellValue("X");
rws2.GetCell(23).SetCellValue("X");
rws1.GetCell(25).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.PriceWithoutNDS).ToString("f2"));
rws2.GetCell(25).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.PriceWithoutNDS).ToString("f2"));
rws1.GetCell(30).SetCellValue("X");
rws2.GetCell(30).SetCellValue("X");
rws1.GetCell(33).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.NDS).ToString("f2"));
rws2.GetCell(33).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.NDS).ToString("f2"));
rws1.GetCell(36).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.Price).ToString("f2"));
rws2.GetCell(36).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.Price).ToString("f2"));
copied.GetRow(22 + oi + 3)
.GetCell(5)
.SetCellValue(Russian.ToString(order.OrderedProducts.Count, Gender.Masculine));
var all = order.OrderedProducts.Sum(x => x.Price * x.Amount);
var allStr = Russian.ToString((int)all, Gender.Masculine, true) + " рублей " +
((int)((all - (int)all) * 100)).ToString("d2") + " копеек";
copied.GetRow(22 + oi + 13)
.GetCell(1)
.SetCellValue(allStr);
index++;
}
workbook.Write(stream);
}
return(File(stream.ToArray(), MIMETypeWrapper.GetMIME("xls"),
"Torg12_" + List.Trim(';').Replace(";", "_") + ".xls"));
}
public void TestSetGetProperties()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox.String = (new HSSFRichTextString("test"));
Assert.AreEqual(textbox.String.String, "test");
textbox.HorizontalAlignment = ((HorizontalAlignment)5);
Assert.AreEqual((HorizontalAlignment)5, textbox.HorizontalAlignment);
textbox.VerticalAlignment = ((VerticalAlignment)6);
Assert.AreEqual((VerticalAlignment)6, textbox.VerticalAlignment);
textbox.MarginBottom = (7);
Assert.AreEqual(textbox.MarginBottom, 7);
textbox.MarginLeft = (8);
Assert.AreEqual(textbox.MarginLeft, 8);
textbox.MarginRight = (9);
Assert.AreEqual(textbox.MarginRight, 9);
textbox.MarginTop = (10);
Assert.AreEqual(textbox.MarginTop, 10);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test");
Assert.AreEqual(textbox.HorizontalAlignment, (HorizontalAlignment)5);
Assert.AreEqual(textbox.VerticalAlignment, (VerticalAlignment)6);
Assert.AreEqual(textbox.MarginBottom, 7);
Assert.AreEqual(textbox.MarginLeft, 8);
Assert.AreEqual(textbox.MarginRight, 9);
Assert.AreEqual(textbox.MarginTop, 10);
textbox.String = (new HSSFRichTextString("test1"));
textbox.HorizontalAlignment = HorizontalAlignment.Center;
textbox.VerticalAlignment = VerticalAlignment.Top;
textbox.MarginBottom = (71);
textbox.MarginLeft = (81);
textbox.MarginRight = (91);
textbox.MarginTop = (101);
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HorizontalAlignment.Center);
Assert.AreEqual(textbox.VerticalAlignment, VerticalAlignment.Top);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HorizontalAlignment.Center);
Assert.AreEqual(textbox.VerticalAlignment, VerticalAlignment.Top);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
}
public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
{
IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();
using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
Where = GetWhereCondition(model)
};
MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lst = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ICell cell = null;
IRow row = null;
ISheet ws = null;
for (int j = 0; j < lst.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
row = ws.CreateRow(0);
#region //列名
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料名称"); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("供应商名称"); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("效率档"); //效率档
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("描述"); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑人"); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑时间"); //编辑时间
#endregion
font.Boldweight = 5;
}
MaterialReceiptDetail obj = lst[j];
MaterialReceipt mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
Material m = model.GetMaterial(obj.MaterialCode);
Supplier s = model.GetSupplier(obj.SupplierCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate)); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Attr1); //效率档
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Description); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Editor); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelExtractor"/> class.
/// </summary>
/// <param name="wb">The wb.</param>
public ExcelExtractor(HSSFWorkbook wb)
: base(wb)
{
this.wb = wb;
_formatter = new HSSFDataFormatter();
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">必须是有构造函数的实体类</typeparam>
/// <param name="dtSource"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="writeColumnName"></param>
/// <param name="excelType"></param>
public static void WriteStringsInToExcel(this IEnumerable <string> dtSource, string fileName, string sheetName, bool writeColumnName = true, string columnName = "Null Column Title", ExcelType excelType = ExcelType.XLS)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
try
{
#region 初始化
if (excelType == ExcelType.XLS)
{
workbook = new HSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xls";
}
}
else
{
workbook = new XSSFWorkbook();
if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
{
fileName = fileName + ".xlsx";
}
}
if (workbook != null)
{
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
#endregion
var list = dtSource.ToList();
#region 写列名称和内容
if (writeColumnName)
{
list.Insert(0, columnName);
}
var rowBeginIndex = 0;
list.ForEach(x => {
IRow row = sheet.CreateRow(rowBeginIndex);
var cell = row.CreateCell(0, CellType.String);
cell.CellStyle.WrapText = true;
if (rowBeginIndex == 0 && writeColumnName)
{
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中，有边框
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.FillForegroundColor = HSSFColor.White.Index;
style.FillBackgroundColor = HSSFColor.Red.Index; //指定背景颜色
//将新的样式赋给单元格
cell.CellStyle = style;
}
cell.SetCellValue(x);
rowBeginIndex++;
});
#endregion
workbook.Write(fs); //写入到excel
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Dispose();
fs.Close();
}
}
//Datatable导出Excel
public static void DatatableToExcelByNPOI(DataTable dt, string strExcelFileName)
{
HSSFWorkbook workbook = null;
try
{
workbook = new HSSFWorkbook();
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);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
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();
//MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//ILog log = LogManager.GetLogger("Exception Log");
_logger.Error(ex.Message + Environment.NewLine + ex.StackTrace);
//记录AuditTrail
//CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);
//MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally { workbook = null; }
}
public byte[] Export(DataTable dt, IDictionary<string, string> columns)
{
//HttpResponse rsp = HttpContext.Current.Response;
HSSFWorkbook wb = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = wb.CreateSheet("Sheet1");
//填充表头
IRow dataRow = sheet.CreateRow(0);
bool isReColumn = !(columns == null || columns.Count == 0);
int tmpInt = 0;
if (isReColumn)
{
foreach (string columnName in columns.Keys)
{
dataRow.CreateCell(tmpInt++).SetCellValue(columns[columnName]);
}
}
else
{
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(tmpInt++).SetCellValue(column.ColumnName);
}
}
//填充内容
for (int i = 0; i < dt.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
if (isReColumn)
{
tmpInt = 0;
foreach (string columnName in columns.Keys)
{
dataRow.CreateCell(tmpInt++).SetCellValue(dt.Rows[i][columnName].ToString());
}
}
else
{
for (int j = 0; j < dt.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
wb.Write(ms);
//rsp.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
//rsp.BinaryWrite(ms.ToArray());
wb = null;
ms.Close();
byte[] bytes = ms.ToArray();
ms.Dispose();
return bytes;
}
public static DataTable ExcelToDataTable(string path)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
DataTable table = new DataTable();
DataRow dataRow = null;
try
{
using (FileStream fs = File.OpenRead(path))
{
// Path.GetExtension(path) 获取文件的后缀名
if (Path.GetExtension(path) == ".xlsx")
{
//从流中获取excel 工作簿
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
//从工作簿里获取指定的表
sheet = workbook.GetSheetAt(0);
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
//根据excel表里的列数为datatable创建列
table.Columns.Add(new DataColumn());
}
//根据表格的行数进行循环创建dataRow
for (int i = 1; i <= sheet.LastRowNum; i++)
{
//为datatable创建行
dataRow = table.NewRow();
//从excel表中获取指定的行
row = sheet.GetRow(i);
for (int j = 0; j < row.LastCellNum; j++)
{
//从行里获取单元格
cell = row.GetCell(j);
switch (cell.CellType)
{
//如果excel单元格内的值的数据类型为 数值
case CellType.Numeric:
//将excel表中单元格的值赋给datatable行中的某列
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
case CellType.Blank:
dataRow[j] = "";
break;
default:
throw new Exception("格式错误");
}
}
//将创建的行添加到datatable的rows（行集合）
table.Rows.Add(dataRow);
}
//返回结果
return(table);
}
}
catch (Exception ex)
{
throw ex;
}
}
///// <summary>
///// 给指定单元格写入内容
///// </summary>
///// <param name="workBook"></param>
///// <param name="sheetName"></param>
///// <param name="rowNum"></param>
///// <param name="colNum"></param>
///// <param name="content"></param>
//public void WriteCell(HSSFWorkbook workBook, string sheetName, int rowNum, int colNum, string content)
//{
// if (workBook == null)
// {
// throw new Exception("workBook不能为null");
// }
// WriteCell(workBook, workBook.GetSheetIndex(sheetName), rowNum, colNum, content);
//}
///// <summary>
///// 给指定单元格写入内容
///// </summary>
///// <param name="workBook"></param>
///// <param name="sheetNum"></param>
///// <param name="rowNum"></param>
///// <param name="colNum"></param>
///// <param name="content"></param>
///// <returns></returns>
//public void WriteCell(HSSFWorkbook workBook, int sheetNum, int rowNum, int colNum, string content)
//{
// if (workBook == null)
// {
// throw new Exception("workBook不能为null");
// }
// if (workBook.NumberOfSheets < sheetNum || sheetNum < 0)
// {
// throw new Exception("指定的sheet不存在");
// }
// ISheet sheet = workBook.GetSheetAt(sheetNum - 1);
// HSSFRow row = (HSSFRow)sheet.GetRow(rowNum) ?? (HSSFRow)sheet.CreateRow(rowNum - 1);
// HSSFCell cell = (HSSFCell)row.CreateCell(6);
// cell.SetCellValue(content);
// //using (MemoryStream ms = new MemoryStream())
// //{
// // workBook.Write(ms);
// // ms.Flush();
// // ms.Position = 0;
// // using (FileStream fs = new FileStream("测试行列写入.xls", FileMode.Create, FileAccess.Write))
// // {
// // byte[] data = ms.ToArray();
// // fs.Write(data, 0, data.Length);
// // fs.Flush();
// // }
// //}
// //return workBook;
//}
#endregion
/// <summary>
/// 设置Excel文件属性信息
/// </summary>
/// <param name="workbook"></param>
private void SetFileProperty(HSSFWorkbook workbook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = this.Company;
dsi.Scale = this.Scale;
dsi.Manager = this.Manager;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = this.Author;
si.ApplicationName = this.ApplicationName;
si.LastAuthor = this.LastAuthor;
si.Comments = this.Comments;
si.Title = this.Title;
si.Subject = this.Subject;
si.CreateDateTime = this.CreateDateTime ?? DateTime.Now;
si.Keywords = this.Keywords;
si.LastAuthor = this.LastAuthor;
si.LastPrinted = this.LastPrinted;
si.LastSaveDateTime = this.LastSaveDateTime ?? DateTime.Now;
workbook.SummaryInformation = si;
}
void ReadFile(string filePath, string fileExtension)
{
StringBuilder sb = new StringBuilder();
ISheet sheet;
using (var stream = new FileStream(filePath, FileMode.Open))
{
stream.Position = 0;
if (fileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
this.Header.Add(cell.ToString());
}
//for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
int rowId = 1;
for (int i = sheet.LastRowNum; i > (sheet.FirstRowNum + 1); i--) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
TransactionExtended tr = new TransactionExtended();
tr.Id = rowId;
tr.AccountingDate = row.GetCell(0).DateCellValue;
tr.TransactionId = row.GetCell(1).ToString();
tr.Type = row.GetCell(2).ToString();
tr.Account = row.GetCell(3).ToString();
tr.AccountName = row.GetCell(4).ToString();
tr.PartnerAccount = row.GetCell(5).ToString();
tr.PartnerName = row.GetCell(6).ToString();
tr.Sum = decimal.Parse(row.GetCell(7).ToString());
tr.Currency = row.GetCell(7).ToString();
tr.Message = row.GetCell(9).ToString();
rowId++;
}
}
}
/**
* Creates a bar chart. API needs some work. :)
* <p>
* NOTE: Does not yet work... checking it in just so others
* can take a look.
*/
public void CreateBarChart( HSSFWorkbook workbook, HSSFSheet sheet )
{
List<Record> records = new List<Record>();
records.Add( CreateMSDrawingObjectRecord() );
records.Add( CreateOBJRecord() );
records.Add( CreateBOFRecord() );
records.Add(new HeaderRecord(""));
records.Add(new FooterRecord(""));
records.Add( CreateHCenterRecord() );
records.Add( CreateVCenterRecord() );
records.Add( CreatePrintSetupRecord() );
// unknown 33
records.Add( CreateFontBasisRecord1() );
records.Add( CreateFontBasisRecord2() );
records.Add(new ProtectRecord(false));
records.Add( CreateUnitsRecord() );
records.Add( CreateChartRecord( 0, 0, 30434904, 19031616 ) );
records.Add( CreateBeginRecord() );
records.Add( CreateSCLRecord( (short) 1, (short) 1 ) );
records.Add( CreatePlotGrowthRecord( 65536, 65536 ) );
records.Add( CreateFrameRecord1() );
records.Add( CreateBeginRecord() );
records.Add( CreateLineFormatRecord(true) );
records.Add( CreateAreaFormatRecord1() );
records.Add( CreateEndRecord() );
records.Add( CreateSeriesRecord() );
records.Add( CreateBeginRecord() );
records.Add( CreateTitleLinkedDataRecord() );
records.Add( CreateValuesLinkedDataRecord() );
records.Add( CreateCategoriesLinkedDataRecord() );
records.Add( CreateDataFormatRecord() );
// records.add(createBeginRecord());
// unknown
// records.add(createEndRecord());
records.Add( CreateSeriesToChartGroupRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateSheetPropsRecord() );
records.Add( CreateDefaultTextRecord( DefaultDataLabelTextPropertiesRecord.CATEGORY_DATA_TYPE_ALL_TEXT_CHARACTERISTIC ) );
records.Add( CreateAllTextRecord() );
records.Add( CreateBeginRecord() );
// unknown
records.Add( CreateFontIndexRecord( 5 ) );
records.Add( CreateDirectLinkRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateDefaultTextRecord( (short) 3 ) ); // eek, undocumented text type
records.Add( CreateUnknownTextRecord() );
records.Add( CreateBeginRecord() );
records.Add( CreateFontIndexRecord( (short) 6 ) );
records.Add( CreateDirectLinkRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateAxisUsedRecord( (short) 1 ) );
CreateAxisRecords( records );
records.Add( CreateEndRecord() );
records.Add( CreateDimensionsRecord() );
records.Add( CreateSeriesIndexRecord(2) );
records.Add( CreateSeriesIndexRecord(1) );
records.Add( CreateSeriesIndexRecord(3) );
records.Add(EOFRecord.instance);
sheet.InsertChartRecords( records );
workbook.InsertChartRecord();
}
protected void ExportExcel(IList <EDIFordPlan> exportList)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
MemoryStream output = new MemoryStream();
if (exportList != null && exportList.Count > 0)
{
var groups = (from tak in exportList
group tak by tak.Control_Num into result
select new
{
Control_Num = result.Key,
List = result.ToList()
}).ToList();
for (int ti = 0; ti < groups.Count(); ti++)
{
var g = groups[ti];
Sheet sheet1 = hssfworkbook.CreateSheet(g.Control_Num);
IList <DateTime> dateListDic = g.List.OrderBy(l => l.ForecastDate).Select(l => l.ForecastDate).Distinct().ToList();
#region 写入字段
Row rowHeader = sheet1.CreateRow(0);
for (int i = 0; i < 6 + dateListDic.Count; i++)
{
if (i == 0) //版本号
{
rowHeader.CreateCell(i).SetCellValue("版本号");
}
else if (i == 1) //文件发布日期
{
rowHeader.CreateCell(i).SetCellValue("文件发布日期");
}
else if (i == 2) //物料号
{
rowHeader.CreateCell(i).SetCellValue("物料号");
}
else if (i == 3) //物料描述
{
rowHeader.CreateCell(i).SetCellValue("物料描述");
}
else if (i == 4) //福特物料号
{
rowHeader.CreateCell(i).SetCellValue("福特物料号");
}
else if (i == 5) //单位
{
rowHeader.CreateCell(i).SetCellValue("单位");
}
else
{
foreach (var date in dateListDic)
{
rowHeader.CreateCell(i++).SetCellValue(date.ToShortDateString());
}
}
}
#endregion
#region 写入数值
//Caption.Visible = true;
var groupByRefItem = (from tak in g.List
group tak by tak.RefItem into result
select new
{
RefItem = result.Key,
List = result.ToList()
}).ToList();
IList <FlowDetail> flowdets = TheGenericMgr.FindAllWithCustomQuery <FlowDetail>(string.Format(" select d from FlowDetail as d where d.ReferenceItemCode in('{0}') ", string.Join("','", g.List.Select(w => w.RefItem).Distinct().ToArray())));
int j = 1;
foreach (var d in groupByRefItem)
{
Row rowDetail = sheet1.CreateRow(j);
Row rowDetail2 = sheet1.CreateRow(j + 1);
EDIFordPlan newPlan = d.List.First();
if (flowdets != null && flowdets.Count > 0)
{
var flowDet = flowdets.Where(f => f.ReferenceItemCode == newPlan.RefItem);
if (flowDet != null && flowDet.Count() > 0)
{
newPlan.Item = flowDet.First().Item.Code;
newPlan.ItemDesc = flowDet.First().Item.Description;
}
}
rowDetail.CreateCell(0).SetCellValue(newPlan.Control_Num);
rowDetail.CreateCell(1).SetCellValue(newPlan.ReleaseIssueDate.ToShortDateString());
rowDetail.CreateCell(2).SetCellValue(newPlan.Item);
rowDetail.CreateCell(3).SetCellValue(newPlan.ItemDesc);
rowDetail.CreateCell(4).SetCellValue(newPlan.RefItem);
rowDetail.CreateCell(5).SetCellValue(newPlan.Uom);
int cell = 0;
foreach (var f in d.List.OrderBy(o => o.ForecastDate))
{
cell++;
//rowDetail.CreateCell(5 + cell).
var createCell = rowDetail.CreateCell(5 + cell);
createCell.SetCellType(CellType.NUMERIC);
createCell.SetCellValue(Convert.ToDouble(f.ForecastQty > 0 ? f.ForecastQty : 0));
var createCell2 = rowDetail2.CreateCell(5 + cell);
createCell2.SetCellType(CellType.NUMERIC);
createCell2.SetCellValue(Convert.ToDouble(f.ForecastCumQty > 0 ? f.ForecastCumQty : 0));
}
j += 2;
}
#endregion
}
hssfworkbook.Write(output);
string filename = "FordEdiPlan.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
Response.BinaryWrite(output.GetBuffer());
Response.End();
//return File(output, contentType, exportName + "." + fileSuffiex);
}
}
