public bool exportFromExcel(DataGridView data, string pathToExcelFile)
{
if (File.Exists(pathToExcelFile))
{
File.Delete(pathToExcelFile);
}
FileInfo excelFile = new FileInfo(pathToExcelFile);
ExcelPackage excelPackage = new ExcelPackage(excelFile);
try
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Mã lớp";
worksheet.Cell(1, 2).Value = "Tên lớp";
worksheet.Cell(1, 3).Value = "Khoa";
//MessageBox.Show(data.Rows[6].Cells[2].Value.ToString());
//return false;
int rowCount = data.Rows.Count;
for (int r = 0; r < rowCount; r++)
{
for (int c = 0; c < 3; c++)
{
worksheet.Cell(r + 2, c + 1).Value = data.Rows[r].Cells[c].Value.ToString();
}
}
excelPackage.Save();
return(true);
}
catch
{
return(false);
}
finally
{
excelPackage.Dispose();
}
}
void AssertFileCorrect(String password)
{
using (OfficeCryptoStream s = OfficeCryptoStream.Open(TestFile, password))
{
using (ExcelPackage p = new ExcelPackage(s))
{
Assert.IsNotNull(p, "Cannot create package.");
ExcelWorksheet ws = p.Workbook.Worksheets["Test"];
Assert.IsNotNull(ws, "No Test worksheet.");
String cval = ws.Cell(1, 1).Value;
Assert.AreEqual("Test Cell", cval, "First cell value incorrect.");
}
}
}
public void Excel2007SearchAndReplace(string srcExcelName, string searchStr, string replaceStr)
{
FileInfo newFile = new FileInfo(srcExcelName);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
string content = null;
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
content = worksheet.Cell(1, 1).Value;
Regex regexText = new Regex(searchStr);
content = regexText.Replace(content, replaceStr);
worksheet.Cell(1, 1).Value = content;
try
{
xlPackage.Save();
}
catch
{
return;
}
}
}
private static int GetColumnCount(TableDescriptor descriptor, ExcelWorksheet worksheet)
{
var columnCount = descriptor.ColumnCount;
if (!descriptor.DynamicColumnCount)
{
return columnCount;
}
columnCount = 1;
while (!string.IsNullOrEmpty(worksheet.Cell(descriptor.HeaderRow, columnCount).Value))
{
columnCount++;
}
columnCount--;
return columnCount;
}
private static int GetColumnCount(TableDescriptor descriptor, ExcelWorksheet worksheet)
{
var columnCount = descriptor.ColumnCount;
if (!descriptor.DynamicColumnCount)
{
return(columnCount);
}
columnCount = 1;
while (!string.IsNullOrEmpty(worksheet.Cell(descriptor.HeaderRow, columnCount).Value))
{
columnCount++;
}
columnCount--;
return(columnCount);
}
public bool exportFromExcel(DataGridView data, string pathToExcelFile)
{
if (File.Exists(pathToExcelFile))
{
File.Delete(pathToExcelFile);
}
FileInfo excelFile = new FileInfo(pathToExcelFile);
ExcelPackage excelPackage = new ExcelPackage(excelFile);
try
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Mã Khoa";
worksheet.Cell(1, 2).Value = "Tên Khoa";
worksheet.Cell(1, 3).Value = "Trưởng Khoa";
worksheet.Cell(1, 4).Value = "P. Trưởng Khoa";
worksheet.Cell(1, 5).Value = "Email";
worksheet.Cell(1, 6).Value = "Địa chỉ";
worksheet.Cell(1, 7).Value = "Điện thoại";
int rowCount = data.Rows.Count;
for (int r = 0; r < rowCount; r++)
{
for (int c = 0; c < 7; c++)
{
worksheet.Cell(r + 2, c + 1).Value = data.Rows[r].Cells[c].Value.ToString();
}
}
excelPackage.Save();
return(true);
}
catch
{
return(false);
}
finally
{
excelPackage.Dispose();
}
}
void CreateTestWorkbook(String password)
{
using (OfficeCryptoStream s = OfficeCryptoStream.Create(TestFile))
{
s.Password = password;
using (ExcelPackage p = new ExcelPackage(s))
{
ExcelWorksheet ws = p.Workbook.Worksheets["Test"];
if (ws == null)
{
ws = p.Workbook.Worksheets.Add("Test");
}
ws.Cell(1, 1).Value = "Test Cell";
p.Save();
}
s.Save();
}
}
public void Finalize(ref ExcelWorkbook ew)
{
//Dirty fix - if you don't update at least one cell in EVERY
//worksheet, there's an exception when saving the excel file.
ExcelWorksheet accounts = ew.Worksheets["Accounts"];
ExcelWorksheet campaigns = ew.Worksheets["Campaigns"];
ExcelWorksheet adgroups = ew.Worksheets["AdGroups"];
ExcelWorksheet keywords = ew.Worksheets["Keywords"];
ExcelWorksheet gateways = ew.Worksheets["Gateways"];
ExcelWorksheet adtexts = ew.Worksheets["AdTexts"];
if (accounts != null)
{
accounts.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
if (campaigns != null)
{
campaigns.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
if (adgroups != null)
{
adgroups.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
if (keywords != null)
{
keywords.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
if (gateways != null)
{
gateways.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
if (adtexts != null)
{
adtexts.Cell(3, 24).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
}
}
public void CreateDummyExcelFile(string sPath)
{
FileInfo newFile = new FileInfo(sPath);
using (ExcelPackage xlPackage = new ExcelPackage(newFile)) {
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
// write some titles into column 1
worksheet.Cell(1, 1).Value = "Product";
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";
// write some values into column 2
worksheet.Cell(1, 2).Value = "Tins Sold";
ExcelCell cell = worksheet.Cell(2, 2);
cell.Value = "15"; // tins of Beans sold
string calcStartAddress = cell.CellAddress; // we want this for the formula
ExcelCell cell2 = worksheet.Cell(3, 2);
cell2.Value = "32"; // tins Carrots sold
string calcEndAddress = cell2.CellAddress; // we want this for the formula
worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);
worksheet.Column(1).Width = 15;
worksheet.HeaderFooter.oddHeader.CenteredText = "Tinned Goods Sales";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.oddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
worksheet.InsertRow(3);
xlPackage.Workbook.Properties.Title = "Sample 1";
xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
xlPackage.Save();
}
}
private static void excelPackageDataGroupMixedOne(ExcelWorksheet myWorksheet)
{
int hangY = 1;
string eStrHang = "", eStrNext = "";
try
{
FileStream aFile = new FileStream("excelPackage-dataGroupMixedOne.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* raw datas like:
* 小胖B 男
* 小胖B 20岁
* 小胖B 宅
* 小胖B 木讷
* After mix like:
* 小胖B 男 20岁 宅 木讷
*/
eStrNext = myWorksheet.Cell(hangY, 1).Value;
while (eStrNext != null && eStrNext != "")
{
string outPrint = "";
if (eStrNext == eStrHang)
{
outPrint += "\t" + myWorksheet.Cell(hangY, 2).Value;
hangY++;
// Write data to file.
sw.Write(outPrint);
if (myWorksheet.Cell(hangY, 1).Value != null)
{
eStrNext = myWorksheet.Cell(hangY, 1).Value;
}
else
{
break;
}
}
else
{
eStrHang = myWorksheet.Cell(hangY, 1).Value;
outPrint += "\t" + myWorksheet.Cell(hangY, 2).Value;
hangY++;
// Write data to file.
sw.Write("\n" + eStrHang + outPrint);
if (myWorksheet.Cell(hangY, 1).Value != null)
{
eStrNext = myWorksheet.Cell(hangY, 1).Value;
}
else
{
continue;
}
}
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch
{
throw new ApplicationException();
}
}
public static void taoexcelfilesv(DataGridView data, int sodong, string excelFilePath)
{
if (File.Exists(excelFilePath))
{
File.Delete(excelFilePath);
}
FileInfo newFile = new FileInfo(excelFilePath);
// FileInfo newFile_template = new FileInfo(@"C:\template\mauimportsv.xlsx");
ExcelPackage xlPackage = new ExcelPackage(newFile);//
try
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Ma";
worksheet.Cell(1, 2).Value = "Hoten";
worksheet.Cell(1, 3).Value = "Malop";
worksheet.Cell(1, 4).Value = "Bacdaotao";
worksheet.Cell(1, 5).Value = "Khoahoc";
worksheet.Cell(1, 6).Value = "Khoa";
worksheet.Cell(1, 7).Value = "Cmnd";
worksheet.Cell(1, 8).Value = "Ngaysinh";
worksheet.Cell(1, 9).Value = "Gioitinh";
worksheet.Cell(1, 10).Value = "Diachi";
worksheet.Cell(1, 11).Value = "Dienthoai";
for (int j = 0; j < sodong; j++)
{
//for (int i = 0; i < 11; i++)
worksheet.Cell(j + 2, 1).Value = data.Rows[j].Cells["ma"].Value.ToString();
worksheet.Cell(j + 2, 2).Value = data.Rows[j].Cells["hoten"].Value.ToString();
worksheet.Cell(j + 2, 3).Value = data.Rows[j].Cells["malopd"].Value.ToString();
worksheet.Cell(j + 2, 4).Value = data.Rows[j].Cells["bacdaotaod"].Value.ToString();
worksheet.Cell(j + 2, 5).Value = data.Rows[j].Cells["Khoahocd"].Value.ToString();
worksheet.Cell(j + 2, 6).Value = data.Rows[j].Cells["khoad"].Value.ToString();
worksheet.Cell(j + 2, 7).Value = data.Rows[j].Cells["cmndd"].Value.ToString();
worksheet.Cell(j + 2, 8).Value = data.Rows[j].Cells["ngaysinhd"].Value.ToString();
worksheet.Cell(j + 2, 9).Value = data.Rows[j].Cells["gioitinhd"].Value.ToString();
worksheet.Cell(j + 2, 10).Value = data.Rows[j].Cells["diachid"].Value.ToString();
worksheet.Cell(j + 2, 11).Value = data.Rows[j].Cells["dienthoaid"].Value.ToString();
}
xlPackage.Save();
}
catch
{
MessageBox.Show("bạn đã tao file Sinhvien.xlsx trong ổ C:/ rồi ,muốn tạo lại bạn hãy xóa nó và thực hiện lại");
}
finally
{
xlPackage.Dispose();
}
}
public ActionResult _ExportVolunteerToExcel(List <Volunteer> studList, string filename)
{
var physicalPath = Path.Combine(Server.MapPath("~/Download/"), filename);
FileInfo file = new FileInfo(physicalPath);
if (file.Exists)
{
file.Delete();
file = new FileInfo(physicalPath);
}
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add("Volunteer List");
xlWorkSheet.Cell(1, (int)ExcelFormat.NAME).Value = "Name";
xlWorkSheet.Cell(1, (int)ExcelFormat.GENDER).Value = "Gender";
xlWorkSheet.Cell(1, (int)ExcelFormat.EMAIL).Value = "Email";
xlWorkSheet.Cell(1, (int)ExcelFormat.PHONE).Value = "Phone";
xlWorkSheet.Cell(1, (int)ExcelFormat.ADDRESS).Value = "Adress";
xlWorkSheet.Cell(1, (int)ExcelFormat.INTRO).Value = "Brief Introduction";
xlWorkSheet.Cell(1, (int)ExcelFormat.COMMENT).Value = "Comments";
xlWorkSheet.Cell(1, (int)ExcelFormat.ORGANIZATION).Value = "Organization";
int row = 2;
foreach (var item in studList)
{
xlWorkSheet.Cell(row, (int)ExcelFormat.NAME).Value = item.Name;
xlWorkSheet.Cell(row, (int)ExcelFormat.GENDER).Value = SystemGender.ToStringGender(item.Gender);
xlWorkSheet.Cell(row, (int)ExcelFormat.EMAIL).Value = item.Email;
xlWorkSheet.Cell(row, (int)ExcelFormat.PHONE).Value = item.Phone ?? "Not Provided";
xlWorkSheet.Cell(row, (int)ExcelFormat.ADDRESS).Value = Server.HtmlEncode(item.Address ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.INTRO).Value = Server.HtmlEncode(item.BriefIntro ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.COMMENT).Value = Server.HtmlEncode(item.Note ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.ORGANIZATION).Value = item.Organization.Name;
row++;
}
xlWorkSheet.Column((int)ExcelFormat.NAME).Width = 15;
xlWorkSheet.Column((int)ExcelFormat.EMAIL).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.PHONE).Width = 20;
xlWorkSheet.Column((int)ExcelFormat.INTRO).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.ADDRESS).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.COMMENT).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.ORGANIZATION).Width = 25;
xlPackage.Save();
}
byte[] ByteFile = System.IO.File.ReadAllBytes(physicalPath);
MemoryStream m = new MemoryStream(ByteFile);
return(File(m, "application/vnd.ms-excel", filename));
}
/// <summary>
/// ExcelPackage组件方法生成Insert语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangInsertEachLineASentence(ExcelWorksheet myWorksheet)
{
int hangY = 0, lieXX = 0;
string eCellStr = "", langTop = "";
string basicStr = "INSERT INTO ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 3)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
FileStream aFile = new FileStream("InsertEach.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/**
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
* ('526','17','我想','你好啊','2014-09-20 21:33:25','230');
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
* ('527','18','不想','我好啊','2014-09-21 21:34:26','231');
* 同一个表中插入，每行只写一句话，并以分号结束，方便大量的数据多次、多进程处理。
**/
langTop = basicStr + myWorksheet.Cell(2, 1).Value + " (";
for (int j = 2; j < lieXX; j++)
{
langTop += myWorksheet.Cell(1, j).Value;
if (j != lieXX - 1)
{
langTop = langTop + ",";
}
}
//固定部分无须带入循环中生成
langTop += ") VALUES ("; //langTop = INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES (
for (int i = 2; i < hangY; i++)
{
string outPrint = "";
for (int j = 2; j < lieXX; j++)
{
outPrint += "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
}
//outPrint = '526','17','我想','你好啊','2014-09-20 21:33:25','230'
outPrint = langTop + outPrint + ");";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
protected override IExternalExcelCell CellInternal(int row, int col)
{
return(new OpenXmlExcelCell(_obj.Cell(row, col)));
}
public ActionResult ExportExcelLocalHelp(int registerEngryId)
{
var registerEngry = db.RegisterEntries.Find(registerEngryId);
string filename = "Local Help" + "-" + registerEngry.Time.ToString("MM-dd-yyyy-HH-mm") + ".xlsx";
var physicalPath = Path.Combine(Server.MapPath("~/Download/"), filename);
FileInfo file = new FileInfo(physicalPath);
if (file.Exists)
{
file.Delete();
file = new FileInfo(physicalPath);
}
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add("Local Help Registration");
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.NAME).Value = "Name";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.GENDER).Value = "Gender";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.MAJOR).Value = "Major";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.EMAIL).Value = "Email";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.PHONE).Value = "Phone";
int row = 2;
foreach (var item in registerEngry.NewStudents)
{
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.NAME).Value = item.CnName ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.GENDER).Value = SystemGender.ToStringGender(item.Gender);
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.MAJOR).Value = item.Major ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.EMAIL).Value = item.Email ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.PHONE).Value = item.Phone;
xlWorkSheet.Row(row).Style = (row % 2 == 0) ? "Good" : "Bad";
row++;
}
foreach (var item in registerEngry.GuestParticipants)
{
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.NAME).Value = item.Name ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.GENDER).Value = " ";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.MAJOR).Value = " ";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.EMAIL).Value = item.Email ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.PHONE).Value = item.Phone;
xlWorkSheet.Row(row).Style = (row % 2 == 0) ? "Good" : "Bad";
row++;
}
xlWorkSheet.Column((int)LocalHelpRegistration.NAME).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.GENDER).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.MAJOR).Width = 20;
xlWorkSheet.Column((int)LocalHelpRegistration.EMAIL).Width = 25;
xlWorkSheet.Column((int)LocalHelpRegistration.PHONE).Width = 25;
xlPackage.Save();
}
byte[] ByteFile = System.IO.File.ReadAllBytes(physicalPath);
MemoryStream m = new MemoryStream(ByteFile);
return(File(m, "application/vnd.ms-excel", filename));
}
// Excel package from http://www.codeplex.com/Wikipage?ProjectName=ExcelPackage
private void saveResultsToExcel(string path)
{
try
{
if (File.Exists(path))
{
File.Delete(path);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
FileInfo fileInfo = new FileInfo(path);
using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Simulation Results");
// change the sheet view layout/page
worksheet.View.PageLayoutView = false;
worksheet.Cell(2, 2).Value = "Predictors used ↓ | Benchmarks used →";
worksheet.Column(2).Width = 40;
int startRowIndex = 3;
int startColumnIndex = 3;
int rowIndex;
int columnIndex;
// row above
columnIndex = startColumnIndex;
foreach (KeyValuePair <BenchmarkInfo, int> benchmarkAndIndex in simulationResultsDictionary.BenchmarksWithIndices)
{
worksheet.Cell(2, columnIndex).Value = benchmarkAndIndex.Key.benchmarkName;
worksheet.Column(columnIndex).Width = 18;
columnIndex++;
}
worksheet.Cell(2, columnIndex).Value = "Arithmetic Mean";
worksheet.Column(columnIndex).Width = 17;
rowIndex = startRowIndex;
foreach (KeyValuePair <PredictorInfo, int> predictorAndIndex in simulationResultsDictionary.PredictorsWithIndices)
{
worksheet.Cell(rowIndex, 2).Value = predictorAndIndex.Key.description;
columnIndex = startColumnIndex;
foreach (KeyValuePair <BenchmarkInfo, int> benchmarkAndIndex in simulationResultsDictionary.BenchmarksWithIndices)
{
var benchmarkStatisticsResult = simulationResultsDictionary.getResult(predictorAndIndex.Value, benchmarkAndIndex.Value);
if (benchmarkStatisticsResult != null)
{
if (!double.IsNaN(benchmarkStatisticsResult.Accuracy))
{
worksheet.Cell(rowIndex, columnIndex).Value = (benchmarkStatisticsResult.Accuracy * 100).ToString(CultureInfo.InvariantCulture);
}
}
else
{
worksheet.Cell(rowIndex, columnIndex).Value = " ";
}
columnIndex++;
}
// average formula
worksheet.Cell(rowIndex, columnIndex).Formula = "AVERAGE(" + worksheet.Cell(rowIndex, startColumnIndex).CellAddress + ":" + worksheet.Cell(rowIndex, columnIndex - 1).CellAddress + ")";
rowIndex++;
}
// lets set the header text
worksheet.HeaderFooter.oddHeader.CenteredText = "Predictor Simulation Results";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.oddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.oddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.oddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
// set some core property values
excelPackage.Workbook.Properties.Title = "Predictor Simulation Results";
excelPackage.Workbook.Properties.Author = "Andrei Marinică & Alexandru Dorobanțiu";
excelPackage.Workbook.Properties.Subject = "Branch Prediction Simulation Results";
excelPackage.Workbook.Properties.Keywords = "Branch Prediction Simulation";
excelPackage.Workbook.Properties.Category = "Branch Prediction";
excelPackage.Workbook.Properties.Comments = "Results";
// save our new workbook and we are done!
excelPackage.Save();
}
}
protected void ExcelExport(DataTable dt)
{
try
{
SqlProcsNew sqlp = new SqlProcsNew();
DataSet dsH = new DataSet();
DataSet dsFetch = new DataSet();
int LineNumber = 0;
int ColCount = 0;
LineNumber = 5;
ColCount = 6;
//string FileName = "DiningRegister_July"+ "_" + DateTime.Now.Day + DateTime.Now.Month + DateTime.Now.Year + "_" + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".xls";
//string FileName = "DiningRegister_" + DateTime.Now.ToString("MMMM") + "_" + DateTime.Now.Year + ".xlsx";
string TFileName = "DiningRegisterTemplate_" + txtMonthYear.Text.ToString() + ".xslt";
//string TFileName = "DiningRegisterTemplate_" + txtMonthYear.Text.ToString() + ".xltx";
string FileName = "DiningRegister_" + txtMonthYear.Text.ToString() + ".xlsx";
string templateFilePath = Server.MapPath("~/DiningTemplate/" + TFileName);
string newFilePath = Server.MapPath("~/DiningDownload/" + FileName);
FileInfo newFile = new FileInfo(newFilePath);
FileInfo template = new FileInfo(templateFilePath);
using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
{
foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
{
//aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
aworksheet.Cell(1, 2).Value = ddlsession.SelectedItem.ToString();
}
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
int startrow = LineNumber;
int row = 0;
int col = 0;
try
{
for (int j = 0; j < ColCount; j++)
{
col++;
for (int i = 0; i < dt.Rows.Count; i++)
{
row = startrow + i;
ExcelCell cell = worksheet.Cell(row, col);
cell.Value = dt.Rows[i][j].ToString();
//row.Cells[i].Attributes.Add("style", "textmode");
}
}
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
for (int iCol = 1; iCol <= ColCount; iCol++)
{
ExcelCell cell = worksheet.Cell(startrow - 2, iCol);
for (int iRow = startrow; iRow <= row; iRow++)
{
worksheet.Cell(iRow, iCol).StyleID = cell.StyleID;
}
}
xlPackage.Save();
}
string attachment = "attachment; filename=" + newFilePath;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + FileName + ";");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.TransmitFile(newFilePath);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
/// <summary>
/// ExcelPackage组件方法生成Update语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangUpdate(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "UPDATE ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 4)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("Update.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* UPDATE `kswiki2`.`wish` SET status='12',text='dsf' WHERE id='1';
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " SET ";
for (int j = 3; j < lieXX; j++)
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + "=" + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
outPrint = outPrint + " ";
}
}
outPrint = outPrint + "WHERE " + myWorksheet.Cell(1, 2).Value + "=" + "'" + myWorksheet.Cell(i, 2).Value + "';";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
public bool exportFromExcel(DataGridView data, string pathToExcelFile)
{
if (File.Exists(pathToExcelFile))
{
File.Delete(pathToExcelFile);
}
FileInfo excelFile = new FileInfo(pathToExcelFile);
ExcelPackage excelPackage = new ExcelPackage(excelFile);
try
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Mã";
worksheet.Cell(1, 2).Value = "Họ tên";
worksheet.Cell(1, 3).Value = "Ngày sinh";
worksheet.Cell(1, 4).Value = "Giới tính";
worksheet.Cell(1, 5).Value = "Địa chỉ";
worksheet.Cell(1, 6).Value = "Điện thoại";
worksheet.Cell(1, 7).Value = "Email";
worksheet.Cell(1, 8).Value = "Mã khoa";
worksheet.Cell(1, 9).Value = "Trình độ";
worksheet.Cell(1, 10).Value = "Phân loại";
worksheet.Cell(1, 11).Value = "Quốc tịch";
worksheet.Cell(1, 12).Value = "Năng khiếu";
//MessageBox.Show(data.Rows[6].Cells[2].Value.ToString());
//return false;
int rowCount = data.Rows.Count;
for (int r = 0; r < rowCount; r++)
{
for (int c = 0; c < 12; c++)
{
worksheet.Cell(r + 2, c + 1).Value = data.Rows[r].Cells[c].Value.ToString();
}
}
excelPackage.Save();
return(true);
}
catch
{
return(false);
}
finally
{
excelPackage.Dispose();
}
}
public string ReadExcelFile(string FileName)
{
bool ConfigExist = true;
xmlConfigInputExcelFile = SingleSessionConfig.Instance().GetXmlConfigInputExcelFile();
if (xmlConfigInputExcelFile == null || xmlConfigInputExcelFile.ColInfos == null)
{
xmlConfigInputExcelFile = new XmlConfigInputExcelFile()
{
ColInfos = new List <ColInfo>(), NbCol = 0, FamilleName = "", ConfigOKForUpdateArticle = false
};
ConfigExist = false;
}
//this.xmlConfigInputExcelFile = xmlConfigInputExcelFile;
int NumOnglet = 1;
lastStatutFichier = new StatutFichier()
{
ListExcellLine = new List <ExcelLine>(), ListeColonnes = new List <ExcelColonne>(), ChampsColonnes = new List <string>()
};
try {
FileInfo template = new FileInfo(FileName);
using (ExcelPackage xlPackage = new ExcelPackage(template)) {
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[NumOnglet];
// Calcul du nombre de ligne consécutives
int RowBase = 1;
string sValeur = "";
do
{
sValeur = worksheet.Cell(RowBase, 1).Value;
RowBase++;
}while (!string.IsNullOrEmpty(sValeur));
// calcul du nombre de colonnes
int ColBase = 1;
do
{
sValeur = worksheet.Cell(1, ColBase).Value;
ColBase++;
}while (!string.IsNullOrEmpty(sValeur));
RowBase--;
ColBase--;
lastStatutFichier.NbRow = RowBase;
lastStatutFichier.NbCol = ColBase;
for (int row = 1; row < RowBase; row++)
{
var line = new ExcelLine()
{
LineRow = row, listValues = new List <string>(), listValuesExtended = new List <ValueExtended>()
};
for (int col = 1; col < ColBase; col++)
{
string varCellule = worksheet.Cell(row, col).Value;
if (string.IsNullOrEmpty(varCellule))
{
//varCellule = "___";
}
if (row == 1)
{
ExcelColonne excelColonne = new ExcelColonne()
{
Nom = varCellule, NumColonne = col
};
lastStatutFichier.ListeColonnes.Add(excelColonne);
lastStatutFichier.ChampsColonnes.Add(varCellule); // old..
}
else
{
line.listValues.Add(varCellule);
ValueExtended valueExtended = new ValueExtended()
{
PosCol = col, Value = varCellule
};
line.listValuesExtended.Add(valueExtended);
}
}
lastStatutFichier.ListExcellLine.Add(line);
}
if (ConfigExist) // Test si compatibilité du fichier excel avec Config Fichier
{
lastStatutFichier.Message = "Config excel available : ";
lastStatutFichier.ConfigOKForUpdateArticle = xmlConfigInputExcelFile.ConfigOKForUpdateArticle;
if (xmlConfigInputExcelFile.NbCol != lastStatutFichier.NbCol)
{
lastStatutFichier.Erreur = true;
lastStatutFichier.Message += "Col Number not match with config";
}
else
{
int NbreDifference = 0;
for (int i = 0; i < lastStatutFichier.NbCol; i++)
{
if (xmlConfigInputExcelFile.ColInfos[i].ColName != lastStatutFichier.ListeColonnes[i].Nom)
{
NbreDifference++;
}
}
if (NbreDifference > 0)
{
lastStatutFichier.Erreur = true;
lastStatutFichier.Message += "Col Name not match with config";
}
}
if (!lastStatutFichier.Erreur)
{
// SI tout est OK..
lastStatutFichier.Message += "Erreur sur xmlConfigInputExcelFile ( a regènèrer)";
}
}
else
{
lastStatutFichier.Message = "Config excel NOT available : Creation xmlConfigInputExcelFile ";
xmlConfigInputExcelFile.NbCol = lastStatutFichier.NbCol;
xmlConfigInputExcelFile.ColInfos = new List <ColInfo>();
foreach (var v in lastStatutFichier.ListeColonnes)
{
xmlConfigInputExcelFile.ColInfos.Add(new ColInfo()
{
ColPosition = v.NumColonne, ColName = v.Nom, ToArticleCodeCritere = "", ToArticleNomCritere = "", ToArticleTypeCritere = 0, IsCritereFamille = false, IsMandatory = true
});
}
}
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("SingleExcel", "ReadExcelFile ex :" + ex.Message);
}
return(lastStatutFichier.Message);
}
public ActionResult PricingDeployment(PricingDeployment objPricingDeployment, string Command, FormCollection collection, HttpPostedFileBase file, HttpPostedFileBase file1)
{
LoginController objLoginController = new LoginController();
List <UploadFileMaster> UploadFileMasterList = (List <UploadFileMaster>)Session["UploadFileMasterList"];
string strEntityID = collection["EntityID"];
string strIsSecuredID = collection["IsSecuredID"];
Int32 intEntityID = Convert.ToInt32(strEntityID.Split(',')[0]);
Int32 intIsSecuredID = Convert.ToInt32(strIsSecuredID.Split(',')[0]);
try
{
if (ValidateSession())
{
bool blnUploadStatus = false;
bool blnUploadDataStatus = true;
#region Price Deployment
if (Command == "PDUpload")
{
if (file != null && file.ContentLength > 0)
{
UploadFileMaster objUploadFileMaster = UploadFileMasterList.Find(delegate(UploadFileMaster oUploadFileMaster) { return(oUploadFileMaster.UploadTypeCode == "PDR"); });
string strFilePath = System.Web.HttpContext.Current.Server.MapPath("~/Uploads/");
string strFileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("dd_mmm_yyyy_hh_mm");
string strExtension = Path.GetExtension(file.FileName);
strFilePath += strFileName + strExtension;
file.SaveAs(strFilePath);
FileInfo newFile = new FileInfo(strFilePath);
string strSourceColumn = objUploadFileMaster.SourceColumn;
string[] arrSourceColumn = null;
if (strSourceColumn != "")
{
arrSourceColumn = strSourceColumn.Split('|');
}
DataTable dtData = new DataTable();
for (int i = 0; i < arrSourceColumn.Length; i++)
{
dtData.Columns.Add(arrSourceColumn[i]);
}
string strDestinationColumn = objUploadFileMaster.DestinationColumn;
string[] arrDestinationColumn = null;
if (strDestinationColumn != "")
{
arrDestinationColumn = strDestinationColumn.Split('|');
}
string strTableName = objUploadFileMaster.TableName;
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[objUploadFileMaster.SheetName];
string strVersion = worksheet.Cell(1, 2).Value.Trim();
if (strVersion == "")
{
strVersion = "0";
}
DataRow drNew;
for (int iRow = 4; iRow < 36; iRow++)
{
if (Convert.ToString(worksheet.Cell(iRow, 1).Value) != "")
{
drNew = dtData.NewRow();
var Days = worksheet.Cell(iRow, 1).Value.Split('-');
var MinDays = Days[0].Trim();
var MaxDays = Days[1].Trim();
drNew["VERSION"] = strVersion;
drNew["ENTITY_ID"] = intEntityID;
drNew["IS_SECURED_ID"] = intIsSecuredID;
drNew["MIN_DAYS"] = MinDays;//worksheet.Cell(iRow, 1).Value;
drNew["MAX_DAYS"] = MaxDays;
drNew["DEPLOYMENT_RATE"] = worksheet.Cell(iRow, 2).Value;
drNew["CREATED_DATE"] = DateTime.Now;
drNew["CREATED_BY"] = 1;
dtData.Rows.Add(drNew);
}
else
{
break;
}
}
}
string strMyConnection = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["SP_PRICINGConnectionString"]);
if (arrSourceColumn != null && arrDestinationColumn != null && arrSourceColumn.Length == arrDestinationColumn.Length)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strMyConnection))
{
bulkCopy.DestinationTableName = strTableName;
for (int i = 0; i < arrSourceColumn.Length; i++)
{
bulkCopy.ColumnMappings.Add(arrSourceColumn[i], arrDestinationColumn[i]);
}
bulkCopy.WriteToServer(dtData);
}
blnUploadStatus = true;
DataSet dsIV = new DataSet();
dsIV = General.ExecuteDataSet(objUploadFileMaster.ExtraProcedure);
}
else
{
blnUploadStatus = false;
}
if (blnUploadStatus)
{
ManageUploadFileInfo(0, file.FileName, strFilePath, blnUploadStatus, blnUploadDataStatus);
ViewBag.Message = "Imported successfully";
}
return(View(objPricingDeployment));
}
}
#endregion
#region Actual Deployment
else if (Command == "ADUpload")
{
if (file1 != null && file1.ContentLength > 0)
{
UploadFileMaster objUploadFileMaster = UploadFileMasterList.Find(delegate(UploadFileMaster oUploadFileMaster) { return(oUploadFileMaster.UploadTypeCode == "ADR"); });
string strFilePath = System.Web.HttpContext.Current.Server.MapPath("~/Uploads/");
string strFileName = Path.GetFileNameWithoutExtension(file1.FileName) + DateTime.Now.ToString("dd_mmm_yyyy_hh_mm");
string strExtension = Path.GetExtension(file1.FileName);
strFilePath += strFileName + strExtension;
file1.SaveAs(strFilePath);
FileInfo newFile = new FileInfo(strFilePath);
string strSourceColumn = objUploadFileMaster.SourceColumn;
string[] arrSourceColumn = null;
if (strSourceColumn != "")
{
arrSourceColumn = strSourceColumn.Split('|');
}
DataTable dtData = new DataTable();
for (int i = 0; i < arrSourceColumn.Length; i++)
{
dtData.Columns.Add(arrSourceColumn[i]);
}
string strDestinationColumn = objUploadFileMaster.DestinationColumn;
string[] arrDestinationColumn = null;
if (strDestinationColumn != "")
{
arrDestinationColumn = strDestinationColumn.Split('|');
}
string strTableName = objUploadFileMaster.TableName;
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[objUploadFileMaster.SheetName];
string strVersion = worksheet.Cell(1, 2).Value.Trim();
if (strVersion == "")
{
strVersion = "0";
}
DataRow drNew;
for (int iRow = 4; iRow < 100; iRow++)
{
if (Convert.ToString(worksheet.Cell(iRow, 1).Value) != "")
{
drNew = dtData.NewRow();
var Days = worksheet.Cell(iRow, 1).Value.Split(' ');
var MinDays = Days[0].Trim();
var MaxDays = Days[2].Trim();
drNew["VERSION"] = strVersion;
drNew["MIN_DAYS"] = MinDays;//worksheet.Cell(iRow, 1).Value;
drNew["MAX_DAYS"] = MaxDays;
drNew["DEPLOYMENT_RATE"] = worksheet.Cell(iRow, 2).Value;
drNew["CREATED_DATE"] = DateTime.Now;
drNew["CREATED_BY"] = 1;
dtData.Rows.Add(drNew);
}
else
{
break;
}
}
}
string strMyConnection = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["SP_PRICINGConnectionString"]);
if (arrSourceColumn != null && arrDestinationColumn != null && arrSourceColumn.Length == arrDestinationColumn.Length)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strMyConnection))
{
bulkCopy.DestinationTableName = strTableName;
for (int i = 0; i < arrSourceColumn.Length; i++)
{
bulkCopy.ColumnMappings.Add(arrSourceColumn[i], arrDestinationColumn[i]);
}
bulkCopy.WriteToServer(dtData);
}
blnUploadStatus = true;
DataSet dsIV = new DataSet();
dsIV = General.ExecuteDataSet(objUploadFileMaster.ExtraProcedure);
}
else
{
blnUploadStatus = false;
}
if (blnUploadStatus)
{
ManageUploadFileInfo(0, file1.FileName, strFilePath, blnUploadStatus, blnUploadDataStatus);
ViewBag.Message = "Imported successfully";
}
return(View(objPricingDeployment));
}
}
#endregion
#region Pricing Deployment Download
else if (Command == "PDDownload")
{
UploadFileMaster objUploadFileMaster = UploadFileMasterList.Find(delegate(UploadFileMaster oUploadFileMaster) { return(oUploadFileMaster.UploadTypeCode == "PDR"); });
string strFilePath = System.Web.HttpContext.Current.Server.MapPath(objUploadFileMaster.TemplateFileName);
if (System.IO.File.Exists(strFilePath))
{
FileInfo fileinfo = new FileInfo(strFilePath);
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + Path.GetFileName(strFilePath));
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", fileinfo.Length.ToString());
Response.WriteFile(fileinfo.FullName);
Response.End();
}
return(View(objPricingDeployment));
}
#endregion
return(View());
}
else
{
return(RedirectToAction("Login", "Login"));
}
}
catch (Exception ex)
{
UserMaster objUserMaster = new UserMaster();
objUserMaster = (UserMaster)Session["LoggedInUser"];
LogError(ex.Message, ex.StackTrace, "UnderlyingCreationController", "PricingDeployment Post", objUserMaster.UserID);
return(RedirectToAction("ErrorPage", "Login"));
}
}
/// <summary>
/// ExcelPackage组件方法生成Delete语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangDelete(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "DELETE FROM ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 2)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("Delete.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* delete from tableA where statusB='12'and textC='dsf';
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
if (lieXX == 2)
{
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " where "
+ myWorksheet.Cell(1, 2).Value + "='" + myWorksheet.Cell(i, 2).Value + "';";
}
else//如果大于2，就用3列的，大于3列的表格也只考虑3列数据！
{
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " where "
+ myWorksheet.Cell(1, 2).Value + "='" + myWorksheet.Cell(i, 2).Value
+ "' AND " + myWorksheet.Cell(1, 3).Value + "='" + myWorksheet.Cell(i, 3).Value
+ "';";
}
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
public ActionResult HolidayMaster(string Command, HttpPostedFileBase file)
{
if (ValidateSession())
{
}
FetchUploadFileMasterList();
List <UploadFileMaster> UploadFileMasterList = (List <UploadFileMaster>)Session["UploadFileMasterList"];
HolidayMaster hMaster = new HolidayMaster();
if (Command == "PDDownload")
{
UploadFileMaster objUploadFileMaster = UploadFileMasterList.Find(
delegate(UploadFileMaster oUploadFileMaster)
{
return(oUploadFileMaster.UploadTypeCode == "HM");
});
string strFilePath = System.Web.HttpContext.Current.Server.MapPath(objUploadFileMaster.TemplateFileName);
if (System.IO.File.Exists(strFilePath))
{
FileInfo fileinfo = new FileInfo(strFilePath);
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + Path.GetFileName(strFilePath));
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", fileinfo.Length.ToString());
Response.WriteFile(fileinfo.FullName);
Response.End();
}
}
if (Command == "PDUpload")
{
if (file != null && file.ContentLength > 0)
{
UploadFileMaster objUploadFileMaster = UploadFileMasterList.Find(delegate(UploadFileMaster oUploadFileMaster) { return(oUploadFileMaster.UploadTypeCode == "HM"); });
string strFilePath = System.Web.HttpContext.Current.Server.MapPath("~/Uploads/");
string strFileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("dd_mmm_yyyy_hh_mm");
string strExtension = Path.GetExtension(file.FileName);
strFilePath += strFileName + strExtension;
file.SaveAs(strFilePath);
FileInfo newFile = new FileInfo(strFilePath);
string strSourceColumn = objUploadFileMaster.SourceColumn;
string[] arrSourceColumn = null;
if (strSourceColumn != "")
{
arrSourceColumn = strSourceColumn.Split('|');
}
DataTable dtData = new DataTable();
for (int i = 0; i < arrSourceColumn.Length; i++)
{
dtData.Columns.Add(arrSourceColumn[i]);
}
string strDestinationColumn = objUploadFileMaster.DestinationColumn;
string[] arrDestinationColumn = null;
if (strDestinationColumn != "")
{
arrDestinationColumn = strDestinationColumn.Split('|');
}
string strTableName = objUploadFileMaster.TableName;
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[objUploadFileMaster.SheetName];
string strVersion = worksheet.Cell(1, 2).Value.Trim();
if (strVersion == "")
{
strVersion = "0";
}
DataRow drNew;
for (int iRow = 2; iRow < 36; iRow++)
{
if (Convert.ToString(worksheet.Cell(iRow, 1).Value) != "")
{
drNew = dtData.NewRow();
var Reason = worksheet.Cell(iRow, 1).Value;
var Holiday_Date = DateTime.FromOADate(double.Parse(worksheet.Cell(iRow, 2).Value));
drNew["REASON"] = Reason;//worksheet.Cell(iRow, 1).Value;
drNew["HOLIDAY_DATE"] = Convert.ToDateTime(Holiday_Date);
drNew["VERSION"] = 1;
drNew["ISACTIVE"] = 1;
dtData.Rows.Add(drNew);
}
else
{
break;
}
}
}
string strMyConnection = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["SP_PRICINGConnectionString"]);
if (arrSourceColumn != null && arrDestinationColumn != null && arrSourceColumn.Length == arrDestinationColumn.Length)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strMyConnection))
{
bulkCopy.DestinationTableName = strTableName;
for (int i = 0; i < arrSourceColumn.Length; i++)
{
bulkCopy.ColumnMappings.Add(arrSourceColumn[i], arrDestinationColumn[i]);
}
bulkCopy.WriteToServer(dtData);
}
blnUploadStatus = true;
}
else
{
blnUploadStatus = false;
}
if (blnUploadStatus)
{
ViewBag.Message = "Imported successfully";
}
return(View(hMaster));
}
}
return(View(hMaster));
}
private void button1_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel 2007 |*.xlsx";
saveFileDialog1.Title = "Save to Excel File";
saveFileDialog1.ShowDialog();
if (saveFileDialog1.FileName != "")
{
switch (saveFileDialog1.FilterIndex)
{
case 1:
FileInfo newFile = new FileInfo(saveFileDialog1.FileName);
if (newFile.Exists)
{
try
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(saveFileDialog1.FileName);
}
catch {
MessageBox.Show("Pembuatan file gagal. Pastikan file tidak digunakan");
return;
}
}
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
xlPackage.DebugMode = true;
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
//Title
worksheet.Cell(1, 1).Value = "Waktu";
for (int s = 1; s < 16; s++)
{
worksheet.Cell(1, s + 1).Value = "S " + s.ToString();
worksheet.Column(s + 1).Width = 5;
}
xxx = new string[listView1.Items.Count, 16];
for (int y = 0; y < listView1.Items.Count; y++)
{
for (int x = 0; x < 16; x++)
{
//xxx[y, x] = listView1.Items[y].SubItems[x].Text;
//y = 1, title
worksheet.Cell(y + 2, x + 1).Value = listView1.Items[y].SubItems[x].Text;
}
}
// lets set the header text
worksheet.HeaderFooter.oddHeader.CenteredText = "Rakapitulasi Suhu Beton";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.oddFooter.RightAlignedText =
string.Format("Halaman {0} dari {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.oddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.oddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
// change the sheet view to show it in page layout mode
worksheet.View.PageLayoutView = true;
// set some core property values
xlPackage.Workbook.Properties.Title = "Rakapitulasi Suhu Beton";
xlPackage.Workbook.Properties.Author = "Ristiana Dyah";
xlPackage.Workbook.Properties.Subject = "Lembar Rakapitulasi Suhu Beton";
xlPackage.Workbook.Properties.Keywords = "Suhu Beton";
xlPackage.Workbook.Properties.Category = "Laporan Rekapitulasi";
xlPackage.Workbook.Properties.Comments = "Rakapitulasi Suhu Beton dari pengukuran alat pengukur suhu beton";
// set some extended property values
xlPackage.Workbook.Properties.Company = "Universitas Muhammadiyah Purwokerto";
xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.ump.ac.id");
// set some custom property values
xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "Ristiana Dyah");
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "-");
xlPackage.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "Excel Package");
// save our new workbook and we are done!
xlPackage.Save();
}
break;
}
}
}
public void Generate(string templateFileName, DateTime date)
{
if (templateFileName == String.Empty ||
templateFileName == null)
{
throw new ArgumentException("Invalid template file name. Cannot be null or empty");
}
if (!File.Exists(templateFileName))
{
throw new FileNotFoundException("Could not find templat efile: " + templateFileName);
}
if (_source == null ||
_compare == null)
{
throw new Exception("Invalid source or compare data. Cannot be null.");
}
string reportFilePath = Path.GetDirectoryName(templateFileName);
if (!reportFilePath.EndsWith("\\"))
{
reportFilePath += "\\";
}
string reportFileName = "AdminAlert_" + DayCode.ToDayCode(date).ToString() + ".xlsx";
reportFileName = reportFilePath + reportFileName;
FileInfo newFile = new FileInfo(reportFileName);
FileInfo template = new FileInfo(templateFileName);
using (ExcelPackage ep = new ExcelPackage(newFile, template))
{
ExcelWorkbook ew = ep.Workbook;
ExcelWorksheet worksheet = ew.Worksheets["AdWords"];
//Start inserting rows from the begining.
int row = ADMIN_START_ROW;
IDictionaryEnumerator ide = _source.GetEnumerator();
while (ide.MoveNext())
{
AccountAllMeasures csv = (AccountAllMeasures)ide.Value;
//First the CSV Row.
worksheet.Cell(row, 1).Value = "Adwords - " + csv.AccountName;
worksheet.Cell(row, 2).Value = csv.ImpsCurrent.ToString();
worksheet.Cell(row, 3).Value = csv.ClicksCurrent.ToString();
worksheet.Cell(row, 4).Value = csv.CPCCurrent.ToString();
worksheet.Cell(row, 5).Value = csv.CostCurrent.ToString();
worksheet.Cell(row, 6).Value = csv.AveragePosition.ToString();
worksheet.Cell(row, 7).Value = csv.ConversionsCurrent.ToString();
worksheet.Cell(row, 8).Value = csv.PurchasesCurrent.ToString();
worksheet.Cell(row, 9).Value = csv.LeadsCurrent.ToString();
worksheet.Cell(row, 10).Value = csv.SignupsCurrent.ToString();
//Now - OLTP
int id = -1;
try
{
id = AccountAllMeasures.FromAccountName(csv.AccountName);
}
catch (Exception ex)
{
ex.ToString();
}
if (_compare.ContainsKey(id))
{
AccountAllMeasures oltp = (AccountAllMeasures)_compare[id];
worksheet.Cell(row + 1, 1).Value = "OLTP - " + oltp.AccountName;
worksheet.Cell(row + 1, 2).Value = oltp.ImpsCurrent.ToString();
worksheet.Cell(row + 1, 3).Value = oltp.ClicksCurrent.ToString();
worksheet.Cell(row + 1, 4).Value = oltp.CPCCurrent.ToString();
worksheet.Cell(row + 1, 5).Value = oltp.CostCurrent.ToString();
worksheet.Cell(row + 1, 6).Value = oltp.AveragePosition.ToString();
worksheet.Cell(row + 1, 7).Value = oltp.ConversionsCurrent.ToString();
worksheet.Cell(row + 1, 8).Value = oltp.PurchasesCurrent.ToString();
worksheet.Cell(row + 1, 9).Value = oltp.LeadsCurrent.ToString();
worksheet.Cell(row + 1, 10).Value = oltp.SignupsCurrent.ToString();
if (csv.ImpsCurrent != oltp.ImpsCurrent)
{
worksheet.Cell(row + 1, 2).Style = "Bad";
}
if (csv.ClicksCurrent != oltp.ClicksCurrent)
{
worksheet.Cell(row + 1, 3).Style = "Bad";
}
if (csv.CPCCurrent != oltp.CPCCurrent)
{
worksheet.Cell(row + 1, 4).Style = "Bad";
}
if (csv.CostCurrent != oltp.CostCurrent)
{
worksheet.Cell(row + 1, 5).Style = "Bad";
}
if (csv.AveragePosition != oltp.AveragePosition)
{
worksheet.Cell(row + 1, 6).Style = "Bad";
}
if (csv.ConversionsCurrent != oltp.ConversionsCurrent)
{
worksheet.Cell(row + 1, 7).Style = "Bad";
}
if (csv.PurchasesCurrent != oltp.PurchasesCurrent)
{
worksheet.Cell(row + 1, 8).Style = "Bad";
}
if (csv.LeadsCurrent != oltp.LeadsCurrent)
{
worksheet.Cell(row + 1, 9).Style = "Bad";
}
if (csv.SignupsCurrent != oltp.SignupsCurrent)
{
worksheet.Cell(row + 1, 10).Style = "Bad";
}
}
else
{
//Couldn't find it. Put 0 in everyone, and mark them all as "Bad".
worksheet.Cell(row + 1, 1).Value = "OLTP - " + csv.AccountName;
worksheet.Cell(row + 1, 2).Value = "0";
worksheet.Cell(row + 1, 2).Style = "Bad";
worksheet.Cell(row + 1, 3).Value = "0";
worksheet.Cell(row + 1, 3).Style = "Bad";
worksheet.Cell(row + 1, 4).Value = "0";
worksheet.Cell(row + 1, 4).Style = "Bad";
worksheet.Cell(row + 1, 5).Value = "0";
worksheet.Cell(row + 1, 5).Style = "Bad";
worksheet.Cell(row + 1, 6).Value = "0";
worksheet.Cell(row + 1, 6).Style = "Bad";
worksheet.Cell(row + 1, 7).Value = "0";
worksheet.Cell(row + 1, 7).Style = "Bad";
worksheet.Cell(row + 1, 8).Value = "0";
worksheet.Cell(row + 1, 8).Style = "Bad";
worksheet.Cell(row + 1, 9).Value = "0";
worksheet.Cell(row + 1, 9).Style = "Bad";
worksheet.Cell(row + 1, 10).Value = "0";
worksheet.Cell(row + 1, 10).Style = "Bad";
}
//Now panorama
string accountName = AccountAllMeasures.FromGoogleAccountName(csv.AccountName);
if (_bi != null && _bi.ContainsKey(accountName))
{
AccountAllMeasures panorama = (AccountAllMeasures)_bi[accountName];
worksheet.Cell(row + 2, 1).Value = "PANORAMA - " + panorama.AccountName;
worksheet.Cell(row + 2, 2).Value = panorama.ImpsCurrent.ToString();
worksheet.Cell(row + 2, 3).Value = panorama.ClicksCurrent.ToString();
worksheet.Cell(row + 2, 4).Value = panorama.CPCCurrent.ToString();
worksheet.Cell(row + 2, 5).Value = panorama.CostCurrent.ToString();
worksheet.Cell(row + 2, 6).Value = panorama.AveragePosition.ToString();
worksheet.Cell(row + 2, 7).Value = panorama.ConversionsCurrent.ToString();
worksheet.Cell(row + 2, 8).Value = panorama.PurchasesCurrent.ToString();
worksheet.Cell(row + 2, 9).Value = panorama.LeadsCurrent.ToString();
worksheet.Cell(row + 2, 10).Value = panorama.SignupsCurrent.ToString();
if (csv.ImpsCurrent != panorama.ImpsCurrent)
{
worksheet.Cell(row + 1, 2).Style = "Bad";
}
if (csv.ClicksCurrent != panorama.ClicksCurrent)
{
worksheet.Cell(row + 1, 3).Style = "Bad";
}
if (csv.CPCCurrent != panorama.CPCCurrent)
{
worksheet.Cell(row + 1, 4).Style = "Bad";
}
if (csv.CostCurrent != panorama.CostCurrent)
{
worksheet.Cell(row + 1, 5).Style = "Bad";
}
if (csv.AveragePosition != panorama.AveragePosition)
{
worksheet.Cell(row + 1, 6).Style = "Bad";
}
if (csv.ConversionsCurrent != panorama.ConversionsCurrent)
{
worksheet.Cell(row + 1, 7).Style = "Bad";
}
if (csv.PurchasesCurrent != panorama.PurchasesCurrent)
{
worksheet.Cell(row + 1, 8).Style = "Bad";
}
if (csv.LeadsCurrent != panorama.LeadsCurrent)
{
worksheet.Cell(row + 1, 9).Style = "Bad";
}
if (csv.SignupsCurrent != panorama.SignupsCurrent)
{
worksheet.Cell(row + 1, 10).Style = "Bad";
}
}
else
{
//Couldn't find it. Put 0 in everyone, and mark them all as "Bad".
worksheet.Cell(row + 2, 1).Value = "PANORAMA - " + csv.AccountName;
worksheet.Cell(row + 2, 2).Value = "0";
worksheet.Cell(row + 2, 2).Style = "Bad";
worksheet.Cell(row + 2, 3).Value = "0";
worksheet.Cell(row + 2, 3).Style = "Bad";
worksheet.Cell(row + 2, 4).Value = "0";
worksheet.Cell(row + 2, 4).Style = "Bad";
worksheet.Cell(row + 2, 5).Value = "0";
worksheet.Cell(row + 2, 5).Style = "Bad";
worksheet.Cell(row + 2, 6).Value = "0";
worksheet.Cell(row + 2, 6).Style = "Bad";
worksheet.Cell(row + 2, 7).Value = "0";
worksheet.Cell(row + 2, 7).Style = "Bad";
worksheet.Cell(row + 2, 8).Value = "0";
worksheet.Cell(row + 2, 8).Style = "Bad";
worksheet.Cell(row + 2, 9).Value = "0";
worksheet.Cell(row + 2, 9).Style = "Bad";
worksheet.Cell(row + 2, 10).Value = "0";
worksheet.Cell(row + 2, 10).Style = "Bad";
}
//Advance 3 rows.
row += 3;
}
worksheet.Cell(ADMIN_START_ROW + 1, 16).Value = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
worksheet.Cell(ADMIN_START_ROW + 2, 16).Value = date.ToString("dd/MM/yyyy HH:mm:ss");
ExcelWorksheet boHistory = ew.Worksheets["BOHistory"];
ExcelWorksheet bo_ef = ew.Worksheets["BO_EF"];
if (_bo != null)
{
//Add the BO sheet.
int boRow = ADMIN_START_ROW;
IDictionaryEnumerator boe = _bo.GetEnumerator();
if (boHistory != null)
{
while (boe.MoveNext())
{
AccountAllMeasures boData = (AccountAllMeasures)boe.Value;
boHistory.Cell(boRow, 1).Value = boData.AccountName;
boHistory.Cell(boRow, 2).Value = boData.ClicksCurrent.ToString();
boHistory.Cell(boRow, 3).Value = boData.LeadsCurrent.ToString();
boHistory.Cell(boRow, 4).Value = boData.BONewUsersCurrent.ToString();
boHistory.Cell(boRow, 5).Value = boData.BONewActivationsCurrent.ToString();
boHistory.Cell(boRow, 6).Value = boData.SumOfActiveUsers.ToString();
boHistory.Cell(boRow, 7).Value = boData.SumOfNewNetDeposits.ToString();
boHistory.Cell(boRow, 8).Value = boData.SumOfTotalNetDeposits.ToString();
boHistory.Cell(boRow, 9).Value = boData.SumOfClientSpecific1.ToString();
boHistory.Cell(boRow, 10).Value = boData.SumOfClientSpecific2.ToString();
boHistory.Cell(boRow, 11).Value = boData.SumOfClientSpecific3.ToString();
boHistory.Cell(boRow, 12).Value = boData.SumOfClientSpecific4.ToString();
boHistory.Cell(boRow, 13).Value = boData.SumOfClientSpecific5.ToString();
boRow++;
}
}
boe.Reset();
boRow = ADMIN_START_ROW;
if (bo_ef != null)
{
while (boe.MoveNext())
{
AccountAllMeasures boef = (AccountAllMeasures)boe.Value;
//Hack for easy forex only!
if (boef.AccountID == 7)
{
bo_ef.Cell(boRow, 2).Value = boef.LeadsCurrent.ToString();
bo_ef.Cell(boRow, 3).Value = boef.BONewUsersCurrent.ToString();
bo_ef.Cell(boRow, 4).Value = boef.BONewActivationsCurrent.ToString();
bo_ef.Cell(boRow, 5).Value = boef.SumOfActiveUsers.ToString();
bo_ef.Cell(boRow, 6).Value = boef.SumOfNewNetDeposits.ToString();
bo_ef.Cell(boRow, 7).Value = boef.SumOfTotalNetDeposits.ToString();
}
}
}
}
else
{
boHistory.Cell(100, 1).Value = "1";
bo_ef.Cell(100, 1).Value = "1";
}
ep.Save();
_generatedFileName = reportFileName;
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column=ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while(column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if(cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
/// <summary>
/// ExcelPackage组件方法生成UpdateOnly语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
private static void excelPackagePrintSQLLangUpdateOnly(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "UPDATE ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 4)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
try
{
FileStream aFile = new FileStream("UpdateOnly.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* Excel File Likes：(Sheet Name = DataBase Table name = String "update_only")
* tables_name name age sex
* video_category 张松溪 22 1
* video_category 宋远桥 33 2
* video_category 俞岱岩 44 1
* video_category 张三丰 55 1
* video_category 殷梨亭 66 2
* UPDATE `update_only`.`video_category` SET age='22',sex='1' WHERE 'id'=
* (SELECT 'id' FROM `update_only`.`video_category` WHERE 'name'='张松溪');
* 减少部分语句，假设已经切换到当前数据库：
* UPDATE `video_category` SET age='22',sex='1' WHERE 'id'=
* (SELECT 'id' FROM `video_category` WHERE 'name'='张松溪');
*/
for (int i = 2; i < hangY; i++)
{
string outPrint;
outPrint = basicStr + myWorksheet.Cell(i, 1).Value + " SET ";
for (int j = 3; j < lieXX; j++)
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + "=" + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
outPrint = outPrint + " ";
}
}
//假设主键名称为'id'
outPrint = outPrint + "WHERE '" + FormMain.primaryKeyName +
"'=" + "(SELECT '" + FormMain.primaryKeyName + "' FROM '" + myWorksheet.Cell(i, 1).Value + "' WHERE '"
+ myWorksheet.Cell(1, 2).Value + "'='" + myWorksheet.Cell(i, 2).Value + "');";
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}
catch (Exception ex)
{
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
private void ExportExcel()
{
var list = (PayCardInfo[])Session[sSessionList];
if (list == null || list.Count() == 0)
{
Alert.Show("Không có dữ liệu!");
return;
}
try
{
string sdate = DateTime.Now.ToString("yyyy_MM_dd_HHmmss");
string fileName = sdate + ".xlsx";
var fullpath = Server.MapPath("~/PayCard/Export/" + fileName);
using (var xlPackage = ExcelHelper.OpenFromFile(fullpath))
{
//Note: http://excelpackage.codeplex.com/wikipage?title=Creating%20an%20Excel%20spreadsheet%20from%20scratch&referringTitle=Home
const string exportName = "PayCard";
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(exportName);
//Add table headers going cell by cell.
worksheet.Cell(1, 1).Value = "STT";
worksheet.Cell(1, 2).Value = "ID";
worksheet.Cell(1, 3).Value = "Mã hợp đồng";
worksheet.Cell(1, 4).Value = "Mã thẻ";
worksheet.Cell(1, 5).Value = "Mã giao dịch";
worksheet.Cell(1, 6).Value = "Thông báo";
worksheet.Cell(1, 7).Value = "Thời gian";
worksheet.Column(3).Width = 25;
worksheet.Column(4).Width = 30;
worksheet.Column(6).Width = 60;
worksheet.Column(7).Width = 30;
worksheet.Column(3).StyleID = 1;
//Fill dòng
for (int k = 0; k < list.Count(); k++)
{
var c = list[k];
worksheet.Cell(k + 2, 1).Value = (k + 1).ToString();
worksheet.Cell(k + 2, 2).Value = c.ID.ToString();
worksheet.Cell(k + 2, 3).Value = c.UserId;
worksheet.Cell(k + 2, 4).Value = c.CardId;
worksheet.Cell(k + 2, 5).Value = c.ResulId;
worksheet.Cell(k + 2, 6).Value = c.Msg;
worksheet.Cell(k + 2, 7).Value = string.Format("{0:HH:mm:ss dd/MM/yyyy}", c.CreateDate);
}
xlPackage.Workbook.Properties.Title = exportName;
//xlPackage.Workbook.Properties.Author = "kienthuc.net.vn";
//xlPackage.Workbook.Properties.SetCustomPropertyValue("nhuanbut", sdate);
xlPackage.Save();
//Alert.Show("Export thành công ra tệp " + newFile.FullName.Replace("\\", "/"));
Response.Redirect("/PayCard/Export/" + fileName);
}
}
catch (Exception theException)
{
Alert.Show(theException.Message.Replace("\\", "/"));
}
}
public void Write(int rows, int cols)
{
String file = "testWs.xlsx";
if (File.Exists(file))
{
File.Delete(file);
}
TimeSpan start;
// Write
Console.WriteLine();
Console.WriteLine("Test: " + rows + " x " + cols);
Console.WriteLine("=======");
Console.WriteLine("Writing");
start = Process.GetCurrentProcess().TotalProcessorTime;
using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
{
int div = Math.Max(1, rows / 20);
ExcelWorksheet ws = package.Workbook.Worksheets.Add("Stress", rows, cols);
TimeRestart("Create", ref start);
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
ws.Cell(row + 1, col + 1).Value = GetVal(row, col);
}
if (row % div == 0)
{
Console.Write("*");
}
}
Console.WriteLine("done");
TimeRestart("Write", ref start);
package.Save();
}
TimeRestart("Save", ref start);
// Read
Console.WriteLine("Reading");
start = Process.GetCurrentProcess().TotalProcessorTime;
using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
{
int div = Math.Max(1, rows / 20);
ExcelWorksheet ws = package.Workbook.Worksheets["Stress"];
TimeRestart("Open", ref start);
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
ExcelCell cell = ws.Cell(row + 1, col + 1);
String val = cell.Value;
Assert.AreEqual(GetVal(row, col), cell.Value, "@" + (row + 1) + ", " + (col + 1));
}
if (row % div == 0)
{
Console.Write("*");
}
}
Console.WriteLine("done");
TimeRestart("Read", ref start);
}
Console.WriteLine(" "); // skip
}
/// <summary>
/// ExcelPackage组件方法生成Insert语句。
/// </summary>
/// <param name="myWorksheet">引用ExcelPackage组件的某张Sheet表的数据内容</param>
/// <returns></returns>
public static void excelPackagePrintSQLLangInsertMulti(ExcelWorksheet myWorksheet)
{
int hangY = 1, lieXX = 1;
string eCellStr = "";
string basicStr = "INSERT INTO ";
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
lieXX++;
eCellStr = myWorksheet.Cell(1, lieXX).Value;
}
eCellStr = myWorksheet.Cell(1, 1).Value;
while (eCellStr != null && eCellStr != "")
{
hangY++;
eCellStr = myWorksheet.Cell(hangY, 1).Value;
}
if (hangY < 3 || lieXX < 3)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("表格内容太少，无进行语句生成！确认返回并重新选择文件？", "提醒",
MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
return; //如果行列太少，那么直接无视！
}
FileStream aFile = new FileStream("InsertMulti.txt", FileMode.Append);
StreamWriter sw = new StreamWriter(aFile);
/*
* INSERT INTO `kswiki2`.`wish` (id,user_id,title,text,created_at,votes_count) VALUES
* ('526','17','我想','你好啊','2014-09-20 21:33:25','230'),
* ('527','18','不想','我好啊','2014-09-21 21:34:26','231');
*/
for (int i = 1; i < hangY; i++)
{
string outPrint = "";
if (i == 1)
{
outPrint = basicStr + myWorksheet.Cell(2, 1).Value + " (";
}
else
{
outPrint = outPrint + "(";
}
for (int j = 2; j < lieXX; j++)
{
if (i == 1)
{
if (j != lieXX - 1)
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + ",";
}
else
{
outPrint = outPrint + myWorksheet.Cell(1, j).Value + ") VALUES ";
}
}
else
{
outPrint = outPrint + "'" + myWorksheet.Cell(i, j).Value + "'";
if (j != lieXX - 1)
{
outPrint = outPrint + ",";
}
else
{
if (i != hangY - 1)
{
outPrint = outPrint + "),";
}
else
{ //末行加分号，表示所有插入语句结束
outPrint = outPrint + ");";
}
}
}
}
// Write data to file.
sw.WriteLine(outPrint);
//清空缓冲区
sw.Flush();
}
//结束写入
sw.Close();
aFile.Close();
}