private void CreateLRInsureContSheet(IExcel excelApp)
{
excelApp.SetCellValue(1, "A", "BusiNo");
excelApp.SetCellValue(1, "B", "CompanyCode");
excelApp.SetCellValue(1, "C", "ReInsuranceContNo");
excelApp.SetCellValue(1, "D", "ReInsuranceContName");
excelApp.SetCellValue(1, "E", "ReInsuranceContTitle");
excelApp.SetCellValue(1, "F", "MainReInsuranceContNo");
excelApp.SetCellValue(1, "G", "ContOrAmendmentType");
excelApp.SetCellValue(1, "H", "ContAttribute");
excelApp.SetCellValue(1, "I", "ContStatus");
excelApp.SetCellValue(1, "J", "TreatyOrFacultativeFlag");
excelApp.SetCellValue(1, "K", "ContSigndate");
excelApp.SetCellValue(1, "L", "PeriodFrom");
excelApp.SetCellValue(1, "M", "PeriodTo");
excelApp.SetCellValue(1, "N", "ContType");
excelApp.SetCellValue(1, "O", "ReinsurerCode");
excelApp.SetCellValue(1, "P", "ReinsurerName");
excelApp.SetCellValue(1, "Q", "ChargeType");
excelApp.SetColumnTextType("Sheet1", 1);
excelApp.SetColumnTextType("Sheet1", 2);
excelApp.SetColumnTextType("Sheet1", 15);
excelApp.SetColumnDateType("Sheet1", 11);
excelApp.SetColumnDateType("Sheet1", 12);
excelApp.SetColumnDateType("Sheet1", 13);
excelApp.Save();
}
private void CreateLJInvoiceSheet(IExcel excelApp)
{
excelApp.SetCellValue("Sheet1", 1, "A", "BusiNo");
excelApp.SetCellValue("Sheet1", 1, "B", "CompanyCode");
excelApp.SetCellValue("Sheet1", 1, "C", "InvoiceNo");
excelApp.SetCellValue("Sheet1", 1, "D", "InvoiceCode");
excelApp.SetCellValue("Sheet1", 1, "E", "TaxCompanyCode");
excelApp.SetCellValue("Sheet1", 1, "F", "Drawer");
excelApp.SetCellValue("Sheet1", 1, "G", "InvoiceClass");
excelApp.SetCellValue("Sheet1", 1, "H", "InvoiceType");
excelApp.SetCellValue("Sheet1", 1, "I", "InvoiceAmount");
excelApp.SetCellValue("Sheet1", 1, "J", "TaxAmount");
excelApp.SetCellValue("Sheet1", 1, "K", "SubFeeCode");
excelApp.SetCellValue("Sheet1", 1, "L", "SubFeeName");
excelApp.SetCellValue("Sheet1", 1, "M", "SubFee");
excelApp.SetCellValue("Sheet1", 1, "N", "SubTaxRate");
excelApp.SetCellValue("Sheet1", 1, "O", "SubTaxAmount");
excelApp.SetCellValue("Sheet1", 1, "P", "ProductCode");
excelApp.SetCellValue("Sheet1", 1, "Q", "ProductName");
excelApp.SetCellValue("Sheet1", 1, "R", "TaxpayerName");
excelApp.SetCellValue("Sheet1", 1, "S", "TaxpayerId");
excelApp.SetCellValue("Sheet1", 1, "T", "TaxpayerAddress");
excelApp.SetCellValue("Sheet1", 1, "U", "TaxpayerType");
excelApp.SetCellValue("Sheet1", 1, "V", "TaxpayerPhone");
excelApp.SetCellValue("Sheet1", 1, "W", "TaxpayerBankCode");
excelApp.SetCellValue("Sheet1", 1, "X", "TaxpayerBankAccount");
excelApp.SetCellValue("Sheet1", 1, "Y", "Currency");
excelApp.SetCellValue("Sheet1", 1, "Z", "InvioceState");
excelApp.SetCellValue("Sheet1", 1, "AA", "InvoicePrintDate");
excelApp.Save();
}
public ActionResult ImportPay()
{
if (Request.Files.Count == 0)
{
Object o = new
{
success = false,
data = "上传失败，请选择需要上传的EXCEL"
};
JsonResult r = Json(o, JsonRequestBehavior.AllowGet);
return(r);
}
string message = string.Empty;
string fileName = System.IO.Path.GetFileNameWithoutExtension(Request.Files[0].FileName);
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(Request.Files[0].InputStream);
DataTable dt = ds.Tables[0];
bool pass = new Manager.PayManager().ImportPaymentData(dt, fileName, ref message);
Object result = new
{
success = pass,
data = message
};
JsonResult jsonResult = Json(result, JsonRequestBehavior.AllowGet);
return(jsonResult);
}
private void CreateLRProductSheet(IExcel excelApp)
{
excelApp.SetCellValue(1, "A", "BusiNo");
excelApp.SetCellValue(1, "B", "CompanyCode");
excelApp.SetCellValue(1, "C", "ReInsuranceContNo");
excelApp.SetCellValue(1, "D", "ReInsuranceContName");
excelApp.SetCellValue(1, "E", "ReInsuranceContTitle");
excelApp.SetCellValue(1, "F", "MainReInsuranceContNo");
excelApp.SetCellValue(1, "G", "ContOrAmendmentType");
excelApp.SetCellValue(1, "H", "ProductCode");
excelApp.SetCellValue(1, "I", "ProductName");
excelApp.SetCellValue(1, "J", "GPFlag");
excelApp.SetCellValue(1, "K", "ProductType");
excelApp.SetCellValue(1, "L", "LiabilityCode");
excelApp.SetCellValue(1, "M", "LiabilityName");
excelApp.SetCellValue(1, "N", "ReinsurerCode");
excelApp.SetCellValue(1, "O", "ReinsurerName");
excelApp.SetCellValue(1, "P", "ReinsuranceShare");
excelApp.SetCellValue(1, "Q", "ReinsurMode");
excelApp.SetCellValue(1, "R", "ReInsuranceType");
excelApp.SetCellValue(1, "S", "TermType");
excelApp.SetCellValue(1, "T", "RetentionAmount");
excelApp.SetCellValue(1, "U", "RetentionPercentage");
excelApp.SetCellValue(1, "V", "QuotaSharePercentage");
excelApp.SetColumnTextType("Sheet1", 1);
excelApp.SetColumnTextType("Sheet1", 2);
excelApp.SetColumnTextType("Sheet1", 10);
excelApp.SetColumnTextType("Sheet1", 14);
excelApp.SetColumnTextType("Sheet1", 18);
excelApp.Save();
}
public ActionResult ExportPayDetail(string payMonth)
{
IExcel excel = ExcelFactory.CreateDefault();
PaymentManager payment = new PaymentManager();
DataSet ds = payment.ExportDetail(payMonth);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, ds, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Close();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(payMonth + "个税信息", System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
public ActionResult Import()
{
if (Request.Files.Count == 0)
{
Object failResult = new
{
success = false,
data = ""
};
JsonResult failJsonResult = Json(failResult, JsonRequestBehavior.AllowGet);
return(failJsonResult);
}
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(Request.Files[0].InputStream);
DataTable dt = ds.Tables[0];
string message;
bool pass = new EmployeeManager().ImportRecharges(dt, out message);
Object result = new
{
success = pass,
data = message
};
JsonResult jsonResult = Json(result, JsonRequestBehavior.AllowGet);
return(jsonResult);
}
public AppEngine(IExcel iExcel, IEventAggregator iAgregator, IOutput iOutput, IImport import, IBudgetRepository repository)
{
_IExcel = iExcel;
_iAggregator = iAgregator;
_iOutput = iOutput;
_import = import;
_repository = repository;
_iAggregator.SubsribeEvent(this);
string path = _repository.Options.Item.CategoryPath;
string reportPath = _repository.Options.Item.DocumentPath;
_repository.Categories.Load(path);
_repository.Reports.Load(reportPath);
_iOutput.ShowReports(_repository.Reports.Item.Reports);
_iAggregator.PublishEvent(new PathChangedEvent()
{
Path = path
});
MenuItem menuStructure = MenuFactory.GetMenu();
_iOutput.AttachMenu(menuStructure);
ProceedEditRules();
}
public ProdSuggest(IExcel e, IDriver d)
{
list = new List <string>();
Excel = e;
ByObj = By.Id("suggestions-template");
driver = d.WebDriver;
}
public ProdDescription(IExcel e, IDriver d)
{
list = new List <string>();
Excel = e;
ByObj = By.Id("feature-bullets");
driver = d.WebDriver;
}
public SiteTest(ITest t, IDriver b, IExcel e)
{
Test = t;
Driver = b;
Browse = Driver.WebDriver;
Browse.Url = Test.Website;
Excel = e;
}
private void AddNewSheet(string excelFilePath, IExcel excelApp)
{
excelApp.AddNewSheet(excelFilePath, "LRProduct");
excelApp.AddNewSheet(excelFilePath, "LRInsureCont");
excelApp.AddNewSheet(excelFilePath, "LRAccount");
excelApp.AddNewSheet(excelFilePath, "LJInvoiceRelation");
excelApp.AddNewSheet(excelFilePath, "LJInvoice");
}
public SearchResult(IExcel e, IDriver d)
{
list = new List <string>();
list2 = new List <List <string> >();
Excel = e;
ByObj = By.Id("atfResults");
driver = d.WebDriver;
}
public ProdInfo(IExcel e, IDriver d)
{
list = new List <string>();
list2 = new List <List <string> >();
Excel = e;
ByObj = By.Id("prodDetails");
driver = d.WebDriver;
}
public SaleOrderController(IContextService contextService, ISaleOrderQuery saleQuery, ICategoryQuery categoryQuery, ISaleReportFacade saleReportFacade, IExcel excelService)
{
_saleOrderQuery = saleQuery;
this._context = contextService;
this._categoryQuery = categoryQuery;
_saleReportFacade = saleReportFacade;
this._excelService = excelService;
}
public ProdReviews(IExcel e, IDriver d)
{
list = new List <string>();
list2 = new List <List <string> >();
Excel = e;
ByObj = By.Id("cr-medley-top-reviews-wrapper");
driver = d.WebDriver;
}
public void WriteMoreDataTypeTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t3.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(file);
DataTable dt = ds.Tables[0];
Assert.IsNotNull(ds);
}
public ReportController(IContextService contextService, IReportQuery reportQuery, IQuery query, IPurchaseSaleInventoryFacade psiFacade, ICategoryQuery categoryQuery, IExcel excelService)
{
_reportQuery = reportQuery;
this._context = contextService;
_query = query;
_purchaseSaleInventoryFacade = psiFacade;
this._categoryQuery = categoryQuery;
this._excelService = excelService;
}
public StoreInventoryController(IContextService contextService, IQuery query, IStoreInventoryQuery storeInventoryQuery, IAreaQuery areaQuery, ICategoryQuery categoryQuery, IExcel iexcel)
{
this._query = query;
this._storeInventoryQuery = storeInventoryQuery;
this._areaQuery = areaQuery;
this._context = contextService;
this._categoryQuery = categoryQuery;
this._excelService = iexcel;
}
private void CreateExcel(IExcel excelApp, string filePath)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
excelApp.CreateExcel(filePath);
// AddNewSheet(filePath);
}
public CatererController(ILogger <CatererController> logger, IGoogleMap googleMapSvc, IQRCodeGenerator QRCodeSvc, ICaterers catererSvc, UserManager <ApplicationUser> userManager, IExcel ExcelSvc, IItem ItemSvc)
{
_GoogleMapSvc = googleMapSvc;
_logger = logger;
_QRCodeSvc = QRCodeSvc;
_CatererSvc = catererSvc;
_userManager = userManager;
_ExcelSvc = ExcelSvc;
_ItemSvc = ItemSvc;
}
public void ReadTest2()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t2.XLSX";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 2);
int realCount = 10;
int count = dt.Rows.Count;
Assert.IsTrue(count == realCount);
}
public void ReadTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t1.xls";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(file);
int realCount = 7;
int count = ds.Tables[0].Rows.Count;
Assert.IsTrue(count == realCount);
}
public void Write(IExcel excel, string fileName)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
using (var stream = File.OpenWrite(fileName))
{
((NPoiExcelFile)excel).Excel.Write(stream);
}
}
/// <summary>
/// Get or create IWorkbook from file name.
/// </summary>
/// <param name="fileName">Full workbook file path to get or create.</param>
public NPOIWorkbook(IExcel excel)
{
try
{
Excel = excel;
_wb = GetNPOIWorkbook(Excel.FileName);
}
catch (Exception)
{
throw;
}
}
public ActionResult ImportorEditor()
{
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
string outmsg = string.Empty;
bool success = false;
if (files.Count > 0)
{
for (int i = 0; i < files.Count; i++)
{
HttpPostedFile file1 = files[i];
Stream stream = file1.InputStream;
DataTable dt = new DataTable();
string fileName = System.IO.Path.GetFileNameWithoutExtension(file1.FileName);
string fileType = System.IO.Path.GetExtension(file1.FileName).ToLower();
switch (fileType)
{
case ".csv":
CsvFileParser cfp = new CsvFileParser();
dt = cfp.TryParse(stream, out outmsg);
break;
case ".xls":
case ".xlsx":
success = false;
//outmsg = "文件：" + fileName + "的文件格式接口待开发！<br />";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(stream);
dt = ds.Tables[0];
break;
default:
success = false;
outmsg += "文件：" + fileName + "格式不支持！<br />";
break;
}
success = new Manager.PayManager().ImportInsurance(dt, fileName, ref outmsg);
}
}
else
{
outmsg += "未获取到文件，请重试！<br />";
}
if (success)
{
outmsg = "上传成功！<br />" + outmsg;
}
else
{
outmsg = "上传失败！<br />" + outmsg;
}
return(Json(outmsg));
}
/// <summary>
/// Gets a range object from an existing instance or new one
/// if source is null, then adds a new worksheet and return the used range
/// if source is Range, then return the range
/// if source is Worksheet, then return the used range
/// if source is string, then parse the source and return the range
/// </summary>
/// <param name="source">Reference, range or worksheet</param>
/// <returns>Range</returns>
public static IRange GetRange(object source)
{
//Get a running instance of Excel or create one
IExcel excel = ExcelExt.GetInstance();
//Handle null source
IWorksheets worksheets = excel.Worksheets;
if (source == null)
{
//Add a new sheet to the current workbook
IWorksheet lastWorksheet = worksheets[worksheets.Count];
IWorksheet newWorksheet = worksheets.Add(Type.Missing, lastWorksheet, Type.Missing, Type.Missing);
return(newWorksheet.UsedRange);
}
//Handle source as address
if (source is string)
{
//try parse worksheet name
var srctxt = source as string;
if (srctxt != null && srctxt.IndexOf('!') == -1 && srctxt.IndexOf(':') == -1)
{
try {
return(((IWorksheet)worksheets[source]).UsedRange);
} catch { }
}
//try parse range name
try {
return(excel[source, Type.Missing]);
} catch {
throw new ExcelSourceError("Failed to resolve the source:\n{0}", source);
}
}
//Handle source as Worksheet
IWorksheet worksheet = source as IWorksheet;
if (worksheet != null)
{
return(worksheet.UsedRange);
}
//Handle source as Range
IRange range = source as IRange;
if (range != null)
{
return(range);
}
throw new ExcelSourceError("Invalid type of source: {0}", source.GetType().Name);
}
private IWorksheet GetWorksheet(IExcel excel)
{
if (_workSheetIndex.HasValue)
{
return(excel.Workbook.Worksheets[_workSheetIndex.Value]);
}
if (!string.IsNullOrEmpty(_workSheetName))
{
return(excel.Workbook.GetWorksheet(_workSheetName));
}
return(null);
}
public ProcessumService(
IRepository repository,
IMemoryCache cache,
IExcel excel,
IConfiguration configuration,
ILogger<ProcessumService> logger)
{
_repository = repository;
_cache = cache;
_excel = excel;
_configuration = configuration;
_logger = logger;
}
/// <summary>
/// Get or create IWorkbook from file name.
/// </summary>
/// <param name="fileName">Full workbook file path to get or create.</param>
public EPPlusWorkbook(IExcel excel)
{
try
{
Excel = excel;
_package = new ExcelPackage(new FileInfo(Excel.FileName));
_wb = _package.Workbook;
}
catch (Exception)
{
throw;
}
}
private void Apply_Click(object sender, RoutedEventArgs e)
{
#if DEBUG
List <string> titleList = new List <string>();
List <string> contentList = new List <string>();
foreach (var item in Histories)
{
titleList.Add(item.Title);
contentList.Add(item.Description);
}
IExcel excel = ExcelFactory.GetExcel();
excel.SaveHistory(CharToIntColumnName(ConfigExcelFormat.TitleColumn), CharToIntColumnName(ConfigExcelFormat.ContentColumn), titleList, contentList);
#endif
}
public void WriteXlsxTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t2.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 2);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
TTemplate template = new TTemplate();
TRow R1 = new TRow();
TCell A = new TCell("A", "A", 0, 0); A.ColSpan = 2;
TCell B = new TCell("", "B", 0, 2);
TCell C = new TCell("C", "C", 0, 3); C.RowSpan = 2;
TCell D = new TCell("D", "D", 0, 4); D.RowSpan = 2;
TCell E = new TCell("", "E", 0, 5); E.ColSpan = 2;
R1.Cells.Add(A);
R1.Cells.Add(B);
R1.Cells.Add(C);
R1.Cells.Add(D);
R1.Cells.Add(E);
TRow R2 = new TRow();
TCell A1 = new TCell("A1", "A1", 1, 0);
TCell A2 = new TCell("A2", "A2", 1, 1);
TCell B1 = new TCell("B1", "B1", 1, 2);
TCell E1 = new TCell("E1", "E1", 1, 5);
TCell E2 = new TCell("E2", "E2", 1, 6);
R2.Cells.Add(A1);
R2.Cells.Add(A2);
R2.Cells.Add(B1);
R2.Cells.Add(E1);
R2.Cells.Add(E2);
TSheet tsheet = new TSheet();
tsheet.Name = "Sheet1";
tsheet.Title = "表格O";
tsheet.Head.Rows.Add(R1);
tsheet.Head.Rows.Add(R2);
template.Sheets.Add(tsheet);
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out2.xlsx";
bool result = excel.WriteFile(outputFilePath, ds, template);
Assert.IsTrue(result);
}
public void WriteXlsxTestByTemplate()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t4.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 1);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string templateName = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/template.xml";
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out4.xlsx";
bool result = excel.WriteFile(outputFilePath, ds, templateName);
Assert.IsTrue(result);
}
/// <summary>
/// 初始化Excel导出
/// </summary>
/// <param name="format">导出格式</param>
/// <param name="excel">Excel操作</param>
public ExcelExport( ExportFormat format, IExcel excel )
: base( format ) {
_excel = excel;
}
