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;
}
/// <summary>
/// 导出
/// </summary>
public override byte[] Export(List <SearchWordConvertMapDb> list, Dictionary <string, object> extra = null)
{
//创建Excel文件的对象
var wb = new HSSFWorkbook();
//添加一个sheet
var sheet = wb.CreateSheet("二级车型配置表");
//给sheet1添加第一行的头部标题
var row = sheet.CreateRow(0);
// 设置样式
var style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
// 设置字体
var font = wb.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "微软雅黑";
style.SetFont(font);
string[] title =
{
"关键词",
"同义词",
"是否删除",
"二级车型ID",
"排序",
"规格",
"特殊规格",
"二级车型名称"
};
for (var i = 0; i < title.Length; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(title[i]);
}
for (var i = 0; i < list.Count; i++)
{
var cell = sheet.CreateRow(i + 1);
cell.CreateCell(0).SetCellValue(list[i].TargetWord);
cell.CreateCell(1).SetCellValue(list[i].SourceWord);
cell.CreateCell(2).SetCellValue("1");
cell.CreateCell(3).SetCellValue(list[i].VehicleID);
cell.CreateCell(4).SetCellValue(list[i].Sort);
cell.CreateCell(5).SetCellValue(list[i].TireSize);
cell.CreateCell(6).SetCellValue(list[i].SpecialTireSize);
cell.CreateCell(7).SetCellValue(list[i].VehicleName);
}
// 写入到客户端
byte[] file;
using (var ms = new MemoryStream())
{
wb.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
file = ms.ToArray();
}
return(file);
}
public ActionResult Export_ToExcel()
{
var getPersonCities = ItemManager.Instance.GetPersonCities();
var workbook = new HSSFWorkbook();
//Create new Excel Sheet
var sheet = workbook.CreateSheet();
HSSFFont myFont = (HSSFFont)workbook.CreateFont();
myFont.FontHeightInPoints = (short)12;
myFont.FontName = "Times New Roman";
HSSFCellStyle borderedCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.WrapText = true;
borderedCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
borderedCellStyle.FillPattern = FillPattern.SolidForeground;
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
HSSFCellStyle borderedCellStyleContent = (HSSFCellStyle)workbook.CreateCellStyle();
borderedCellStyleContent.SetFont(myFont);
borderedCellStyleContent.WrapText = true;
//borderedCellStyleContent.Alignment = HorizontalAlignment.Center; //chieu ngang
borderedCellStyleContent.VerticalAlignment = VerticalAlignment.Center;
//(Optional) set the width of the columns
sheet.SetColumnWidth(0, 20 * 200);
sheet.SetColumnWidth(1, 20 * 200);
//sheet.SetColumnWidth(2, 20 * 200);
//sheet.SetDefaultColumnStyle(0, borderedCellStyle);
//sheet.SetDefaultColumnStyle(1, borderedCellStyle);
//sheet.SetDefaultColumnStyle(2, borderedCellStyle);
CreateCell(sheet.CreateRow(0), 0, "Column Value", borderedCellStyle);
IRow HeaderRow = sheet.CreateRow(0);
CreateCell(HeaderRow, 0, "CityName", borderedCellStyle);
CreateCell(HeaderRow, 1, "Gender", borderedCellStyle);
CreateCell(HeaderRow, 2, "Amount", borderedCellStyle);
//headerRow.CreateCell(0).SetCellValue("CityName");
//headerRow.CreateCell(1).SetCellValue("Gender");
//headerRow.CreateCell(2).SetCellValue("Amount");
//for (int c = 0; c < headerRow.Cells.Count; c++)
//{
// headerRow.Cells[c].CellStyle = borderedCellStyle;
//}
sheet.CreateFreezePane(0, 1, 0, 1);
var result = from cities in getPersonCities
group cities by cities.IdCity into c
select c.ToList();
int RowIndex = 1;
foreach (var objOrg in result)
{
int Number = objOrg.Count;
if (Number > 1)
{
int MergeIndex = (Number - 1) + RowIndex;
CellRangeAddress Merged = new CellRangeAddress(RowIndex, MergeIndex, 0, 0);
sheet.AddMergedRegion(Merged);
}
foreach (var objOrg1 in objOrg)
{
IRow row = sheet.CreateRow(RowIndex);
CreateCell(row, 0, objOrg1.CityName, borderedCellStyleContent);
CreateCell(row, 1, objOrg1.Gender, borderedCellStyleContent);
CreateCell(row, 2, objOrg1.Amount.ToString(), borderedCellStyleContent);
//row.CreateCell(0).SetCellValue(objOrg1.CityName);
//row.CreateCell(1).SetCellValue(objOrg1.Gender);
//row.CreateCell(2).SetCellValue(objOrg1.Amount);
RowIndex++;
//for (int c = 0; c < row.Cells.Count; c++)
//{
// row.Cells[c].CellStyle = borderedCellStyleContent;
//}
}
}
//Write the Workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"OrganizationList.xls"));
}
/// <summary>
/// DataTable 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="workbook">源 workbook</param>
/// <param name="dtSource">源 DataTable</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开，个数应与表相同)</param>
/// <returns></returns>
public static void ExportFromDSExcel(HSSFWorkbook workbook, DataTable dtSource, string strHeaderText)
{
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
ISheet sheet = workbook.CreateSheet(strHeaderText);
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int intTop = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表、填充表头、填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
intTop = 0;
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
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); // 设置设置列宽 太长会报错 修改2014 年9月22日
int dd = (arrColWidth[column.Ordinal] + 1) * 256;
if (dd > 200 * 256)
{
dd = 100 * 256;
}
sheet.SetColumnWidth(column.Ordinal, dd);
}
}
#endregion
rowIndex = intTop;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
else
{
newCell.SetCellValue(drValue);
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
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++;
}
}
/// <summary>
/// 导出到excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static MemoryStream ListToExcel <T>(List <T> data, string sheetName = "Sheet1") where T : class
{
var count = 0;
var columnIndex = 0;
var type = data.GetType();
var properties = type.GetGenericArguments()[0].GetProperties(); //获取T类型的所有属性
IWorkbook workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet(sheetName);
try
{
var rowHeader = sheet.CreateRow(count);
for (var n = 0; n < properties.Length; n++)
{
//sheet.AutoSizeColumn(n);//自动列宽
var ignore = ((ExcelExportIgnoreAttribute[])properties[n].GetCustomAttributes(typeof(ExcelExportIgnoreAttribute), false)).FirstOrDefault();
if (ignore != null)
{
//sheet.SetColumnHidden(n, true);
continue;
}
var displayName = ((DisplayNameAttribute[])properties[n].GetCustomAttributes(typeof(DisplayNameAttribute), false)).FirstOrDefault();
rowHeader.CreateCell(columnIndex).SetCellValue(displayName == null ? "无列名" : displayName.DisplayName);
columnIndex++;
}
count++;
for (var i = 0; i < data.Count; ++i)
{
var obj = data[i];
var row = sheet.CreateRow(count);
columnIndex = 0;
for (var j = 0; j < properties.Length; ++j)
{
var ignore = ((ExcelExportIgnoreAttribute[])properties[j].GetCustomAttributes(typeof(ExcelExportIgnoreAttribute), false)).FirstOrDefault();
if (ignore != null)
{
//sheet.SetColumnHidden(j, true);
continue;
}
var pValue = properties[j].GetValue(obj, null);
var cell = row.CreateCell(columnIndex);
cell.SetCellValue(pValue == null ? "" : pValue.ToString());
columnIndex++;
}
++count;
}
using (var ms = new MemoryStream())
{
workbook.Write(ms); //写入到内存流
ms.Flush();
ms.Position = 0;
return(ms);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">需要导出的泛型List</param>
/// <param name="strHeaderText">第一行标题头</param>
/// <param name="titleDictionaries">列名称字典映射</param>
/// <param name="title">todo: describe title parameter on ListToExcel</param>
/// <param name="titleDic">todo: describe titleDic parameter on ListToExcel</param>
/// <returns>内存流</returns>
private static MemoryStream ListToExcel <T>(List <T> list, string strHeaderText = null,
Dictionary <string, string> titleDic = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//设置文件属性
SetFileSummary(strHeaderText, workbook);
//获取列宽
int[] arrColWidth = GetColumnWidth(titleDic);
//日期风格
ICellStyle dateStyle = GetDateStyle(workbook);
//Excel标题风格
HSSFCellStyle headStyle = GetHeadStyle(workbook);
/*在第一行创建标题行*/
CreateHeadRow(titleDic, sheet, arrColWidth, headStyle);
//通过反射得到对象的属性集合
Type type = null;
if (list != null && list.Count > 0)
{
type = list.First().GetType();
for (int row = 0; row < list.Count; row++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row + 1);
int cellIndex = 0;
foreach (var dicItem in titleDic)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(cellIndex);
string drValue = string.Empty;
PropertyInfo propInfo = type.GetProperty(dicItem.Key);
var propValue = type.GetProperty(dicItem.Key).GetValue(list[row]);
if (propValue != null)
{
drValue = propValue.ToString();
}
SetCellValueByType(newCell, drValue, propInfo, dateStyle);
cellIndex = cellIndex + 1;
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook.Close();
return(ms);
}
}
public async Task <ActionResult> ExportToExcel(LineStoreMaterialDetailQueryViewModel model)
{
IList <LineStoreMaterialDetail> lst = new List <LineStoreMaterialDetail>();
using (LineStoreMaterialServiceClient client = new LineStoreMaterialServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc",
Where = GetDetailWhereCondition(model)
};
MethodReturnResult <IList <LineStoreMaterialDetail> > 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(StringResource.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_LineStoreName); //线边仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("工单号"); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_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.LineStoreMaterialDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_ReceiveQty); //领料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_LoadingQty); //上料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_UnloadingQty); //下料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_CurrentQty); //当前数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_SupplierMaterialLot); //供应商物料批次号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_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;
}
LineStoreMaterialDetail obj = lst[j];
Material m = model.GetMaterial(obj.Key.MaterialCode);
Supplier s = model.GetSupplier(obj.SupplierCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(j + 1); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.LineStoreName); //线边仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.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.Key.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.ReceiveQty); //领料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LoadingQty); //上料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.UnloadingQty); //下料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.CurrentQty); //当前数量
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", "LineStoreMaterialData.xls"));
}
private static byte[] SaveToExcelFile(List <Tuple <int, int, object> > content, string sheetName)
{
HSSFWorkbook hssfwb = new HSSFWorkbook();
var sheet = hssfwb.CreateSheet(sheetName);
Dictionary <int, IRow> rows = new Dictionary <int, IRow>();
double output;
DateTime dt;
var numberStyle = hssfwb.CreateCellStyle();
var formatId = HSSFDataFormat.GetBuiltinFormat("0");
if (formatId == -1)
{
var newDataFormat = hssfwb.CreateDataFormat();
numberStyle.DataFormat = newDataFormat.GetFormat("0");
}
else
{
numberStyle.DataFormat = formatId;
}
var dateStyle = hssfwb.CreateCellStyle();
formatId = HSSFDataFormat.GetBuiltinFormat("dd/MM/yyyy");
if (formatId == -1)
{
var newDataFormat = hssfwb.CreateDataFormat();
dateStyle.DataFormat = newDataFormat.GetFormat("dd/MM/yyyy");
}
else
{
dateStyle.DataFormat = formatId;
}
var invalidStyle = (HSSFCellStyle)hssfwb.CreateCellStyle();
invalidStyle.FillForegroundColor = IndexedColors.Yellow.Index;
invalidStyle.FillPattern = FillPattern.SolidForeground;
foreach (var cell in content)
{
if (string.IsNullOrEmpty(cell.Item3?.ToString()))
{
continue;
}
var rowId = cell.Item1;
var columnId = cell.Item2;
IRow row = sheet.GetRow(rowId);
if (row == null)
{
row = sheet.CreateRow(rowId);
}
var excelCell = row.GetCell(columnId);
if (excelCell == null)
{
excelCell = row.CreateCell(columnId);
}
if (cell.Item3 is DateTime)
{
excelCell.SetCellValue((DateTime)cell.Item3);
excelCell.CellStyle = dateStyle;
}
else if (cell.Item3 is bool)
{
excelCell.SetCellValue((bool)cell.Item3);
}
else if (cell.Item3 is string)
{
excelCell.SetCellValue((string)cell.Item3);
}
else if (double.TryParse(cell.Item3.ToString(), out output))
{
excelCell.SetCellValue(output);
excelCell.CellStyle = numberStyle;
}
//else if (cell.Item3.StartsWith(INVALID_CELL))
//{
// excelCell.CellStyle = invalidStyle;
// if (cell.Item3 != INVALID_CELL)
// excelCell.SetCellValue(cell.Item3.Replace(INVALID_CELL, ""));
//}
else
{
excelCell.SetCellValue(cell.Item3.ToString());
}
}
var ms = new MemoryStream();
hssfwb.Write(ms);
return(ms.ToArray());
}
/// <summary>
/// 转换内存表为EXCEL文件流
/// </summary>
/// <param name="SourceTable">源数据</param>
/// <param name="DateTimeFormat">时间列格式化</param>
/// <returns>EXCEL文件流</returns>
public static Stream RenderDataTableToExcelStream(DataTable SourceTable, string DateTimeFormat = "yyyy-MM-dd HH:mm:ss")
{
HSSFWorkbook workbook = new HSSFWorkbook();
IFont font1 = workbook.CreateFont();
font1.IsBold = true;
font1.Color = HSSFColor.White.Index;
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("本地信息列表");
sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, SourceTable.Columns.Count - 1)); //首行筛选
sheet.DefaultColumnWidth = 20;
//sheet.DefaultRowHeight = 20;
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 35;//行高
ICellStyle headstyle = workbook.CreateCellStyle();
headstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1
headstyle.Alignment = HorizontalAlignment.Center; //设置居中 方法3
headstyle.FillPattern = FillPattern.SolidForeground;
headstyle.FillForegroundColor = HSSFColor.BlueGrey.Index;
headstyle.SetFont(font1);
foreach (DataColumn column in SourceTable.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headstyle;
}
int rowIndex = 1;
ICellStyle style = workbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1
style.Alignment = HorizontalAlignment.Center; //设置居中 方法3
style.WrapText = true;
foreach (DataRow row in SourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
//dataRow.HeightInPoints = 20;//行高
IDataFormat dataformat = workbook.CreateDataFormat();
foreach (DataColumn column in SourceTable.Columns)
{
if (row[column] is DBNull)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(string.Empty);
continue;
}
if (column.DataType == typeof(int))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(float))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(double))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(Byte))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(UInt16))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(UInt32))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(UInt64))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else if (column.DataType == typeof(DateTime))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]);
style.DataFormat = dataformat.GetFormat(DateTimeFormat);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column]));
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
bool saveAndWriteContent()
{
string path = "";
string[] _path = this.richTextBox2.Text.Split('\n')[0].Split('\\');
string[] _newPath = new string[_path.Length - 1];
Array.Copy(_path, 0, _newPath, 0, _path.Length - 1);
foreach (string i in _newPath)
{
path += i + "\\";
}
path += "new.xls";
if (File.Exists(path))
{
File.Delete(path);
}
try
{
fs_write = new FileStream(path, FileMode.Create, FileAccess.Write);
workbook = new HSSFWorkbook();
}
catch (Exception e)
{
MessageBox.Show("创建文件失败" + path);
fs_write.Close();
return(false);
}
//content:二维数组，存储每个sheet的所有A145_FCT对象
#region ~添加原始数据
sheet = workbook.CreateSheet("原始数据");
for (int j = 0; j < Head.Length; j++)
{
string[] head_temp = Head[j].Split(',');
for (int k = 0; k < head_temp.Length; k++)
{
if (k == 0)
{
sheet.CreateRow(j).CreateCell(0).SetCellValue(head_temp[k]);
}
else
{
sheet.GetRow(j).CreateCell(k).SetCellValue(head_temp[k]);
}
}
}
for (int i = 0; i < A145s.Length; i++)
{
sheet.CreateRow(i + 5).CreateCell(0).SetCellValue(A145s[i].SN);
sheet.GetRow(i + 5).CreateCell(1).SetCellValue(A145s[i].StationID);
sheet.GetRow(i + 5).CreateCell(2).SetCellValue(A145s[i].PASS_FAIL_Status);
sheet.GetRow(i + 5).CreateCell(3).SetCellValue(A145s[i].SiteID);
sheet.GetRow(i + 5).CreateCell(4).SetCellValue(A145s[i].FailItem);
sheet.GetRow(i + 5).CreateCell(5).SetCellValue(A145s[i].StartTime);
sheet.GetRow(i + 5).CreateCell(6).SetCellValue(A145s[i].TestTime);
sheet.GetRow(i + 5).CreateCell(7).SetCellValue(A145s[i].USB_DP1_TO_GND);
sheet.GetRow(i + 5).CreateCell(8).SetCellValue(A145s[i].USB_DN1_TO_GND);
sheet.GetRow(i + 5).CreateCell(9).SetCellValue(A145s[i].USBC_VBUS_TO_GND);
sheet.GetRow(i + 5).CreateCell(10).SetCellValue(A145s[i].USBC_VCONN_TO_GND);
sheet.GetRow(i + 5).CreateCell(11).SetCellValue(A145s[i].USBC_CC1_TO_GND);
sheet.GetRow(i + 5).CreateCell(12).SetCellValue(A145s[i].USBC_VBUS_TO_USBC_CC1);
sheet.GetRow(i + 5).CreateCell(13).SetCellValue(A145s[i].USBC_VBUS_TO_USBC_VCONN);
sheet.GetRow(i + 5).CreateCell(14).SetCellValue(A145s[i].USBC_CC1_TO_USB_DP1);
sheet.GetRow(i + 5).CreateCell(15).SetCellValue(A145s[i].USB_DP1_TO_USB_DN1);
sheet.GetRow(i + 5).CreateCell(16).SetCellValue(A145s[i].USBC_VBUS_TO_USB_DN1);
sheet.GetRow(i + 5).CreateCell(17).SetCellValue(A145s[i].PP_USBC_VBUS);
sheet.GetRow(i + 5).CreateCell(18).SetCellValue(A145s[i].TP0304);
sheet.GetRow(i + 5).CreateCell(19).SetCellValue(A145s[i].USBC_CC1);
sheet.GetRow(i + 5).CreateCell(20).SetCellValue(A145s[i].USB_DP1);
sheet.GetRow(i + 5).CreateCell(21).SetCellValue(A145s[i].USB_DN1);
sheet.GetRow(i + 5).CreateCell(22).SetCellValue(A145s[i].CURRENT_CC1_475uA);
sheet.GetRow(i + 5).CreateCell(23).SetCellValue(A145s[i].CURRENT_VCONN_475uA);
sheet.GetRow(i + 5).CreateCell(24).SetCellValue(A145s[i].CURRENT_CC1_550uA);
sheet.GetRow(i + 5).CreateCell(25).SetCellValue(A145s[i].CURRENT_VCONN_550uA);
}
#endregion
for (int i = 0; i < content.Length; i++)
{
#region ~添加每个sheet的数据
sheet = workbook.CreateSheet("#" + content[i][0].SiteID);
for (int j = 0; j < Head.Length; j++)
{
string[] head_temp = Head[j].Split(',');
for (int k = 0; k < head_temp.Length; k++)
{
if (k == 0)
{
sheet.CreateRow(j).CreateCell(0).SetCellValue(head_temp[k]);
}
else
{
sheet.GetRow(j).CreateCell(k).SetCellValue(head_temp[k]);
}
}
}
for (int j = 0; j < content[i].Length; j++)
{
sheet.CreateRow(j + 5).CreateCell(0).SetCellValue(content[i][j].SN);
sheet.GetRow(j + 5).CreateCell(1).SetCellValue(content[i][j].StationID);
sheet.GetRow(j + 5).CreateCell(2).SetCellValue(content[i][j].PASS_FAIL_Status);
sheet.GetRow(j + 5).CreateCell(3).SetCellValue(content[i][j].SiteID);
sheet.GetRow(j + 5).CreateCell(4).SetCellValue(content[i][j].FailItem);
sheet.GetRow(j + 5).CreateCell(5).SetCellValue(content[i][j].StartTime);
sheet.GetRow(j + 5).CreateCell(6).SetCellValue(content[i][j].TestTime);
sheet.GetRow(j + 5).CreateCell(7).SetCellValue(content[i][j].USB_DP1_TO_GND);
sheet.GetRow(j + 5).CreateCell(8).SetCellValue(content[i][j].USB_DN1_TO_GND);
sheet.GetRow(j + 5).CreateCell(9).SetCellValue(content[i][j].USBC_VBUS_TO_GND);
sheet.GetRow(j + 5).CreateCell(10).SetCellValue(content[i][j].USBC_VCONN_TO_GND);
sheet.GetRow(j + 5).CreateCell(11).SetCellValue(content[i][j].USBC_CC1_TO_GND);
sheet.GetRow(j + 5).CreateCell(12).SetCellValue(content[i][j].USBC_VBUS_TO_USBC_CC1);
sheet.GetRow(j + 5).CreateCell(13).SetCellValue(content[i][j].USBC_VBUS_TO_USBC_VCONN);
sheet.GetRow(j + 5).CreateCell(14).SetCellValue(content[i][j].USBC_CC1_TO_USB_DP1);
sheet.GetRow(j + 5).CreateCell(15).SetCellValue(content[i][j].USB_DP1_TO_USB_DN1);
sheet.GetRow(j + 5).CreateCell(16).SetCellValue(content[i][j].USBC_VBUS_TO_USB_DN1);
sheet.GetRow(j + 5).CreateCell(17).SetCellValue(content[i][j].PP_USBC_VBUS);
sheet.GetRow(j + 5).CreateCell(18).SetCellValue(content[i][j].TP0304);
sheet.GetRow(j + 5).CreateCell(19).SetCellValue(content[i][j].USBC_CC1);
sheet.GetRow(j + 5).CreateCell(20).SetCellValue(content[i][j].USB_DP1);
sheet.GetRow(j + 5).CreateCell(21).SetCellValue(content[i][j].USB_DN1);
sheet.GetRow(j + 5).CreateCell(22).SetCellValue(content[i][j].CURRENT_CC1_475uA);
sheet.GetRow(j + 5).CreateCell(23).SetCellValue(content[i][j].CURRENT_VCONN_475uA);
sheet.GetRow(j + 5).CreateCell(24).SetCellValue(content[i][j].CURRENT_CC1_550uA);
sheet.GetRow(j + 5).CreateCell(25).SetCellValue(content[i][j].CURRENT_VCONN_550uA);
}
#endregion
}
try
{
workbook.Write(fs_write);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
finally
{
if (fs_write != null)
{
fs_write.Close();
}
}
return(true);
}
//private static WriteLog wl = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return(ms);
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public FileResult OutPutExcel(string statedate, string EndDate)
{
string sql = string.Format(@"select (select MatchBorrowNumber from T_Payment where ID=p.ReunId) as MatchBorrowNumber,(select Reun_Code from T_Payment where ID=p.ReunId) as Reun_Code,(select CrateDate from T_Payment where ID=p.ReunId) as CrateDate,
(select PostUser from T_Payment where ID=p.ReunId) as PostUser,
(select Reun_Reason from T_Payment where ID=p.ReunId) as Reun_Reason,
StoreName,Abstract,Price,Num,
(select top 1 ApproveDate from T_PaymentApprove where Reunbursement_id =p.ReunId and ApproveName='" + UserModel.Nickname + "' and ApproveDate<>'' order by ID desc) as ApproveDate,(select top 1 Remark from T_PaymentApprove where Reunbursement_id =p.ReunId and ApproveName='" + UserModel.Nickname + "' and ApproveDate<>'' order by ID desc) as Remark from T_PaymentProduct p where ReunId in(select Reunbursement_id from T_PaymentApprove where ApproveDate>='" + statedate + "' and ApproveDate<='" + EndDate + " 23:59:59' AND ApproveName='" + UserModel.Nickname + "')");
List <OutDetail> list = db.Database.SqlQuery <OutDetail>(sql).ToList();
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
row1.Height = 3 * 265;
IFont cfont = book.CreateFont();
cfont.FontName = "宋体";
cfont.FontHeight = 1 * 256;
row1.CreateCell(0).SetCellValue("申请日期");
row1.CreateCell(1).SetCellValue("报销人");
row1.CreateCell(2).SetCellValue("报销原因");
row1.CreateCell(3).SetCellValue("店铺名称");
row1.CreateCell(4).SetCellValue("产品名称");
row1.CreateCell(5).SetCellValue("单价");
row1.CreateCell(6).SetCellValue("数量");
row1.CreateCell(7).SetCellValue("应付金额");
row1.CreateCell(8).SetCellValue("审核日期");
row1.CreateCell(9).SetCellValue("报销单号");
row1.CreateCell(10).SetCellValue("审核备注");
row1.CreateCell(11).SetCellValue("冲抵借支批号");
//Remark
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 30 * 256);
sheet1.SetColumnWidth(2, 15 * 256);
sheet1.SetColumnWidth(3, 15 * 256);
sheet1.SetColumnWidth(4, 20 * 256);
sheet1.SetColumnWidth(5, 20 * 256);
sheet1.SetColumnWidth(6, 20 * 256);
sheet1.SetColumnWidth(7, 20 * 256);
sheet1.SetColumnWidth(8, 20 * 256);
sheet1.SetColumnWidth(9, 20 * 256);
sheet1.SetColumnWidth(10, 20 * 256);
sheet1.SetColumnWidth(11, 20 * 256);
double cost = 0;
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.Height = 3 * 265;
rowtemp.CreateCell(0).SetCellValue(list[i].CrateDate.ToString());
rowtemp.Cells[0].CellStyle.Alignment = HorizontalAlignment.Center;
rowtemp.Cells[0].CellStyle.VerticalAlignment = VerticalAlignment.Center;
rowtemp.Cells[0].CellStyle.WrapText = true;
rowtemp.Cells[0].CellStyle.GetFont(book).FontName = "宋体";
rowtemp.Cells[0].CellStyle.GetFont(book).FontHeight = 1 * 256;
rowtemp.CreateCell(1).SetCellValue(string.IsNullOrWhiteSpace(list[i].PostUser) ? "" : list[i].PostUser);
rowtemp.CreateCell(2).SetCellValue(string.IsNullOrWhiteSpace(list[i].Reun_Reason) ? "" : list[i].Reun_Reason);
rowtemp.CreateCell(3).SetCellValue(string.IsNullOrWhiteSpace(list[i].StoreName) ? "" : list[i].StoreName);
rowtemp.CreateCell(4).SetCellValue(string.IsNullOrWhiteSpace(list[i].Abstract) ? "" : list[i].Abstract);
rowtemp.CreateCell(5).SetCellValue(Convert.ToDouble(list[i].Price).ToString("0.00"));
rowtemp.CreateCell(6).SetCellValue(list[i].Num.ToString());
rowtemp.CreateCell(7).SetCellValue(Convert.ToDouble(Convert.ToDouble(list[i].Price) * Convert.ToDouble(list[i].Num)).ToString("0.00"));
rowtemp.CreateCell(8).SetCellValue(list[i].approveDate.ToString());
rowtemp.CreateCell(9).SetCellValue(list[i].Reun_Code);
rowtemp.CreateCell(10).SetCellValue(list[i].Remark);
rowtemp.CreateCell(11).SetCellValue(list[i].MatchBorrowNumber);
cost += Convert.ToDouble(Convert.ToDouble(list[i].Price) * Convert.ToDouble(list[i].Num));
}
IRow heji = sheet1.CreateRow(list.Count() + 1);
ICell heji1 = heji.CreateCell(7);
heji1.SetCellValue(cost.ToString("0.00"));
Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "报销数据.xls"));
}
public ActionResult ExportExcel(BaoYangPriceSelectModel selectModel, string pageType)
{
PagerModel pager = new PagerModel(1, 100000);
var manager = new BaoYangPriceGuideManager();
var list = manager.SelectBaoYangPriceGuide(selectModel, pager);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row = sheet1.CreateRow(0);
var fileName = $"保养价格指导{ThreadIdentity.Operator.Name.Split('@')[0]}.xls";
var rowNum = 0;
row.CreateCell(rowNum).SetCellValue("品牌");
row.CreateCell(rowNum++).SetCellValue("PID");
row.CreateCell(rowNum++).SetCellValue("二级类目");
row.CreateCell(rowNum++).SetCellValue("三级类目");
row.CreateCell(rowNum++).SetCellValue("产品名称");
row.CreateCell(rowNum++).SetCellValue("库存");
row.CreateCell(rowNum++).SetCellValue("工场店库存");
row.CreateCell(rowNum++).SetCellValue("近7天销量");
row.CreateCell(rowNum++).SetCellValue("近30天销量");
row.CreateCell(rowNum++).SetCellValue("周转天数");
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("进货价");
row.CreateCell(rowNum++).SetCellValue("最近一次采购价");
}
if (string.IsNullOrWhiteSpace(pageType))
{
row.CreateCell(rowNum++).SetCellValue("理论指导价");
row.CreateCell(rowNum++).SetCellValue("实际指导价");
}
row.CreateCell(rowNum++).SetCellValue("官网价格");
if (string.IsNullOrWhiteSpace(pageType))
{
row.CreateCell(rowNum++).SetCellValue("活动价");
}
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("途虎毛利率");
row.CreateCell(rowNum++).SetCellValue("途虎毛利额");
}
row.CreateCell(rowNum++).SetCellValue("汽配龙");
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("汽配龙毛利率");
row.CreateCell(rowNum++).SetCellValue("汽配龙毛利额");
row.CreateCell(rowNum++).SetCellValue("工场店毛利率");
row.CreateCell(rowNum++).SetCellValue("工场店毛利额");
}
row.CreateCell(rowNum++).SetCellValue("京东自营");
row.CreateCell(rowNum++).SetCellValue("特维轮天猫");
row.CreateCell(rowNum++).SetCellValue("养车无忧官网");
row.CreateCell(rowNum++).SetCellValue("汽车超人零售");
row.CreateCell(rowNum++).SetCellValue("康众官网");
row.CreateCell(rowNum++).SetCellValue("汽车超人批发");
row.CreateCell(rowNum++).SetCellValue("途虎淘宝");
row.CreateCell(rowNum++).SetCellValue("途虎淘宝2");
row.CreateCell(rowNum++).SetCellValue("途虎天猫1");
row.CreateCell(rowNum++).SetCellValue("途虎天猫2");
row.CreateCell(rowNum++).SetCellValue("途虎天猫3");
row.CreateCell(rowNum++).SetCellValue("途虎天猫4");
row.CreateCell(rowNum++).SetCellValue("途虎京东");
row.CreateCell(rowNum).SetCellValue("途虎京东旗舰");
if (list.Item1 && list.Item2.Any())
{
var i = 0;
foreach (var item in list.Item2)
{
var rowtempNum = 0;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(++i);
rowtemp.CreateCell(rowtempNum).SetCellValue(item.Brand);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.PID);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.SecondType);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ThirdType);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ProductName);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : item.totalstock.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ShopStock);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_week == null ? "" : item.num_week.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null ? "" : item.num_month.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null || item.totalstock == null || item.num_month <= 0 || item.totalstock <= 0
? "" :
Math.Ceiling((decimal)item.totalstock / item.num_month.Value * 30).ToString(CultureInfo.InvariantCulture));
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost == null ? "" : item.cost.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.PurchasePrice == null ? "" : item.PurchasePrice.Value.ToString("0.00"));
}
if (string.IsNullOrWhiteSpace(pageType))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.TheoryGuidePrice == null
? ""
: item.TheoryGuidePrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.ActualGuidePrice == null
? ""
: item.ActualGuidePrice.Value.ToString("0.00"));
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.Price.ToString("0.00"));
if (string.IsNullOrWhiteSpace(pageType))
{
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.FlashSalePrice != null ? item.FlashSalePrice.Value.ToString("0.00") : "-");
}
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0
? ((item.Price - item.cost.Value) / item.Price).ToString("0.00%")
: "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0
? (item.Price - item.cost.Value).ToString("0.00")
: "");
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QPLPrice == null ? "" : item.QPLPrice.Value.ToString("0.00"));
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? (((decimal)item.QPLPrice - item.cost.Value) / (decimal)item.QPLPrice).ToString("0.00%") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? ((decimal)item.QPLPrice - item.cost.Value).ToString("0.00") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.Price > 0 ? ((item.Price - (decimal)item.QPLPrice) / item.Price).ToString("0.00%") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 ? (item.Price - (decimal)item.QPLPrice).ToString("0.00") : "");
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDSelfPrice == null ? "" : item.JDSelfPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TWLTMPrice == null ? "" : item.TWLTMPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrlPrice == null ? "" : item.QccrlPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.YcwyPrice == null ? "" : item.YcwyPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.KzPrice == null ? "" : item.KzPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrpPrice == null ? "" : item.QccrpPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TBPrice == null ? "" : item.TBPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TB2Price == null ? "" : item.TB2Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM1Price == null ? "" : item.TM1Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM2Price == null ? "" : item.TM2Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM3Price == null ? "" : item.TM3Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM4Price == null ? "" : item.TM4Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDPrice == null ? "" : item.JDPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum).SetCellValue(item.JDFlagShipPrice == null ? "" : item.JDFlagShipPrice.Value.ToString("0.00"));
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName));
}
//需要引用DLL组件
/// <summary>
/// DataGridView导出至Excel,解决问题:打开Excel文件格式与扩展名指定格式不一致
/// </summary>
/// <param name="dataGridView">数据源表格</param>
/// <param name="isShowExcle">导出时是否显示excel界面</param>
/// <returns></returns>
//public static bool DcExcel(DataGridView dataGridView, bool isShowExcle = true)
//{
// int FormatNum;//保存excel文件的格式
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// string excelVersion = excel.Version;//获取你使用的excel 的版本号
// //声明保存对话框
// SaveFileDialog saveFileDialog = new SaveFileDialog();
// //默然文件后缀
// saveFileDialog.DefaultExt = "xls";
// if (Convert.ToDouble(excelVersion) < 12)//You use Excel 97-2003
// {
// FormatNum = -4143;
// //文件后缀列表
// saveFileDialog.Filter = "Excel(*.xls)|*.xls";
// }
// else//you use excel 2007 or later
// {
// FormatNum = 56;
// //文件后缀列表
// saveFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(2007-2016)(*.xlsx)|*.xlsx";
// }
// Form fr = dataGridView.Parent as Form;
// if (fr != null)//默认文件名
// {
// saveFileDialog.FileName = fr.Text;
// }
// //默然路径是系统当前路径
// saveFileDialog.InitialDirectory = Directory.GetCurrentDirectory();
// //打开保存对话框
// if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
// return false;
// //返回文件路径
// string fileName = saveFileDialog.FileName;
// if (string.IsNullOrEmpty(fileName.Trim()))
// { return false; }
// if (dataGridView.Rows.Count == 0)
// return false;
// //建立Excel对象
// var objWorkbook = excel.Application.Workbooks.Add(true);
// excel.Visible = isShowExcle;
// //生成字段名称
// for (int i = 0; i < dataGridView.ColumnCount; i++)
// {
// excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
// excel.Cells[1, i + 1].Font.Bold = true;
// }
// //填充数据
// for (int i = 0; i < dataGridView.RowCount - 1; i++)
// {
// for (int j = 0; j < dataGridView.ColumnCount; j++)
// {
// if (dataGridView[j, i].ValueType == typeof(string))
// {
// excel.Cells[i + 2, j + 1] = "'" + dataGridView[j, i].Value.ToString();
// }
// else
// {
// excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString();
// }
// }
// }
// //Excel.XlFileFormat.xlOpenXMLWorkbook（.xlsx）
// //Excel.XlFileFormat.xlExcel8（Excel97 - 2003, .xls）
// //判断excel文件的保存格式是xls还是xlsx
// var format = fileName.EndsWith(".xls") ? Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8 : Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
// objWorkbook.SaveAs(fileName, format, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
// return true;
//}
#region 导出到Excel 2003：xls文件
private void DgvToXls(DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出数据到本地计算机";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Columns[j].HeaderText == "日期" && dgv.Rows[i].Cells[j].Value != DBNull.Value)
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
else
{
cell.SetCellValue((dgv.Rows[i].Cells[j].Value).ToString());
}
//if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
//{
// cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
//{
// cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
//{
// cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
//{
// cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
//{
// cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
//{
// cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
//}
//else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Time"))
//{
// cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("HH-mm-ss"));
//}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(saveFileDialog.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
/// <summary>
/// 生成Excel
/// </summary>
/// <returns>生成的Excel文件</returns>
public virtual byte[] GenerateExcel()
{
NeedPage = false;
if (GridHeaders == null)
{
GetHeaders();
}
if (IsSearched == false)
{
DoSearch();
}
HSSFWorkbook workbook = new HSSFWorkbook();
List <HSSFSheet> sheets = new List <NPOI.HSSF.UserModel.HSSFSheet>();
//准备好sheet，每6万行生成一个sheet
var sheetno = ((EntityList.Count - 1) / 60000) + 1;
var headerStyle = workbook.CreateCellStyle();
//设定单元格边框
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
//用灰色填充背景
var headerbg = HSSFColor.Grey25Percent.Index;
headerStyle.FillForegroundColor = headerbg;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.FillBackgroundColor = headerbg;
//去掉 Id 列和动作列
RemoveActionAndIdColumn();
//循环生成所有sheet，并为每个sheet添加表头
var headerrows = 0;
for (int i = 1; i <= sheetno; i++)
{
HSSFSheet sheet = workbook.CreateSheet("Sheet" + i) as HSSFSheet;
//生成表头
headerrows = MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle);
sheets.Add(sheet);
}
var rowIndex = headerrows;
var colIndex = 0;
//Excel中用到的style，每种前景色和背景色的组合为一个style。Nopi值支持4000个style，所以同样的style要重复使用
Dictionary <string, ICellStyle> styles = new Dictionary <string, ICellStyle>();
//Excel中用到的font，主要是为了更改字体颜色
Dictionary <string, IFont> fonts = new Dictionary <string, IFont>();
//循环数据
foreach (var row in EntityList)
{
var sheetindex = ((rowIndex - headerrows) / 60000);
colIndex = 0;
string bgColor = "";
string fColor = "";
//获取设定的行背景色
bgColor = SetFullRowBgColor(row);
//获取设定的行前景色
fColor = SetFullRowColor(row);
var dr = sheets[sheetindex].CreateRow(rowIndex - sheetindex * 60000) as HSSFRow;
foreach (var baseCol in GridHeaders)
{
//处理枚举变量的多语言
bool IsEmunBoolParp = false;
var proType = baseCol.FieldType;
if (proType.IsEnumOrNullableEnum())
{
IsEmunBoolParp = true;
}
foreach (var col in baseCol.BottomChildren)
{
//获取数据，并过滤特殊字符
string text = Regex.Replace(col.GetText(row).ToString(), @"<[^>]*>", String.Empty);
//处理枚举变量的多语言
if (IsEmunBoolParp)
{
if (int.TryParse(text, out int enumvalue))
{
text = PropertyHelper.GetEnumDisplayName(proType, enumvalue);
}
}
//建立excel单元格
var cell = dr.CreateCell(colIndex);
ICellStyle style = null;
IFont font = null;
var styleKey = string.Empty;
//获取设定的单元格背景色
string backColor = col.GetBackGroundColor(row);
//获取设定的单元格前景色
string foreColor = col.GetForeGroundColor(row);
//如果行背景色或单元格背景色有值，则用颜色的ARGB的值作为style的key
if (bgColor != "" || backColor != "")
{
styleKey = backColor == "" ? bgColor : backColor;
}
//如果行前景色或单元格前景色有值，则用颜色的ARGB加上背景色的ARGB作为style的key
if (fColor != "" || foreColor != "")
{
styleKey += foreColor == "" ? foreColor : fColor;
}
//如果已经有符合条件的style，则使用
if (styles.ContainsKey(styleKey))
{
style = styles[styleKey];
}
//如果没有，则新建一个style
else
{
var newKey = "";
var newFontKey = "";
//新建style
style = workbook.CreateCellStyle();
//设定单元格边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//如果行前景色或单元格前景色有值，则设定单元格的填充颜色
if (bgColor != "" || backColor != "")
{
newKey = backColor == "" ? bgColor : backColor;
var ci = Utils.GetExcelColor(backColor == "" ? bgColor : backColor);
style.FillForegroundColor = ci;
style.FillPattern = FillPattern.SolidForeground;
style.FillBackgroundColor = ci;
}
//如果行前景色或单元格前景色有值，则设定单元格的字体的颜色
if (fColor != "" || foreColor != "")
{
newFontKey = foreColor == "" ? foreColor : fColor;
newKey += foreColor == "" ? foreColor : fColor;
//如果已经有符合条件的字体，则使用
if (fonts.ContainsKey(newFontKey))
{
font = fonts[newFontKey];
}
//如果没有，则新建
else
{
//新建字体
font = workbook.CreateFont();
//设定字体颜色
font.Color = Utils.GetExcelColor(foreColor == "" ? fColor : foreColor);
//向集合中添加新字体
fonts.Add(newFontKey, font);
}
//设定style中的字体
style.SetFont(font);
}
//将新建的style添加到集合中
styles.Add(newKey, style);
}
cell.CellStyle = style;
cell.SetCellValue(text);
colIndex++;
}
}
rowIndex++;
}
//获取Excel文件的二进制数据
byte[] rv = new byte[] { };
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
rv = ms.ToArray();
}
return(rv);
}
/// <summary>
/// 转换内存表为EXCEL文件流(行数超过65535，sheet分页)
/// </summary>
/// <param name="SourceTable">源数据</param>
/// <param name="sheetSize">sheet最大行数，不大于65535</param>
/// <param name="DateTimeFormat">时间列格式化</param>
/// <returns>EXCEL文件流</returns>
public static Stream RenderDataTableToPagingExcelStream(DataTable SourceTable, int sheetSize = 65535, string DateTimeFormat = "yyyy-MM-dd HH:mm:ss")
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
IDataFormat dataformat = workbook.CreateDataFormat();
ICellStyle style = workbook.CreateCellStyle();
int count = SourceTable.Rows.Count;
int total = count / sheetSize + (count % sheetSize > 0 ? 1 : 0);
for (
int sheetIndex = 0;
sheetIndex < total;
sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in SourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
for (int i = sheetIndex * sheetSize;
i < (total.Equals(sheetIndex + 1) ? count : (sheetIndex + 1) * sheetSize);
i++)
{
DataRow row = SourceTable.Rows[i];
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in SourceTable.Columns)
{
if (row[column] is DBNull)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(string.Empty);
continue;
}
if (column.DataType == typeof(int))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]);
}
else if (column.DataType == typeof(float))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]);
}
else if (column.DataType == typeof(double))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]);
}
else if (column.DataType == typeof(Byte))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]);
}
else if (column.DataType == typeof(UInt16))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]);
}
else if (column.DataType == typeof(UInt32))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]);
}
else if (column.DataType == typeof(UInt64))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]);
}
else if (column.DataType == typeof(DateTime))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]);
style.DataFormat = dataformat.GetFormat(DateTimeFormat);
dataRow.GetCell(column.Ordinal).CellStyle = style;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column]));
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
}
workbook = null;
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "刘晓雷"; //填加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[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource"> 源DataTable </param>
/// <param name="strHeaderText"> 表头文本 </param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, string strHeaderText)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion 右击文件 属性信息
var dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
var format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if ((rowIndex == 65535) || (rowIndex == 0))
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
var headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
var font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion 表头及样式
#region 列头及样式
{
var headerRow = (HSSFRow)sheet.CreateRow(1);
var headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
var 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] + 3) * 256);
}
//headerRow.Dispose();
}
#endregion 列头及样式
rowIndex = 2;
}
#endregion 新建表，填充表头，填充列头，样式
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
var 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": //布尔型
var boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
var 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++;
}
var ms = new MemoryStream();
//using (var ms = new MemoryStream())
//{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
//}
}
/// <summary>
/// 將DataTable轉成Stream輸出.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <returns></returns>
public Stream RenderDataTableToExcel(DataTable SourceTable, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitializeWorkbook(workbook);
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet();
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
//设置标题的样式
IRow titlerow = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
style.SetFont(font);
titlerow.CreateCell(0).SetCellValue(title);
titlerow.GetCell(0).CellStyle = style;
//设置时间格式
ICellStyle datetimestyle = workbook.CreateCellStyle();
datetimestyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
IRow headerRow = sheet.CreateRow(1);
// handling header.
foreach (DataColumn column in SourceTable.Columns)
{
if (column.DataType == typeof(DateTime))
{
sheet.SetColumnWidth(column.Ordinal, 5120);
}
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 2;
object obj;
foreach (DataRow row in SourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in SourceTable.Columns)
{
obj = row[column];
if (column.DataType == typeof(double))
{
if (obj == DBNull.Value)
{
obj = 0.0;
}
dataRow.CreateCell(column.Ordinal).SetCellValue((double)obj);
}
else if (column.DataType == typeof(DateTime))
{
dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)obj);
dataRow.GetCell(column.Ordinal).CellStyle = datetimestyle;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(obj.ToString());
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 输出Excel内存流
/// </summary>
/// <param name="dts">数据表集合</param>
/// <returns>内存流</returns>
public static MemoryStream ToExcelStream(this DataTable[] dts)
{
if (dts.IsNullOrLength0())
{
return(new MemoryStream());
}
MemoryStream ms = null;
IWorkbook workbook = null;
try
{
//创建一个工作簿
workbook = new HSSFWorkbook();
for (int k = 0; k < dts.Length; k++)
{
DataTable dt = dts[k];
string sheetName = string.IsNullOrWhiteSpace(dt.TableName) ? $"sheet{k + 1}" : dt.TableName;
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(sheetName);
//创建一行
IRow rowH = sheet.CreateRow(0);
//创建一个单元格
ICell cell = null;
//创建列标题单元格样式
ICellStyle cellTitleStyle = workbook.CreateCellStyle();
cellTitleStyle.Alignment = HorizontalAlignment.Center;
cellTitleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont f = workbook.CreateFont();
//f.Boldweight = (short)FontBoldWeight.Bold;
cellTitleStyle.SetFont(f);
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
//cellTitleStyle.DataFormat = dataFormat.GetFormat("@");
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellTitleStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
//创建一个 IO 流
ms = new MemoryStream();
//写入到流
workbook.Write(ms);
return(ms);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}
/// <summary>
/// DataTable 导出到 Excel 的 MemoryStream
/// </summary>
/// <param name="dtSource">源 DataTable</param>
/// <param name="strHeaderText">表头文本 空值未不要表头标题</param>
/// <returns></returns>
public static MemoryStream ExportExcel(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "517best.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "517best.com";
si.ApplicationName = "517best.com";
si.LastAuthor = "517best.com";
si.Comments = "";
si.Title = "";
si.Subject = "";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int intTop = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表、填充表头、填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
intTop = 0;
#region 表头及样式
{
if (strHeaderText.Length > 0)
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(intTop);
intTop += 1;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
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 = intTop;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="colipuSouceModels"></param>
private void Export(List <ColipuSouceModel> colipuSouceModels)
{
//输出Excel
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet tb = workbook.CreateSheet("核销结果");
#region 表头
IRow row = tb.CreateRow(0);
var cellValList = new List <string>()
{
"出库单号",
"商品编号",
"金额",
"数量",
"签收日期",
"是否已核销",
};
for (int i = 0; i < cellValList.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(cellValList[i]);
}
#endregion
#region 组装数据
var table = new DataTable("匹配结果");
DataColumn column;
column = new DataColumn
{
ColumnName = "Dorder",
Caption = "出库单号"
};
table.Columns.Add(column);
column = new DataColumn
{
ColumnName = "ProductCode",
Caption = "商品编码"
};
table.Columns.Add(column);
column = new DataColumn
{
ColumnName = "Price",
Caption = "价格"
};
table.Columns.Add(column);
column = new DataColumn
{
ColumnName = "Quantity",
Caption = "价格"
};
table.Columns.Add(column);
column = new DataColumn
{
ColumnName = "Time",
Caption = "签收时间"
};
table.Columns.Add(column);
column = new DataColumn
{
ColumnName = "IsHeXiao",
Caption = "是否核销"
};
table.Columns.Add(column);
#endregion
//colipuSouceModels
foreach (var item in colipuSouceModels.OrderBy(x => x.ProductCode))
{
if (item != null)
{
var tableRow = table.NewRow();
tableRow["Dorder"] = item.DOrder;
tableRow["ProductCode"] = item.ProductCode;
tableRow["Price"] = item.Price;
tableRow["Quantity"] = item.Quantity;
tableRow["Time"] = item.Time;
tableRow["IsHeXiao"] = item.IsHeXiao;
table.Rows.Add(tableRow);
}
}
#region 数据
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row1 = tb.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(table.Rows[i][j].ToString());
}
}
using (FileStream fs = File.OpenWrite($"{textBox2.Text}/核销结果.xls")) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
if (MessageBox.Show("操作成功！", "", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
Environment.Exit(0);
Close();
}
}
#endregion
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetFormString("txtCusName");
_cid = DTRequest.GetFormString("hCusId");
_check1 = DTRequest.GetFormString("ddlcheck1");
_check2 = DTRequest.GetFormString("ddlcheck2");
_check3 = DTRequest.GetFormString("ddlcheck3");
_isconfirm = DTRequest.GetFormString("ddlisConfirm");
_oid = DTRequest.GetFormString("txtOid");
_sign = DTRequest.GetFormString("ddlsign");
_money = DTRequest.GetFormString("txtMoney");
_sdate = DTRequest.GetFormString("txtsDate");
_edate = DTRequest.GetFormString("txteDate");
_farea = DTRequest.GetFormString("ddlfarea");
_darea = DTRequest.GetFormString("ddldarea");
_invType = DTRequest.GetFormString("ddlinvType");
_name = DTRequest.GetFormString("txtName");
_unit = DTRequest.GetFormString("txtUnit");
_purchaserName = DTRequest.GetFormString("txtpurchaserName");
_self = DTRequest.GetFormString("self");//self=1表示个人页面
BLL.invoices bll = new BLL.invoices();
DataTable dt = bll.GetList(this.pageSize, this.page, "inv_id>0" + CombSqlTxt(), "inv_addDate desc,inv_id desc", manager, out this.totalCount, out _tmoney, 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.CreateCell(10).SetCellValue("财务审批");
headRow.CreateCell(11).SetCellValue("开票状态");
headRow.CreateCell(12).SetCellValue("开票日期");
headRow.CreateCell(13).SetCellValue("专普票");
headRow.CreateCell(14).SetCellValue("开票单位");
headRow.CreateCell(15).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;
headRow.GetCell(10).CellStyle = titleCellStyle;
headRow.GetCell(11).CellStyle = titleCellStyle;
headRow.GetCell(12).CellStyle = titleCellStyle;
headRow.GetCell(13).CellStyle = titleCellStyle;
headRow.GetCell(14).CellStyle = titleCellStyle;
headRow.GetCell(15).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);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 20 * 256);
sheet.SetColumnWidth(12, 20 * 256);
sheet.SetColumnWidth(13, 20 * 256);
sheet.SetColumnWidth(14, 20 * 256);
sheet.SetColumnWidth(15, 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());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_oid"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_serviceType"]) + "/" + Utils.ObjectToStr(dt.Rows[i]["inv_serviceName"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_money"]));
row.CreateCell(4).SetCellValue("0");
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_sentWay"]));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["de_subname"]));
row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_personName"]));
row.CreateCell(8).SetCellValue(dt.Rows[i]["inv_flag1"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag1"].ToString() == "1" ? "审批未通过" : "审批通过");
row.CreateCell(9).SetCellValue(dt.Rows[i]["inv_flag2"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag2"].ToString() == "1" ? "审批未通过" : "审批通过");
row.CreateCell(10).SetCellValue(dt.Rows[i]["inv_flag3"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag3"].ToString() == "1" ? "审批未通过" : "审批通过");
row.CreateCell(11).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["inv_isConfirm"]), false) ? "已开票" : "未开票");
row.CreateCell(12).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["inv_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["inv_date"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(13).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_type"]));
row.CreateCell(14).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["invU_name"]));
row.CreateCell(15).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_purchaserName"]));
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;
row.GetCell(10).CellStyle = cellStyle;
row.GetCell(11).CellStyle = cellStyle;
row.GetCell(12).CellStyle = cellStyle;
row.GetCell(13).CellStyle = cellStyle;
row.GetCell(14).CellStyle = cellStyle;
row.GetCell(15).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
public FileResult ExamRoomExport()
{
#region 生成xls
string[] temArr = { "考场", "科目时间", "监考老师", "座位号", "姓名", "准考证", "学生班级" };
List <string> lstTitle = new List <string>(temArr);
//var lstTitle = new List { "考场", "科目时间", "监考老师", "座位号", "姓名", "准考证", "学生班级" };//,
IWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow rowTitle = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
for (int i = 0; i < lstTitle.Count; i++)
{
rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
}
//var list = null;
////var list = data.GetListResultStudent(examId);
//if (list != null)
//{
// list.OrderBy(o => o.RoomId);
// int start = 0;//记录同组开始行号
// int end = 0;//记录同组结束行号
// string temp = "";//记录上一行的值
// for (int i = 0; i < list.Count; i++)
// {
// IRow row = sheet.CreateRow(i + 1);
// row.CreateCell(0).SetCellValue(list[i].RoomName);
// row.CreateCell(1).SetCellValue(list[i].ExamTime.ToLongDateString() + list[i].ExamTime.ToLongTimeString());
// row.CreateCell(2).SetCellValue(list[i].TeacherName);
// row.CreateCell(3).SetCellValue(list[i].ZuoWeiNumber);
// row.CreateCell(4).SetCellValue(list[i].StudentName);
// row.CreateCell(5).SetCellValue(list[i].ZhunNumber);
// row.CreateCell(6).SetCellValue(list[i].ClassName);
// row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String);
// var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值
// if (cellText == temp)//上下行相等，记录要合并的最后一行
// {
// end = i;
// }
// else//上下行不等，记录
// {
// if (start != end)
// {
// //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
// //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
// CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0);
// sheet.AddMergedRegion(region);
// }
// start = i;
// end = i;
// temp = cellText;
// }
// }
//}
#endregion
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "考场总表导出.xls"));
}
public void ExportApproval(string HTCode, string ApplierMUDID, string BUHeadMUDID, string Category, string Type)
{
#region 抓取数据
int total = 0;
var list = PreApprovalService.QueryLoad(HTCode, ApplierMUDID, BUHeadMUDID, Category, Type, int.MaxValue, 1, out total
).Select(a => new
{
c1 = FormatterNull(a.c1),
c2 = FormatterNull(a.c2),
c3 = FormatterNull(a.c3),
c4 = FormatterNull(a.c4),
c5 = FormatterNull(a.c5),
c6 = FormatterNull(a.c6),
c7 = FormatterNull(a.c7),
c8 = FormatterNull(a.c8),
c9 = FormatterNull(a.c9),
c10 = FormatterNull(a.c10),
c11 = FormatterNull(a.c11),
c12 = FormatterNull(a.c12),
c13 = FormatterNull(a.c13)
}).ToArray();;
#endregion
#region 构建Excel
HSSFWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("Cater");
IRow row = sheet.CreateRow(0);
//ICellStyle style = wk.CreateCellStyle();
//style.WrapText = true;
//style.Alignment = HorizontalAlignment.Center;
//IFont font = wk.CreateFont();
//font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
//font.FontHeightInPoints = 10;
//style.SetFont(font);
ICellStyle style = wk.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index;
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index;
style.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 10;
style.SetFont(font);
#endregion
#region 生成表头
var title = new string[] { "HT编号", "申请人姓名", "申请人MUDID", "流程类别", "流程状态", "提交日期", "提交时间", "审批人MUDID", "审批人姓名", "审批动作", "审批理由", "审批日期", "审批时间" };
sheet.DefaultRowHeight = 200 * 2;
for (var i = 0; i < title.Length; i++)
{
sheet.SetColumnWidth(i, 15 * 256);
}
for (var i = 0; i < title.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(title[i]);
cell.SetCellType(CellType.String);
cell.CellStyle = style;
}
#endregion
#region 制作表体
for (var i = 1; i <= list.Length; i++)
{
var item = list[i - 1];
row = sheet.CreateRow(i);
ICell cell = null;
//2018-1-12 史强 注释掉序号列
//cell = row.CreateCell(0);
//cell.SetCellValue(i);
var j = 0;
if (item != null)
{
cell = row.CreateCell(j);
cell.SetCellValue(item.c1);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c2);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c3);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c4);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c5);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c6);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c7);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c8);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c9);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c10);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c11);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c12);
cell = row.CreateCell(++j);
cell.SetCellValue(item.c13);
cell = row.CreateCell(++j);
}
}
#endregion
#region 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
wk.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(ms.ToArray());
}
#endregion
}
public static HSSFWorkbook GetExcel()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//increase the width of Column A
sheet.SetColumnWidth(0, 5000);
//create the format instance
IDataFormat format = workbook.CreateDataFormat();
// Create a row and put some cells in it. Rows are 0 based.
ICell cell = sheet.CreateRow(0).CreateCell(0);
//number format with 2 digits after the decimal point - "1.20"
SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
//RMB currency format with comma - "¥20,000"
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));
//scentific number format - "3.15E+00"
ICell cell3 = sheet.CreateRow(2).CreateCell(0);
SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));
//percent format, 2 digits after the decimal point - "99.33%"
ICell cell4 = sheet.CreateRow(3).CreateCell(0);
SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));
//phone number format - "021-65881234"
ICell cell5 = sheet.CreateRow(4).CreateCell(0);
SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000"));
//Chinese capitalized character number - 壹贰叁 元
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));
//Chinese date string
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
cell7.SetCellValue(new DateTime(2004, 5, 6));
//Chinese date string
ICell cell8 = sheet.CreateRow(7).CreateCell(0);
SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日"));
//formula value with datetime style
ICell cell9 = sheet.CreateRow(8).CreateCell(0);
cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
ICellStyle cellStyle9 = workbook.CreateCellStyle();
cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
cell9.CellStyle = cellStyle9;
//display current time
ICell cell10 = sheet.CreateRow(9).CreateCell(0);
SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));
ISheet sheet2 = workbook.CreateSheet("Sheet2");
//increase the width of Column A
sheet2.SetColumnWidth(0, 5000);
// Create a row and put some cells in it. Rows are 0 based.
ICell cell21 = sheet2.CreateRow(0).CreateCell(0);
//number format with 2 digits after the decimal point - "1.20"
SetValueAndFormat(workbook, cell21, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
return(workbook);
}
/// <summary>
/// 实体类集合导出到EXCLE2003
/// </summary>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
/// <param name="enList">数据源</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>文件的下载地址</returns>
public static string EntityListToExcel2003(Dictionary <string, string> cellHeard, IList enList, string filePath, string sheetName)
{
try
{
//string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
//string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址，供给前台下载
//string filePath = sheetName; // 文件路径
// 1.检测是否存在文件夹，若不存在就建立个文件夹
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
// 2.解析单元格头部，设置单元头的中文名称
HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
ISheet sheet = workbook.CreateSheet(sheetName); // 工作表
IRow row = sheet.CreateRow(0);
List <string> keys = cellHeard.Keys.ToList();
for (int i = 0; i < keys.Count; i++)
{
row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值
}
// 3.List对象的值赋值到Excel的单元格里
int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头)
foreach (var en in enList)
{
IRow rowTmp = sheet.CreateRow(rowIndex);
for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称，获取对象指定属性的值
{
string cellValue = ""; // 单元格的值
object properotyValue = null; // 属性的值
System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息
// 3.1 若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.UserName
if (keys[i].IndexOf(".") >= 0)
{
// 3.1.1 解析子类属性(这里只解析1层子类，多层子类未处理)
string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
if (subClassInfo != null)
{
// 3.1.2 获取子类的实例
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
// 3.1.3 根据属性名称获取子类里的属性类型
properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值
}
}
}
else
{
// 3.2 若不是子类的属性，直接根据属性名称获取对象对应的属性
properotyInfo = en.GetType().GetProperty(keys[i]);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(en, null);
}
}
// 3.3 属性值经过转换赋值给单元格值
if (properotyValue != null)
{
cellValue = properotyValue.ToString();
// 3.3.1 对时间初始值赋值为空
if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
{
cellValue = "";
}
}
// 3.4 填充到Excel的单元格里
rowTmp.CreateCell(i).SetCellValue(cellValue);
}
rowIndex++;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
// 5.返回下载路径
return(filePath);
}
catch (Exception ex)
{
throw ex;
}
}
private void SaveExcelFile(string FilePath)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("DataFromReportProgram");
// dgv에서 column 참조해서 나중에 재설정할것
var columns = new[] { "ID", "Name" };
var headers = new[] { "ID", "Name" };
var headerRow = sheet.CreateRow(0);
var font = workbook.CreateFont();
var headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 11;
headerFont.FontName = "맑은 고딕";
headerFont.IsBold = true;
headerFont.Color = IndexedColors.White.Index;
var dataFont = workbook.CreateFont();
dataFont.FontHeightInPoints = 11;
dataFont.FontName = "맑은 고딕";
dataFont.IsBold = false;
dataFont.Color = IndexedColors.Black.Index;
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
HeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HeaderStyle.FillForegroundColor = IndexedColors.DarkBlue.Index;
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.SetFont(headerFont);
ICellStyle DefaultStyle_Odd = workbook.CreateCellStyle();
DefaultStyle_Odd.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Odd.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Odd.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Odd.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Odd.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
DefaultStyle_Odd.FillForegroundColor = IndexedColors.White.Index;
DefaultStyle_Odd.FillPattern = FillPattern.SolidForeground;
DefaultStyle_Odd.SetFont(dataFont);
ICellStyle DefaultStyle_Even = workbook.CreateCellStyle();
DefaultStyle_Even.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Even.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Even.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Even.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
DefaultStyle_Even.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
DefaultStyle_Even.FillForegroundColor = IndexedColors.Grey25Percent.Index;
DefaultStyle_Even.FillPattern = FillPattern.SolidForeground;
DefaultStyle_Even.SetFont(dataFont);
/*
* ICellStyle cellStyle1 = workbook.CreateCellStyle();
* cellStyle1.FillForegroundColor = IndexedColors.White.Index;
* cellStyle1.FillPattern = FillPattern.SolidForeground;
* cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
* cellStyle1.SetFont(dataFont);
*
* ICellStyle cellStyle2 = workbook.CreateCellStyle();
* cellStyle2.FillForegroundColor = IndexedColors.Grey25Percent.Index;
* cellStyle2.FillPattern = FillPattern.SolidForeground;
* cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
* cellStyle2.SetFont(dataFont);
*/
//Below loop is create header
for (int i = 0; i < selectedDataView.ColumnCount; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(selectedDataView.Columns[i].HeaderText);
cell.CellStyle = HeaderStyle;
}
//Below loop is fill content
for (int i = 0; i < selectedDataView.RowCount; i++)
{
var row = sheet.CreateRow(i + 1);
for (int j = 0; j < selectedDataView.ColumnCount; j++)
{
var cell = row.CreateCell(j);
cell.SetCellValue(selectedDataView.Rows[i].Cells[j].Value.ToString());
if (i % 2 == 0)
{
cell.CellStyle = DefaultStyle_Even;
}
else
{
cell.CellStyle = DefaultStyle_Odd;
}
}
}
// 셀 크기 조정
for (int i = 0; i < selectedDataView.ColumnCount; i++)
{
// 너비 자동조정
sheet.AutoSizeColumn(i);
// 자동 조정하면 너무 빡빡해서 여백을 약간 줌
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 512);
}
// 셀 높이 조정 (기본 Height 255)
/*for(int i=0; i<dgv.RowCount; i++)
* {
* sheet.GetRow(i).Height = 300;
* }*/
// Declare one MemoryStream variable for write file in stream
var stream = new MemoryStream();
workbook.Write(stream);
//Write to file using file stream
FileStream file = new FileStream(FilePath, FileMode.Create, FileAccess.Write);
stream.WriteTo(file);
file.Close();
stream.Close();
stopwatch.Stop();
MessageBox.Show("time : " + stopwatch.ElapsedMilliseconds + "ms");
#region 어플리케이션에 셀 하나씩 직접 대입
/* string filePath = "D:\\SavedExcelFolder\\"+string.Format("Test {0}.xls", DateTime.Now.ToString("yyyy-MM-dd"));
*
* Excel._Application app = new Excel.Application();
* Excel.Workbook workbook;
* Excel.Worksheet worksheet;
*
* workbook = app.Workbooks.Add(Type.Missing);
*
* app.Visible = false;
*
* worksheet = workbook.Sheets["Sheet1"];
* worksheet = workbook.ActiveSheet;
*
* worksheet.Name = sheetName;
* int colIndex = 0;
*
* //// storing header part in Excel
* //for (int i = 1; i < dgv.Columns.Count + 1; i++)
* //{
* // if (dgv.Columns[i - 1].Visible == true)
* // {
* // colIndex += 1;
* // worksheet.Cells[1, colIndex] = dgv.Columns[i - 1].HeaderText;
* // }
* //}
* //// storing Each row and column value to excel sheet
* //for (int i = 0; i < dgv.Rows.Count - 1; i++)
* //{
* // colIndex = 0;
* // for (int j = 0; j < dgv.Columns.Count; j++)
* // {
* // if (dgv.Columns[j].Visible == true)
* // {
* // colIndex += 1;
* // worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? "";
* // }
* // }
* //}
*
* // storing header part in Excel
* for (int i = 0; i < dgv.ColumnCount; i++)
* {
* if (dgv.Columns[i].Visible == true)
* {
* colIndex += 1;
* worksheet.Cells[1, colIndex] = dgv.Columns[i].HeaderText;
* }
* }
* // storing Each row and column value to excel sheet
* for (int i = 0; i < dgv.Rows.Count; i++)
* {
* colIndex = 0;
* for (int j = 0; j < dgv.ColumnCount; j++)
* {
* if (dgv.Columns[j].Visible == true)
* {
* colIndex += 1;
* worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? "";
*
*
* }
* }
* }
*
* Excel.Range usedRange;
* usedRange = worksheet.UsedRange;
*
* Excel.Range rangeStart = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row, usedRange.Column];
* Excel.Range rangeEnd = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row + usedRange.Rows.Count, usedRange.Column + usedRange.Columns.Count];
*
* string usedStartPos = rangeStart.Address;
* string usedEndPos = rangeEnd.Address;
*
* //// BackColor 설정
* //((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Interior.Color = ColorTranslator.ToOle(Color.Navy);
* //((Excel.Range)excelWodrksheet.get_Range("A2", "J2")).Interior.Color = ColorTranslator.ToOle(Color.RoyalBlue);
*
* //// Font Color 설정
* //((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Font.Color = ColorTranslator.ToOle(Color.White);
* //((Excel.Range)excelWorksheet.get_Range("A2", "J2")).Font.Color = ColorTranslator.ToOle(Color.White);
*
* // 상단 첫번째 Row 타이틀 볼드
* SetHeaderBold(worksheet, 1);
*
* // 자동 넓이 지정
* for (int i = 0; i < usedRange.Columns.Count; i++)
* {
* AutoFitColumn(worksheet, i+1);
* }
*
* // 타이틀 색상
* for (int i = 0; i < usedRange.Columns.Count; i++)
* {
* //worksheet.Cells[1, i+1].Interior.Color = Excel.XlRgbColor.rgbGrey;
* worksheet.Cells[1, i + 1].Interior.ColorIndex = 16;
* }
* // 행마다 배경색 번갈아가면서 넣기
* for (int i= 1; i< usedRange.Rows.Count; i++)
* {
*//*if (i % 2 == 0)
* worksheet.Cells.Rows[i + 1].Interior.ColorIndex = 15;
* else
* worksheet.Cells.Rows[i + 1].Interior.ColorIndex = 0;*//*
* for (int j = 1; j < usedRange.Columns.Count; j++)
* {
* if (i % 2 == 0)
* worksheet.Cells[i + 1, j].Interior.ColorIndex = 15;
* else
* worksheet.Cells[i + 1, j].Interior.ColorIndex = 0;
* }
*
*
* }
*
* // 선그리기
* usedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
* usedRange.Borders.ColorIndex = 1;
*
* // 정렬
* usedRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
* workbook.SaveAs(filePath, XlFileFormat.xlWorkbookNormal);
* workbook.Close();
* app.Quit();*/
#endregion
#region 엑셀에 차트 넣기
/*//차트화할 셀의영역
* Excel.Range chartArea = worksheet.get_Range("A1", "B30");
*
* //차트생성
* Excel.ChartObjects chart = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
*
* //차트위치
* Excel.ChartObject mychart = (Excel.ChartObject)chart.Add(100, 40, 800, 400);
*
* //차트 할당
* Excel.Chart chartPage = mychart.Chart;
*
* //차트의 데이터 셋팅
* chartPage.SetSourceData(chartArea, Excel.XlRowCol.xlColumns);
*
* //차트의 형태
* //chartPage.ChartType = Excel.XlChartType.xlCylinderColClustered;
* chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
* app.DisplayAlerts = false;
*
* // 작업관리자 프로세스 해제
* System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
* System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);*/
#endregion
}
/// <summary>
/// 将对象组绑定到Excel(当T为Datarow时必须传入ColumnHeader)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="strSheetName"></param>
/// <returns></returns>
public static IWorkbook FillExcelSheet <T>(IEnumerable <T> collection, string strSheetName, List <string> ColumnHeader = null, Boolean IsExcel2003 = true)
{
IWorkbook workbook = null;
ISheet worksheet = null;
if (IsExcel2003)
{
HSSFWorkbook workbookTemp = new HSSFWorkbook();
worksheet = workbookTemp.CreateSheet(strSheetName);
workbook = workbookTemp;
}
else
{
XSSFWorkbook workbookTemp = new XSSFWorkbook();
worksheet = workbookTemp.CreateSheet(strSheetName);
workbook = workbookTemp;
}
var type = typeof(T);
var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
int sheetCount = collection.Count() % 50000 == 0 ? collection.Count() / 50000 : collection.Count() / 50000 + 1;
for (int i = 0; i < sheetCount; i++)
{
bool hasWriteHeader = false;
int rowPos = 0;
IEnumerable <T> outputData = null;
if (i != 0)
{
worksheet = workbook.CreateSheet(strSheetName + "-" + i);
}
if (collection.Count() >= (i + 1) * 50000)
{
outputData = collection.Skip(i * 50000).Take(50000);
}
else
{
outputData = collection.Skip(i * 50000).Take(collection.Count() - i * 50000);
}
foreach (var item in outputData)
{
int colPos = 0;
if (!hasWriteHeader)
{
if (type == typeof(DataRow) || (ColumnHeader != null && ColumnHeader.Count > 0))
{
if (ColumnHeader != null)
{
foreach (string info in ColumnHeader)
{
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(info);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(info);
}
colPos++;
}
hasWriteHeader = true;
colPos = 0;
rowPos++;
}
else
{
hasWriteHeader = true;
}
}
else
{
foreach (var propertyInfo in properties)
{
var value = propertyInfo.GetValue(item, null);
if (propertyInfo != null && (propertyInfo.PropertyType == typeof(String) || propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?) || propertyInfo.PropertyType.IsPrimitive))
{
var customAttributes = propertyInfo.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (customAttributes.Any())
{
var attr = customAttributes.FirstOrDefault() as DescriptionAttribute;
var desc = attr.Description;
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
}
else
{
var desc = propertyInfo.Name;
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
}
colPos++;
}
else if (propertyInfo.PropertyType.BaseType == typeof(Enum))
{
var customAttributes = propertyInfo.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (customAttributes.Any())
{
var attr = customAttributes.FirstOrDefault() as DescriptionAttribute;
var desc = attr.Description;
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
}
else
{
var desc = propertyInfo.Name;
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc);
}
}
colPos++;
}
}
hasWriteHeader = true;
colPos = 0;
rowPos++;
}
}
if (type == typeof(DataRow) && ColumnHeader != null)
{
DataRow dr = item as DataRow;
foreach (string column in ColumnHeader)
{
var value = dr[column];
if (value != null && value.GetType() == typeof(DateTime))
{
value = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
}
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString());
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString());
}
colPos++;
}
rowPos++;
}
else
{
foreach (var propertyInfo in properties)
{
var value = propertyInfo.GetValue(item, null);
if (propertyInfo != null && (propertyInfo.PropertyType == typeof(String) || propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?) || propertyInfo.PropertyType.IsPrimitive))
{
if (value != null)
{
if (propertyInfo.DeclaringType == typeof(DateTime) || (value != null && value.GetType() == typeof(DateTime)) || propertyInfo.DeclaringType == typeof(DateTime?) || (value != null && value.GetType() == typeof(DateTime?)))
{
value = ((DateTime)value).ToString("yyyy-M-d H:mm:ss");
}
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString());
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString());
}
}
colPos++;
}
else if (propertyInfo.PropertyType.BaseType == typeof(Enum))
{
string tempValue = value.ToString() + ":" + (int)value;
if (value != null)
{
if (worksheet.GetRow(rowPos) != null)
{
worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(tempValue);
}
else
{
worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(tempValue);
}
}
colPos++;
}
}
rowPos++;
}
}
}
return(workbook);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetString("txtCusName");
_cid = DTRequest.GetString("hCusId");
_check1 = DTRequest.GetFormString("ddlcheck1");
_check2 = DTRequest.GetFormString("ddlcheck2");
_check3 = DTRequest.GetFormString("ddlcheck3");
_foresdate = DTRequest.GetFormString("txtforesdate");
_foreedate = DTRequest.GetFormString("txtforeedate");
_collect = DTRequest.GetFormString("ddlcollect");
_self = DTRequest.GetFormString("self");
_person = DTRequest.GetFormString("txtPerson");
_sign = DTRequest.GetFormString("ddlsign");
_money = DTRequest.GetFormString("txtMoney");
_oID = DTRequest.GetFormString("txtorderid");
_area = DTRequest.GetFormString("ddlarea");
_person1 = DTRequest.GetFormString("txtPerson1");
_sdate = DTRequest.GetFormString("txtsdate");
_edate = DTRequest.GetFormString("txtedate");
_num = DTRequest.GetFormString("txtnum");
_method1 = DTRequest.GetFormString("ddlmethod1");
BLL.ReceiptPayDetail bll = new BLL.ReceiptPayDetail();
DataTable dt = bll.GetList(this.pageSize, this.page, "rpd_type=0" + CombSqlTxt(), "rpd_adddate desc,rpd_id desc", manager, out this.totalCount, out decimal _tmoney, true, 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.CreateCell(10).SetCellValue("财务审批");
headRow.CreateCell(11).SetCellValue("总经理审批");
headRow.CreateCell(12).SetCellValue("付款人");
headRow.CreateCell(13).SetCellValue("实付日期");
headRow.CreateCell(14).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;
headRow.GetCell(10).CellStyle = titleCellStyle;
headRow.GetCell(11).CellStyle = titleCellStyle;
headRow.GetCell(12).CellStyle = titleCellStyle;
headRow.GetCell(13).CellStyle = titleCellStyle;
headRow.GetCell(14).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);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 20 * 256);
sheet.SetColumnWidth(12, 20 * 256);
sheet.SetColumnWidth(13, 20 * 256);
sheet.SetColumnWidth(14, 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]["rpd_oid"].ToString());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["cb_bankName"]) + "\r\n" + Utils.ObjectToStr(dt.Rows[i]["cb_bankNum"]) + "\r\n" + Utils.ObjectToStr(dt.Rows[i]["cb_bank"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_content"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_money"]));
row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rpd_foredate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_personNum"]) + "-" + Utils.ObjectToStr(dt.Rows[i]["rpd_personName"]));
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_area"]));
row.CreateCell(9).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag1"].ToString())]);
row.CreateCell(10).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag2"].ToString())]);
row.CreateCell(11).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag3"].ToString())]);
row.CreateCell(12).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_confirmerName"]));
row.CreateCell(13).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(14).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_num"]));
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;
row.GetCell(10).CellStyle = cellStyle;
row.GetCell(11).CellStyle = cellStyle;
row.GetCell(12).CellStyle = cellStyle;
row.GetCell(13).CellStyle = cellStyle;
row.GetCell(14).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
/// <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;
}
}
/// <summary>
/// DataTable转换成Excel文档流并且添加超链接以及限制列宽
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream CreateExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
// using (IWorkbook workbook = new HSSFWorkbook())
// {
// using (ISheet sheet = workbook.CreateSheet())
// {
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
if (column.Caption == "URL地址" || column.Caption == "页面地址" || column.Caption == "url")
{
#region 设置超链接
ICell cell = dataRow.CreateCell(column.Ordinal); //创建单元格
cell.SetCellValue(row[column].ToString()); //设置显示文本
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url); //建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
link.Address = row[column].ToString(); //给HSSFHyperlink的地址赋值
cell.Hyperlink = link; //将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上
#endregion
#region 设置字体
IFont font = workbook.CreateFont(); //创建字体样式
font.Color = HSSFColor.Blue.Index; //设置字体颜色
ICellStyle style = workbook.CreateCellStyle(); //创建单元格样式
style.SetFont(font); //设置单元格样式中的字体样式
cell.CellStyle = style; //为单元格设置显示样式
#endregion
}
else
{
if (row[column].ToString().Length > 32767)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString().Substring(0, 32766));
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
}
}
rowIndex++;
}
SetSizeColumns(sheet); //设置列宽
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
// }
// }
return(ms);
}
