private DataTable ConvertRCPAExcelToDataTable(System.Web.HttpPostedFileBase postedFile)
{
IFileProvider fileProvider = new FileSystemProvider();
IExcelFactory excelFactory = new ExcelFactory();
CurrentInfo objCurInfo = new CurrentInfo();
string containerName = objCurInfo.GetCompanyCode().ToLower();
string fileName = postedFile.FileName;
string[] excelRetrieveColumns = new string[] { "*" };
_employeeExcelTemplateFileName = fileProvider.GetFilePathToSave(UPLOAD_PATH_KEY_NAME, fileName);
string whereQuery = " LEN(Region_Name) >0 ";
// postedFile.SaveAs(_employeeExcelTemplateFileName);
DataControl.Repository.AzureBlobUpload objAzureUpload = new Repository.AzureBlobUpload();
DataControl.Abstraction.IFileProvider objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("UPLOADEDFILEBLOBACCKEY");
string blobURL = objAzureUpload.PutAzureBlobStorage(postedFile.InputStream, postedFile.FileName, accKey, containerName);
System.IO.Stream stream = objAzureUpload.AzureblockDownload(postedFile.FileName, accKey, containerName);
DataTable dt = new DataTable();
dt = objAzureUpload.ConvertStreamToDataTable(stream, "D_Doctor_Name");
return(dt);
}
private string GetBlockOfExcelData(string fileName, string sheetName, int rowStart, int rowEnd)
{
const int MAX_COLUMN_NUMBER = 256;
ExcelFactory factory = ExcelFactory.CreateInstance(ExcelApplicationType.TMS, ExcelGarbageCollectionStyle.DesktopApplication);
ExcelApplication app = factory.AddApplication();
ExcelWorkbook bookInitial = app.AddWorkbook("initial", fileName);
ExcelWorksheet sheetInitial = bookInitial[sheetName];
ExcelWorkbook bookResult = app.AddWorkbook("final");
_context.Log.AddLogInformation("XLS-файл успешно загружен в память. Создан пустая результирующая книга.");
bookResult.CopyRange(bookInitial, sheetInitial.Number, rowStart, 1, rowEnd, MAX_COLUMN_NUMBER, 2, 1, 1);
_context.Log.AddLogInformation("Диапазон скопирован в новую книгу");
ExcelWorksheet sheetResult = bookResult[1];
for (int i = 0; i < MAX_COLUMN_NUMBER; i++)
{
sheetResult.WriteValue(1, i + 1, 1);
}
_context.Log.AddLogInformation("Заполнили единицами первую строку результирующего листа");
string fileNameResult = Path.Combine(_context.ExecutedPackage.PackagePath, Guid.NewGuid().ToString("N") + ".xls");
_context.Log.AddLogInformation("Готовы сохранить результирующий файл в '" + fileNameResult + "'");
bookResult.Save(fileNameResult);
_context.Log.AddLogInformation("Результирующий файл сохранен.");
return(fileNameResult);
}
public HttpResponseMessage Upload()
{
HttpPostedFile file = HttpContext.Current.Request.Files["school"];
ErrMsg msg = new ErrMsg();
IImport import = ExcelFactory.Instance().GetExcelImporter(new eh.impls.configurations.ExcelConfiguration(1, 0, 0), msg);
IList <School> list = SchoolDto.ToList(import.Import <SchoolDto>(file.InputStream));
if (msg.Count != 0)
{
return(ToJson(msg.GetErrors(), status_code: 0, msg: "fail"));
}
else
{
try
{
Service.Add(list);
return(ToJson("success"));
}
catch (SqlException ex)
{
msg.AddErrMsg(ex.Message);
return(ToJson(msg.GetErrors(), status_code: 0, msg: "fail"));
}
}
}
private static void Main()
{
// Save the time stamp and starting memory
Console.WriteLine(@"Starting ...");
var start = DateTime.Now;
var startMemory = _maxMemory = Process.GetCurrentProcess().WorkingSet64;
// Profile the excel generation
var factory = new ExcelFactory();
using (var ms = new MemoryStream()) {
using (var excel = factory.CreateWriter(ms)) {
excel.WriteRecords(GenerateRecords());
// Call close before we dispose of our classes, so we can measure the total memory used
excel.Close();
_maxMemory = Math.Max(Process.GetCurrentProcess().WorkingSet64, _maxMemory);
}
}
// Write out how long it took and how much memory was used
var usedMemory = _maxMemory - startMemory;
var elapsed = DateTime.Now - start;
Console.WriteLine(@"Done!");
Console.WriteLine(@"Process took: {0} minutes and {1} seconds", Math.Floor(elapsed.TotalMinutes), elapsed.Seconds);
Console.WriteLine(@"Process used: {0:F1}MB", usedMemory / 1048576.0);
}
/// <summary>
/// 业务逻辑
/// </summary>
/// <param name="excelId"></param>
/// <param name="accountItemIds"></param>
/// <param name="financialDataItemIds"></param>
/// <param name="qiJianTypeId"></param>
/// <returns></returns>
public Stream Process(int excelId, string accountItemIds, string financialDataItemIds, int qiJianTypeId, int onlyStatisticChildren, int xiangMuTreeTypeId, int statisticAccountChildren)
{
var result = new List <List <string> >();
var columnData = _financialDataExtendBLL.GetGridColumns(excelId, financialDataItemIds, onlyStatisticChildren, xiangMuTreeTypeId);
var gridData = _financialDataExtendBLL.GetGridData(excelId, accountItemIds, financialDataItemIds, qiJianTypeId, onlyStatisticChildren, xiangMuTreeTypeId, statisticAccountChildren);
var columnList = new List <string>();
foreach (var gridColumn in columnData.GridColumns)
{
columnList.Add(gridColumn.text);
}
result.Add(columnList);
foreach (var dataDic in gridData.rows)
{
var dataList = new List <string>();
foreach (var gridColumn in columnData.GridColumns)
{
dataList.Add(dataDic[gridColumn.dataIndex]);
}
result.Add(dataList);
}
var excelBuilder = ExcelFactory.CreateBuilder();
var sheet = excelBuilder.InsertSheet("统计数据");
sheet.InsertSheetContent(ExcelExportRequest.GetInstance(result));
var stream = new MemoryStream();
excelBuilder.Save(stream, SaveFormat.Xlsx);
stream.Position = 0;
return(stream);
}
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);
}
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);
}
/// <summary>
/// 业务逻辑
/// </summary>
/// <param name="excelId"></param>
/// <returns></returns>
public Stream Process(string ids)
{
var excelDatas = new List <List <string> >();
excelDatas.Add(GetExcelTitel());
var idsArr = ids.Split(',');
var companyList = new List <Company>();
var areas = _companyBll.GetAllArea();
foreach (var id in idsArr)
{
if (string.IsNullOrEmpty(id))
{
continue;
}
var idArr = id.Split('_');
var findId = Convert.ToInt32(idArr[1]);
if (idArr[0] == "1")
{
var companys = _companyBll.GetCompanyByAreaId(findId);
if (companys != null)
{
companyList.AddRange(companys);
}
}
else
{
var company = _companyBll.GetCompanyByKey(findId);
if (company != null)
{
companyList.Add(company);
}
}
}
foreach (var company in companyList)
{
var area = areas.FirstOrDefault(x => x.AreaId == company.AreaId);
var companyRecords = _companyBll.GetCompanyconnectrecordByCompanyId(company.CompanyId);
if (companyRecords != null && companyRecords.Count > 0)
{
foreach (var record in companyRecords)
{
excelDatas.Add(GetOneCellData(area, company, record));
}
}
else
{
excelDatas.Add(GetOneCellData(area, company, new CompanyConnectRecord()));
}
}
var excelBuilder = ExcelFactory.CreateBuilder();
var sheet = excelBuilder.InsertSheet("sheet1");
sheet.InsertSheetContent(ExcelExportRequest.GetInstance(excelDatas));
var stream = new MemoryStream();
excelBuilder.Save(stream, SaveFormat.Xlsx);
stream.Position = 0;
return(stream);
}
private DataTable ConvertCCMExcelToDataTable(System.Web.HttpPostedFileBase postedFile)
{
IFileProvider fileProvider = new FileSystemProvider();
IExcelFactory excelFactory = new ExcelFactory();
CurrentInfo objCurInfo = new CurrentInfo();
string containerName = objCurInfo.GetCompanyCode().ToLower();
string fileName = postedFile.FileName;
string[] excelRetrieveColumns = new string[] { "Row_No", "Customer_Name", "Sur_Name", "Speciality_Name", "Gender", "Qualification", "Address1",
"Address2", "Local_Area", "Pin_Code", "City", "State", "Country", "Phone", "Mobile", "Fax", "Email", "Hospital_Name", "Hospital_Classification",
"DOB", "Anniversary_Date", "Registration_No" };
_CCMExcelTemplateFileName = fileProvider.GetFilePathToSave(UPLOAD_PATH_KEY_NAME, fileName);
string whereQuery = " LEN(Customer_Name) >0 ";
DataControl.Repository.AzureBlobUpload objAzureUpload = new Repository.AzureBlobUpload();
DataControl.Abstraction.IFileProvider objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("UPLOADEDFILEBLOBACCKEY");
string blobURL = objAzureUpload.PutAzureBlobStorage(postedFile.InputStream, postedFile.FileName, accKey, containerName);
System.IO.Stream stream = objAzureUpload.AzureblockDownload(postedFile.FileName, accKey, containerName);
DataTable dt = new DataTable();
dt = objAzureUpload.ConvertStreamToDataTable(stream, "Customer_Name");
return(dt);
}
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 void CollectionExcelExportTest()
{
var datas = new List <Excel_Test>
{
new Excel_Test {
Name = "AAA", Age = 18, BirthDate = DateTime.Now, Enabled = true
},
new Excel_Test {
Name = "BBB", Age = 19, BirthDate = DateTime.Now, Enabled = true
},
new Excel_Test {
Name = "CCC", Age = 17, BirthDate = DateTime.Now, Enabled = true
},
new Excel_Test {
Name = "DDD", Age = 16, BirthDate = DateTime.Now, Enabled = true
},
new Excel_Test {
Name = "EEE", Age = 18, BirthDate = DateTime.Now, Enabled = true
}
};
var excel = ExcelFactory.Create("test_sheet", datas);
var bytes = excel.SaveAsBytes();
this.Save(@"D:\Collection_Test.xlsx", bytes);
Assert.IsNotNull(bytes);
}
// 加载指定路径的表格;
public static void LoadExcel(string className, string fileName)
{
#if UNITY_EDITOR
TextAsset textAsset = UnityEditor.AssetDatabase.LoadAssetAtPath <TextAsset>(fileName);
#else
TextAsset textAsset = Resources.Load <TextAsset>(fileName);
#endif
if (textAsset == null)
{
Debug.LogError(fileName + "加载失败");
return;
}
string excelContent = textAsset.text;
if (string.IsNullOrEmpty(excelContent))
{
//Debug.Log("loadfile=" + fileName);
using (StringReader reader = new StringReader(excelContent))
{
// 表头;
string lineData = reader.ReadLine();
while (!string.IsNullOrEmpty(lineData))
{
lineData = reader.ReadLine();
if (!string.IsNullOrEmpty(lineData))
{
string[] datas = lineData.Split(Separators, StringSplitOptions.None); // 不移除空格子
var line = ExcelFactory.InitExcelLine(className, datas);
}
}
}
}
}
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 void DataReaderExcelExportTest()
{
var sql = "SELECT Name, Sex, Age, Birthdate, Address FROM Students";
var dataReader = this.ExecuteReader(sql);
var excel = ExcelFactory.Create("test_sheet", dataReader);
var bytes = excel.SaveAsBytes();
this.Save(@"D:\DataReader_Test.xlsx", bytes);
}
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 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 byte[] ExecuteExcelExport(string sheetName)
{
var connectionOpt = this.GetConnectionOption();
var sqlQueryAndParms = this.GetSqlQueryAndParms(SelectClauseBuildMode.Raw);
var reader = DatabaseReader.Create(connectionOpt.Item1, connectionOpt.Item2)
.Reader.GetDataReader(sqlQueryAndParms.Item1, sqlQueryAndParms.Item2);
var excel = ExcelFactory.Create(sheetName, reader);
return(excel.SaveAsBytes());
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="eventArgs"></param>
private void ExportAllToExcel(object sender, EventArgs eventArgs)
{
SaveFileDialog sf = new SaveFileDialog {
OverwritePrompt = true, Filter = "Excel 2007-2010|*.xlsx|Excel 97-2003|*.xls", AutoUpgradeEnabled = true
};
if (sf.ShowDialog(this) != DialogResult.OK)
{
return;
}
ExportPart ep = new ExportPart {
SheetName = "Elräkningar",
AutoFitColumnWidths = true
};
ep.Headers.Add("Total förbrukning (kWh)");
ep.Headers.Add("Totalpris el");
ep.Headers.Add("Totalpris grundavgift");
ep.Headers.Add("OCR");
ep.Headers.Add("Datum föregående avläsning");
ep.Headers.Add("Förbrukning föregående avläsning");
ep.Headers.Add("Datum nuvarande avläsning");
ep.Headers.Add("Förbrukning nuvarande avläsning");
ep.Headers.Add("Del i grundavgift");
foreach (Bill b in Bills)
{
CellDataList cd = new CellDataList();
ep.Data.Add(cd);
cd.Add(EFD(b.TotalUsedKWh));
cd.Add(EFD(b.TotalPriceElectricity));
cd.Add(EFD(b.TotalPriceGroundFee));
cd.Add(b.OCR, false);
cd.Add(new DateTime(b.GuestLastReadingTicks).ToString("yyyy-MM-dd"));
cd.Add(EFD(b.GuestLastReadingKWh));
cd.Add(new DateTime(b.GuestCurrentReadingTicks).ToString("yyyy-MM-dd"));
cd.Add(EFD(b.GuestCurrentReadingKWh));
cd.Add(b.GuestPartInGroundFee);
}
if (File.Exists(sf.FileName))
{
try {
File.Delete(sf.FileName);
} catch (Exception ex) {
MessageBox.Show("Kan inte skriva över existerande fil: {0}".FillBlanks(ex.Message));
return;
}
}
using (Stream s = File.OpenWrite(sf.FileName)) {
ExcelFactory.ExportList(s, new List <ExportPart>(new[] { ep }), sf.FilterIndex == 1 ? FileFormat.OpenXMLWorkbook : FileFormat.Excel8);
s.Flush();
s.Close();
}
}
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));
}
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);
}
public void WriteXlsTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t1.xls";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 1);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
MemoryStream stream1 = new MemoryStream();
excel.Write(stream1, ds, ExcelExtendType.XLS);
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out1.xls";
bool result = excel.WriteFile(outputFilePath, stream1);
Assert.IsNotNull(result);
}
private void Refresh_Click(object sender, RoutedEventArgs e)
{
Histories = new ObservableCollection <HistoryGridModel>();
List <string> titleList = new List <string>();
List <string> contentList = new List <string>();
var instance = ExcelFactory.GetExcel();
instance.ReadHistory(CharToIntColumnName(ConfigExcelFormat.TitleColumn), CharToIntColumnName(ConfigExcelFormat.ContentColumn), titleList, contentList);
for (int i = 0; i < titleList.Count; i++)
{
Histories.Add(new HistoryGridModel()
{
Title = titleList[i], Description = contentList[i]
});
}
((System.Windows.Controls.Grid) this.Parent).Children.Add(new UserControlHistory());
}
private void ExcelMenuOnClick(object sender, EventArgs eventArgs)
{
if (selectedAccount == null)
{
return;
}
SaveFileDialog sf = new SaveFileDialog {
OverwritePrompt = true, Filter = "Excel 2007-2010|*.xlsx|Excel 97-2003|*.xls"
};
if (sf.ShowDialog(this) != DialogResult.OK)
{
return;
}
ExportPart ep = new ExportPart {
SheetName = selectedAccount.Name, AutoFitColumnWidths = true
};
ep.Headers.Add("Datum");
ep.Headers.Add("Summa");
ep.Headers.Add("Kommentar");
foreach (AccountTransaction t in selectedAccount.Transactions)
{
CellDataList cd = new CellDataList();
ep.Data.Add(cd);
cd.Add(t.Date);
string a = t.Amount.ToString();
if (a.Contains(","))
{
a = a.Replace(",", ".");
}
cd.Add(a);
cd.Add(t.Comment);
}
FileFormat format = sf.FilterIndex == 1 ? FileFormat.OpenXMLWorkbook : FileFormat.Excel8;
if (File.Exists(sf.FileName))
{
File.Delete(sf.FileName);
}
using (Stream stream = sf.OpenFile()) {
ExcelFactory.ExportList(stream, new List <ExportPart>(new[] { ep }), format);
stream.Flush();
}
}
public void TestImport()
{
ErrMsg msg = new ErrMsg();
IImport import = ExcelFactory.Instance().GetExcelImporter(new ExcelConfiguration(1, 0, 0), msg);
FileStream fs = new FileStream(@"D:\projects\yueyouyuebei\src\TravelAgent.Web\TravelAgent.WebAPI\template\school.xls", FileMode.Open);
IList <School> list = import.Import <School>(fs);
if (msg.Count != 0)
{
msg.GetErrors().ForEach(s => { Console.WriteLine(s); });
return;
}
foreach (School item in list)
{
Console.WriteLine(item.ToString());
}
}
public ActionResult Export()
{
List <CompanyInfo> list = new Manager.EmployeeManager().GetCompanies(string.Empty);
IExcel excel = ExcelFactory.CreateDefault();
DataTable dataTable = Converts.ListToDataTable <CompanyInfo>(list);
dataTable.Columns["Name"].Caption = "单位名称";
dataTable.Columns["Code"].Caption = "组织机构代码";
dataTable.Columns["Representative"].Caption = "法人";
dataTable.Columns["AccountBalance"].Caption = "账户余额";
dataTable.Columns.Remove("CompanyId");
dataTable.Columns.Remove("AccountId");
dataTable.Columns.Remove("Positions");
dataTable.Columns.Remove("Remark");
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, dataSet, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Dispose();
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("单位信息", 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 static bool ParseConfig(string className)
{
string[] files = ExcelFactory.GetFiles(className);
ExcelFactory.ClearExcelFunc clearFunc = null;
if (ExcelFactory.ClearFunctions.TryGetValue(className, out clearFunc))
{
clearFunc();
}
for (int i = 0; i < files.Length; ++i)
{
string fileName = ExcelRootDirectory + files[i] + ".txt";
LoadExcel(className, fileName);
}
return(true);
}
private byte[] GetBytesOfAttachment()
{
IDataReader dataReader = null;
try
{
dataReader = this.GetDataReaderOfAttachment();
var sheetname = this._attachmentTopic.TopicName;
return(ExcelFactory.Create(sheetname, dataReader).SaveAsBytes());
}
catch (Exception)
{
if (dataReader != null && !dataReader.IsClosed)
{
dataReader.Close();
}
this.SetErrorStatus("Create the dataReader failure.");
return(null);
}
}
private static void Main()
{
// Save the time stamp
var start = DateTime.Now;
Console.WriteLine(@"Starting ...");
// Profile the excel generation
var factory = new ExcelFactory();
using (var ms = new MemoryStream()) {
using (var excel = factory.CreateWriter(ms)) {
excel.WriteRecords(GenerateRecords());
}
}
// Write out how long it took
var elapsed = DateTime.Now - start;
Console.WriteLine(@"Done!");
Console.WriteLine(@"Process took: {0} minutes and {1} seconds", Math.Floor(elapsed.TotalMinutes), elapsed.Seconds);
}
