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(); }