public FileResult ExportCustomers()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Customers");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var customers = repo.All().ToList();
row1.CreateCell(0).SetCellValue("客戶名稱");
row1.CreateCell(1).SetCellValue("統一編號");
row1.CreateCell(2).SetCellValue("電話");
row1.CreateCell(3).SetCellValue("傳真");
row1.CreateCell(4).SetCellValue("地址");
row1.CreateCell(5).SetCellValue("電子郵件");
for (int i = 0; i < customers.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(customers[i].客戶名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(customers[i].統一編號.ToString());
rowtemp.CreateCell(2).SetCellValue(customers[i].電話.ToString());
rowtemp.CreateCell(3).SetCellValue(customers[i].傳真.ToString());
rowtemp.CreateCell(4).SetCellValue(customers[i].地址.ToString());
rowtemp.CreateCell(5).SetCellValue(customers[i].Email.ToString());
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "Customers.xls"));
}
/// <summary>
/// Calculates the height of a client anchor in points.
/// </summary>
/// <param name="sheet">the sheet the anchor will be attached to</param>
/// <returns>the shape height.</returns>
public float GetAnchorHeightInPoints(NPOI.SS.UserModel.ISheet sheet)
{
int y1 = Dy1;
int y2 = Dy2;
int row1 = Math.Min(Row1, Row2);
int row2 = Math.Max(Row1, Row2);
float points = 0;
if (row1 == row2)
{
points = ((y2 - y1) / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
else
{
points += ((256.0f - y1) / 256.0f) * GetRowHeightInPoints(sheet, row1);
for (int i = row1 + 1; i < row2; i++)
{
points += GetRowHeightInPoints(sheet, i);
}
points += (y2 / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
return(points);
}
static void DoMergeExcelSheet(NPOI.SS.UserModel.ISheet sheet)
{
string[] heads = new string[] { "姓名", "复训带教", "复训检查", "复训翻译" };
//增加列头
//设置列头直接进行单元格合并
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 1024;
for (int i = 0; i < heads.Length; i++)
{
int cellIndex = i > 0 ? 2 * i - 1 : i;
NPOI.SS.UserModel.ICell cell = row.CreateCell(cellIndex);
cell.SetCellValue(heads[i]);
}
for (int i = 0; i < heads.Length; i++)
{
if (i == 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
}
else
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 2 * i - 1, 2 * i));
}
}
}
public ActionResult ExportClientCouponCode(int parentId, int isBind, string channel)
{
CompanyClientManager manager = new CompanyClientManager();
var result = manager.SelectCouponCodeByParentId(parentId, isBind);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var fileName = channel + DateTime.Now.ToString("yyyy_MM_dd_HHmm") + ".xls";
row1.CreateCell(0).SetCellValue("渠道");
row1.CreateCell(1).SetCellValue("活动券码");
row1.CreateCell(2).SetCellValue("手机号");
row1.CreateCell(3).SetCellValue("创建时间");
if (result != null && result.Any())
{
for (var i = 0; i < result.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(channel);
rowtemp.CreateCell(1).SetCellValue(result[i].CouponCode);
rowtemp.CreateCell(2).SetCellValue(result[i].Telephone);
rowtemp.CreateCell(3).SetCellValue(result[i].CreatedTime.ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult 匯出客戶銀行資訊()
{
//建立Excel文件
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//新增sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//取得匯出資料List
List <客戶銀行資訊> dataList = repo客戶銀行資訊.All().ToList();
//给sheet1添加第一行的標題列
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("銀行名稱");
row1.CreateCell(1).SetCellValue("銀行代碼");
row1.CreateCell(2).SetCellValue("分行代碼");
row1.CreateCell(3).SetCellValue("帳戶名稱");
row1.CreateCell(4).SetCellValue("帳戶號碼");
row1.CreateCell(5).SetCellValue("客戶名稱");
//將資料逐筆寫入
for (int i = 0; i < dataList.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dataList[i].銀行名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(dataList[i].銀行代碼.ToString());
rowtemp.CreateCell(2).SetCellValue(dataList[i].分行代碼.ToString());
rowtemp.CreateCell(3).SetCellValue(dataList[i].帳戶名稱.ToString());
rowtemp.CreateCell(4).SetCellValue(dataList[i].帳戶號碼.ToString());
rowtemp.CreateCell(5).SetCellValue(dataList[i].客戶資料.客戶名稱.ToString());
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "客戶銀行資訊清單.xls"));
}
public static void ExportXLS(DataTable dt, string[] columns, string fileName)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");
NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(0);
for (int i = 0; i < columns.Length; i++)
{
row0.CreateCell(i).SetCellValue(columns[i]);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < columns.Length; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public FileResult ExportData()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("客戶名稱");
row1.CreateCell(1).SetCellValue("聯絡人數量");
row1.CreateCell(2).SetCellValue("銀行帳戶數量");
var i = 0;
foreach (var item in db.CustomView.AsEnumerable())
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.客戶名稱);
rowtemp.CreateCell(1).SetCellValue(item.聯絡人數量.ToString());
rowtemp.CreateCell(2).SetCellValue(item.銀行帳戶數量.ToString());
i++;
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "ReadCustomView.xls"));
}
public FileResult Export(string id)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建Excel文件的对象
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //给sheet1添加第一行的头部标题
row1.CreateCell(0).SetCellValue("角色");
row1.CreateCell(1).SetCellValue("帐号");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(3).SetCellValue("过期时间");
row1.CreateCell(4).SetCellValue("状态");
List <int> rids = new List <int>();
string[] ids = id.Split('_');
foreach (var d in ids)
{
if (!string.IsNullOrWhiteSpace(d))
{
rids.Add(int.Parse(d));
}
}
List <VModel.SyUserManager.Grid> list = Bll.SyUserBll.ExportByRoleIds(rids);
int i = 1;
foreach (var m in list)
{
NPOI.SS.UserModel.IRow r = sheet1.CreateRow(i);
if (m.RoleNames != null && m.RoleNames.Count > 0)
{
string roleName = "";
foreach (var name in m.RoleNames)
{
roleName += name + "，";
}
if (roleName != "")
{
roleName = roleName.Substring(0, roleName.Length - 1);
}
r.CreateCell(0).SetCellValue(roleName);
}
else
{
r.CreateCell(0).SetCellValue("");
}
r.CreateCell(1).SetCellValue(m.Account);
r.CreateCell(2).SetCellValue(m.Name);
r.CreateCell(3).SetCellValue(Common.Function.ConvertDate(m.ExpiresTime));
r.CreateCell(4).SetCellValue(Common.Dict.UserState.GetVal(m.IsEnabled));
i++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();// 写入到客户端
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
string fileName = "角色包含用户表.xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult Export(int appId)
{
//获取list数据
var checkList = AppSettingBusiness.GetAppSettings(appId); //db.InfoTables.Where(r => r.ProjectName != null).Select(r => new { r.ProjectName, r.InfoTypes, r.field, r.fieldtxt }).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("配置键");
row1.CreateCell(1).SetCellValue("配置值");
//....N行
//将数据逐步写入sheet1各个行
for (int i = 0; i < checkList.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(checkList[i].ConfigKey.ToString());
rowtemp.CreateCell(1).SetCellValue(checkList[i].ConfigValue.ToString());
//....N行
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssfff");
string fileName = "配置" + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
private void CreateHeader(NPOI.SS.UserModel.ISheet sheet, List <Product> products)
{
var row = sheet.CreateRow(0);
UInt16 colNbr = 0;
row.CreateCell(colNbr++).SetCellValue("koper_naam");
row.CreateCell(colNbr++).SetCellValue("koper_voornaam");
row.CreateCell(colNbr++).SetCellValue("koper_straat");
row.CreateCell(colNbr++).SetCellValue("koper_straat_nr");
row.CreateCell(colNbr++).SetCellValue("koper_bus");
row.CreateCell(colNbr++).SetCellValue("koper_gemeente");
row.CreateCell(colNbr++).SetCellValue("koper_telefoon");
//, <producten>,totaal [#producten], totaal_betaald,
foreach (var product in products)
{
row.CreateCell(colNbr++).SetCellValue(product.Name);
}
row.CreateCell(colNbr++).SetCellValue("totaal");
row.CreateCell(colNbr++).SetCellValue("totaal_betaald");
row.CreateCell(colNbr++).SetCellValue("haalt_zelf_af");
row.CreateCell(colNbr++).SetCellValue("opmerkingen");
row.CreateCell(colNbr++).SetCellValue("verkoper_naam");
row.CreateCell(colNbr++).SetCellValue("klas");
}
public static void WriteExcel(DataGridView dg, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && dg != null && dg.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("DG");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dg.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dg.Columns[i].HeaderText);
}
for (int i = 0; i < dg.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dg.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dg[j, i].Value));
}
}
// 写入到客户端.
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
MessageBox.Show("導出成功：" + filePath, "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public FileResult ExportBanks()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Banks");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var contactors = repo.All().ToList();
row1.CreateCell(0).SetCellValue("銀行名稱");
row1.CreateCell(1).SetCellValue("銀行代碼");
row1.CreateCell(2).SetCellValue("分行代碼");
row1.CreateCell(3).SetCellValue("帳戶名稱");
row1.CreateCell(4).SetCellValue("帳戶號碼");
row1.CreateCell(5).SetCellValue("客戶名稱");
for (int i = 0; i < contactors.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(contactors[i].銀行名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(contactors[i].銀行代碼.ToString());
rowtemp.CreateCell(2).SetCellValue(contactors[i].分行代碼.ToString());
rowtemp.CreateCell(3).SetCellValue(contactors[i].帳戶名稱.ToString());
rowtemp.CreateCell(4).SetCellValue(contactors[i].帳戶號碼.ToString());
rowtemp.CreateCell(5).SetCellValue(contactors[i].客戶資料.客戶名稱.ToString());
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "Banks.xls"));
}
public FileResult ExportData(string keyword)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("客戶名稱");
row1.CreateCell(1).SetCellValue("銀行名稱");
row1.CreateCell(2).SetCellValue("銀行代碼");
row1.CreateCell(3).SetCellValue("分行代碼");
row1.CreateCell(4).SetCellValue("帳戶名稱");
row1.CreateCell(5).SetCellValue("帳戶號碼");
var i = 0;
var data = bankRepo.Where(keyword).ToList();
foreach (var item in data)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.客戶資料.客戶名稱);
rowtemp.CreateCell(1).SetCellValue(item.銀行名稱);
rowtemp.CreateCell(2).SetCellValue(item.銀行代碼);
rowtemp.CreateCell(3).SetCellValue(Convert.ToString(item.分行代碼));
rowtemp.CreateCell(4).SetCellValue(item.帳戶名稱);
rowtemp.CreateCell(5).SetCellValue(Convert.ToString(item.帳戶號碼));
i++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "客戶銀行資訊.xls"));
}
/// <summary>
/// DefectCode 导出
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public FileResult DefectCodeExcel(DefectCodeSearchModel searchModel)
{
searchModel.PageSize = 1000;
var totalCount = 0;
var result = CodeBusiness.DefectCodeSearchResult(searchModel, out totalCount).ToList();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("Code Type");
row1.CreateCell(1).SetCellValue("No");
row1.CreateCell(2).SetCellValue("Code No");
row1.CreateCell(3).SetCellValue("Code Name(English)");
row1.CreateCell(4).SetCellValue("Code Name(Chinese)");
for (int i = 0; i < result.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(result[i].BDCodeType);
rowtemp.CreateCell(1).SetCellValue(result[i].BDCodeNo);
rowtemp.CreateCell(2).SetCellValue(result[i].BDCode);
rowtemp.CreateCell(3).SetCellValue(result[i].BDCodeNameEn);
rowtemp.CreateCell(4).SetCellValue(result[i].BDCodeNameCn);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var exportFileName = string.Format("{0}{1}.xls", "DefectCodeInfo", DateTime.Now.ToString("yyyyMMddHHmmss"));
return(File(ms, "application/vnd.ms-excel", exportFileName));
}
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int startRowIndex)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
dataSource.Columns.Add("Index", typeof(string));
dataSource.Columns.Add("IndexNPOI", typeof(string));
dataSource.Columns.Add("Name", typeof(string));
dataSource.Columns.Add("Description", typeof(string));
dataSource.Columns.Add("HexString", typeof(string));
// 获取Workbook。
NPOI.SS.UserModel.IWorkbook workbook = NPOIExtension.GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
// 获取Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
for (int i = startRowIndex; i < sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
for (int j = 0; j < dataSource.Columns.Count; j++)
{
dataSourceRow[j] = row.GetCell(j).ToString();
}
}
return(dataSource);
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
static void DoFillRowToExcelSheet(NPOI.SS.UserModel.ISheet sheet, List <ExcelHeadAttribute> rows)
{
//当前存在多少行数据
int ri = sheet.LastRowNum;
//文件未写入完成时如何统计当前已写入了多少行数据
int rowNumber = sheet.PhysicalNumberOfRows;//当前写入了多少行数据
}
public static string WriteToFile(string ExcelName, DataTable dt)
{
InitializeWorkbook(ExcelName);
NPOI.SS.UserModel.ISheet sheet1 = hssfworkbook.GetSheetAt(0);
int g = 3;
for (int i = 0; i < dt.Rows.Count; i++)
{
int cIndex = g + i;
sheet1.CreateRow(cIndex);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet1.GetRow(cIndex).CreateCell(j).CellStyle = sheet1.GetRow(1).GetCell(0).CellStyle;
sheet1.GetRow(cIndex).GetCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
sheet1.ForceFormulaRecalculation = true;
string basePath = AppDomain.CurrentDomain.BaseDirectory;
string dPath = "Excel\\" + ExcelName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string tPath = @basePath + dPath;
FileStream file = new FileStream(tPath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
return(tPath);
}
//第二种 导出的方式 NPIO方式
public ActionResult ExportToExcel2()
{
//创建工作簿
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
//创建 工作表
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("SheetTest"); ///通过 工作簿来创建一个 工作表
//创建Cell 单元格
NPOI.SS.UserModel.ICell cell1; //
int i = 0;
int rowLimit = 100;
DateTime originalTime = DateTime.Now;
for (i = 0; i < rowLimit; i++)
{
cell1 = sheet1.CreateRow(i).CreateCell(0);
cell1.SetCellValue("值" + i.ToString());
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
var buffer = ms.GetBuffer();
ms.Close();
return(File(buffer, "application/ms-excel", "test.xlsx"));
}
}
private static void ExportReportCharges(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
if (report.ChargeReport.Count > 0)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event Charges", allStyles.Header2Style);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Name", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 1, "Cost Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 2, "Discount Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 3, "Local Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 4, "State Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 5, "Actual Total", allStyles.TitleStyle);
foreach (var charge in report.ChargeReport.OrderBy(x => x.Name))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, charge.Name, allStyles.LeftAligned);
ReportUtilities.CreateCell(row, 1, charge.CostTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 2, charge.DiscountTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 3, charge.LocalTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 4, charge.StateTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 5, charge.ActualTotal, allStyles.Currency);
}
}
}
public void TestSpuriousSharedFormulaFlag()
{
long actualCRC = GetFileCRC(HSSFTestDataSamples.OpenSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC)
{
Console.Error.WriteLine("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); // Sheet1
String cellFormula;
cellFormula = GetFormulaFromFirstCell(s, 0); // row "1"
// the problem is1 not observable in the first row of the shared formula
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
// but the problem is1 observable in rows 2,3,4
cellFormula = GetFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.Equals("\"second formula\""))
{
throw new AssertionException("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
}
/**
* @param streamOffset start position for serialization. This affects values in some
* records such as INDEX, but most callers will be OK to pass zero.
* @return the {@link Record}s (in order) which will be output when the
* specified sheet is serialized
*/
public static Record[] GetRecords(NPOI.SS.UserModel.ISheet hSheet, int streamOffset)
{
RecordCollector rc = new RecordCollector();
((HSSFSheet)hSheet).Sheet.VisitContainedRecords(rc, streamOffset);
return(rc.Records);
}
/// <summary>
/// 添加合并区域
/// </summary>
/// <param name="sheet">NPOI工作表</param>
/// <param name="regionInfo">合并区域信息</param>
public static void AddMergedRegion(this NPOI.SS.UserModel.ISheet sheet, MergedRegionInfo regionInfo)
{
var region = new CellRangeAddress(regionInfo.FirstRow, regionInfo.LastRow, regionInfo.FirstCol,
regionInfo.LastCol);
sheet.AddMergedRegion(region);
}
public FileResult getFile()
{
List <Person> list = Option.getAwardInfo();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("qq");
row1.CreateCell(1).SetCellValue("昵称");
row1.CreateCell(2).SetCellValue("奖品");
row1.CreateCell(3).SetCellValue("有效发言次数");
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].qq);
rowtemp.CreateCell(1).SetCellValue(list[i].nickname);
rowtemp.CreateCell(2).SetCellValue(list[i].prize);
rowtemp.CreateCell(3).SetCellValue(list[i].speakNum);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "获奖表.xls"));
}
public void InsertByColIndex(NPOI.SS.UserModel.ISheet sheet, IEnumerable <T> lst, int startrow)
{
if (sheet == null)
{
throw new ArgumentException("sheet不能为Null");
}
if (lst == null)
{
throw new ArgumentException("value不能为Null");
}
if (dictMapHandlerByColIndex == null)
{
throw new ArgumentException("必须指定列索引和model之间的映射");
}
//int colIndex = 0;
int rowIndex = startrow;
//NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex++);
//foreach (var kv in dictMapHandlerByColName)
//{
// row.CreateCell(colIndex++).SetCellValue(kv.Key);
//}
foreach (var value in lst)
{
var row = sheet.CreateRow(rowIndex++);
//colIndex = 0;
foreach (var kv in dictMapHandlerByColIndex)
{
//dictMapHandler[kv.Value].SetValueToCell(value, row.CreateCell(kv.Key));
kv.Value(row.CreateCell(kv.Key), value);
}
}
}
public FileResult Export(string StoreId, string StartDate, string EndDate, int pi = 1)
{
using (DBContext db = new DBContext())
{
var storeQuery = db.Store.AsQueryable();
if (!string.IsNullOrEmpty(StoreId))
{
storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId));
}
var orderQuery = db.Order.AsQueryable();
DateTime now = DateTime.Now;
//不选择开始日期默认为本月1号
DateTime start = string.IsNullOrEmpty(StartDate) ? DateTime.Parse(string.Format("{0}/{1}/{2}", now.Year.ToString(), now.Month.ToString(), "01")) : DateTime.Parse(StartDate);
DateTime end = string.IsNullOrEmpty(EndDate) ? now : DateTime.Parse(EndDate).AddDays(1);
if (start > end)
{
DateTime temp = DateTime.MinValue;
temp = end;
end = start;
start = temp;
}
orderQuery = orderQuery.Where(q => q.SubmitTime.CompareTo(start) > 0 && q.SubmitTime.CompareTo(end) < 0);
var list = (from q in storeQuery
join o in orderQuery on q.ID equals o.StoreId into o_join
from os in o_join.DefaultIfEmpty()
group new { q.StoreName, os.Paid } by new { q.StoreName } into s
select new PSJE()
{
StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Paid == null ? 0 : p.Paid)
}).OrderByDescending(q => q.Pay).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("采购单位");
row1.CreateCell(1).SetCellValue("销售额");
string status = string.Empty;
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].StoreName);
rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString());
}
// 写入到客户端
return(ExportExcel(book, now.ToString("yyMMddHHmmssfff")));
}
}
private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 通过NPOI导出excel
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead)
{
if (iSheet == null)
{
throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。");
}
NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName);
}
int startRowIndex = 0;
if (firstRowIsColumnHead)
{
startRowIndex = 1;
}
for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++)
{
iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]);
}
}
}
private static void ExportReportFees(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
if (report.FeeReport.Count > 0)
{
var row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Event Fees", allStyles.Header2Style);
foreach (var feeType in report.FeeReport.GroupBy(x => x.FeeType).Select(x => x.Key))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, feeType.ToString(), allStyles.Header3Style);
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, "Cost", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 1, "Use Count", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 2, "Cost Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 3, "Discount Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 4, "Local Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 5, "State Tax Total", allStyles.TitleStyle);
ReportUtilities.CreateCell(row, 6, "Actual Total", allStyles.TitleStyle);
foreach (var fee in report.FeeReport.Where(x => x.FeeType == feeType).OrderBy(x => x.Cost))
{
row = sheet.CreateRow(rowNumber++);
ReportUtilities.CreateCell(row, 0, fee.Cost, allStyles.Currency);
ReportUtilities.CreateCell(row, 1, fee.UseCount, allStyles.RightAligned);
ReportUtilities.CreateCell(row, 2, fee.CostTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 3, fee.DiscountTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 4, fee.LocalTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 5, fee.StateTaxTotal, allStyles.Currency);
ReportUtilities.CreateCell(row, 6, fee.ActualTotal, allStyles.Currency);
}
row = sheet.CreateRow(rowNumber++);
}
}
}
public void CreateSheet(string sheetName)
{
bool isexistSheet = isExistSheet(sheetName);
if (!isexistSheet)
{
newsheet = excelWorkbook.CreateSheet(sheetName);
newsheet.ForceFormulaRecalculation = true;
}
}
public int CloneSheet(int sheetIndex)
{
int index = -1;
bool isexistSheet = isExistSheet(sheetIndex);
if (isexistSheet)
{
newsheet = excelWorkbook.CloneSheet(sheetIndex);
newsheet.ForceFormulaRecalculation = true;
index = excelWorkbook.GetSheetIndex(newsheet);
}
return index;
}
public void CreateSheet(string sheetName, List<SysConext.Rectangle> rectangleListOrderBylevel)
{
bool isexistSheet = isExistSheet(sheetName);
if (!isexistSheet)
{
newsheet = excelWorkbook.CreateSheet(sheetName);
newsheet.ForceFormulaRecalculation = true;
CreateHeader(rectangleListOrderBylevel);
}
}
public bool isExistSheet(string sheetName)
{
newsheet = excelWorkbook.GetSheet(sheetName);
if (newsheet != null)
{
newsheet.ForceFormulaRecalculation = true;
return true;
}
else
return false;
}
public bool isExistSheet(int sheetIndex)
{
newsheet = excelWorkbook.GetSheetAt(sheetIndex);
if (newsheet != null)
{
newsheet.ForceFormulaRecalculation = true;
return true;
}
else
return false;
}
