Index: app/ui/src/java/org/sakaiproject/tool/gradebook/ui/RosterBean.java =================================================================== --- app/ui/src/java/org/sakaiproject/tool/gradebook/ui/RosterBean.java (revision 74434) +++ app/ui/src/java/org/sakaiproject/tool/gradebook/ui/RosterBean.java (working copy) @@ -695,6 +695,14 @@ return letterGrade; } + public void exportXlsNoCourseGrade(ActionEvent event){ + if(logger.isInfoEnabled()) logger.info("exporting gradebook " + getGradebookUid() + " as Excel"); + getGradebookBean().getEventTrackingService().postEvent("gradebook.downloadRoster","/gradebook/"+getGradebookId()+"/"+getAuthzLevel()); + SpreadsheetUtil.downloadSpreadsheetData(getSpreadsheetData(false), + getDownloadFileName(getLocalizedString("export_gradebook_prefix")), + new SpreadsheetDataFileWriterXls()); + } + public void exportCsvNoCourseGrade(ActionEvent event){ if(logger.isInfoEnabled()) logger.info("exporting gradebook " + getGradebookUid() + " as CSV"); getGradebookBean().getEventTrackingService().postEvent("gradebook.downloadRoster","/gradebook/"+getGradebookId()+"/"+getAuthzLevel()); Index: app/ui/src/java/org/sakaiproject/tool/gradebook/ui/SpreadsheetUploadBean.java =================================================================== --- app/ui/src/java/org/sakaiproject/tool/gradebook/ui/SpreadsheetUploadBean.java (revision 74415) +++ app/ui/src/java/org/sakaiproject/tool/gradebook/ui/SpreadsheetUploadBean.java (working copy) @@ -22,6 +22,7 @@ import java.io.InputStream; import java.io.InputStreamReader; import java.io.Serializable; +import java.text.NumberFormat; import java.util.ArrayList; import java.util.Date; import java.util.Enumeration; @@ -41,6 +42,10 @@ import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.myfaces.custom.fileupload.UploadedFile; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.sakaiproject.component.cover.ComponentManager; import org.sakaiproject.component.cover.ServerConfigurationService; import org.sakaiproject.content.api.ContentResource; @@ -66,6 +71,7 @@ import org.sakaiproject.tool.gradebook.Gradebook; import org.sakaiproject.tool.gradebook.LetterGradePercentMapping; import org.sakaiproject.tool.gradebook.jsf.FacesUtil; +import org.sakaiproject.util.ResourceLoader; import org.sakaiproject.util.Validator; public class SpreadsheetUploadBean extends GradebookDependentBean implements Serializable { @@ -98,6 +104,7 @@ private Gradebook localGradebook; private StringBuilder externallyMaintainedImportMsg; private UIComponent uploadButton; + private String csvDelimiter; // Used for bulk upload of gradebook items // Holds list of unknown user ids @@ -562,15 +569,21 @@ maxFileSizeInMB = FILE_SIZE_DEFAULT; } long maxFileSizeInBytes = 1024L * 1024L * maxFileSizeInMB; + + boolean isXlsImport = false; if (upFile != null) { if (upFile != null && logger.isDebugEnabled()) { logger.debug("file size " + upFile.getSize() + "file name " + upFile.getName() + "file Content Type " + upFile.getContentType() + ""); } - if (logger.isDebugEnabled()) logger.debug("check that the file is csv file"); + if(logger.isDebugEnabled()) logger.debug("check that the file type is allowed"); - if (!upFile.getName().endsWith("csv")) { + if (upFile.getName().endsWith("csv")) { + isXlsImport = false; + } else if (upFile.getName().endsWith("xls")) { + isXlsImport = true; + } else { FacesUtil.addErrorMessage(getLocalizedString("import_entire_filetype_error",new String[] {upFile.getName()})); return null; } @@ -589,11 +602,22 @@ if (pickedFileReference != null) { if (logger.isDebugEnabled()) logger.debug("check that the file is csv file"); - if (pickedFileDesc == null || !pickedFileDesc.endsWith("csv")) { + if (pickedFileDesc == null) { FacesUtil.addErrorMessage(getLocalizedString("import_entire_filetype_error", new String[] {pickedFileDesc})); return null; } + if(logger.isDebugEnabled()) logger.debug("check that the file type is allowed"); + + if (pickedFileDesc.endsWith("csv")) { + isXlsImport = false; + } else if (pickedFileDesc.endsWith("xls")) { + isXlsImport = true; + } else { + FacesUtil.addErrorMessage(getLocalizedString("import_entire_filetype_error",new String[] {pickedFileDesc})); + return null; + } + fileName = pickedFileDesc; ContentResource resource = getPickedContentResource(); @@ -622,12 +646,20 @@ } try { - contents = csvtoArray(inputStream); - inputStream.close(); + if (isXlsImport) { + contents = excelToArray(inputStream); + } else { + contents = csvtoArray(inputStream); + } } catch(IOException ioe) { FacesUtil.addErrorMessage(getLocalizedString("upload_view_config_error")); return null; + } + finally { + if (inputStream != null) { + inputStream.close(); + } } // double check that the number of rows in this spreadsheet is reasonable @@ -752,15 +784,20 @@ maxFileSizeInMB = FILE_SIZE_DEFAULT; } long maxFileSizeInBytes = 1024L * 1024L * maxFileSizeInMB; + boolean isXlsImport = false; if (upFile != null) { if (upFile != null && logger.isDebugEnabled()) { logger.debug("file size " + upFile.getSize() + "file name " + upFile.getName() + "file Content Type " + upFile.getContentType() + ""); } - if (logger.isDebugEnabled()) logger.debug("check that the file is csv file"); + if(logger.isDebugEnabled()) logger.debug("check that the file type is allowed"); - if (!upFile.getName().endsWith("csv")) { + if (upFile.getName().endsWith("csv")) { + isXlsImport = false; + } else if (upFile.getName().endsWith("xls")) { + isXlsImport = true; + } else { FacesUtil.addErrorMessage(getLocalizedString("upload_view_filetype_error",new String[] {upFile.getName()})); return null; } @@ -779,11 +816,22 @@ if (pickedFileReference != null) { if (logger.isDebugEnabled()) logger.debug("check that the file is csv file"); - if (pickedFileDesc == null || !pickedFileDesc.endsWith("csv")) { + if (pickedFileDesc == null) { FacesUtil.addErrorMessage(getLocalizedString("upload_view_filetype_error", new String[] {pickedFileDesc})); return null; } + if(logger.isDebugEnabled()) logger.debug("check that the file type is allowed"); + + if (pickedFileDesc.endsWith("csv")) { + isXlsImport = false; + } else if (pickedFileDesc.endsWith("xls")) { + isXlsImport = true; + } else { + FacesUtil.addErrorMessage(getLocalizedString("import_entire_filetype_error",new String[] {pickedFileDesc})); + return null; + } + fileName = pickedFileDesc; ContentResource resource = getPickedContentResource(); @@ -812,13 +860,21 @@ } try { - contents = csvtoArray(inputStream); - inputStream.close(); + if (isXlsImport) { + contents = excelToArray(inputStream); + } else { + contents = csvtoArray(inputStream); + } } catch(IOException ioe) { FacesUtil.addErrorMessage(getLocalizedString("upload_view_config_error")); return null; - } + } + finally { + if (inputStream != null) { + inputStream.close(); + } + } // double check that the number of rows in this spreadsheet is reasonable int numStudentsInSite = getNumStudentsInSite(); @@ -1350,7 +1406,7 @@ List line = row.getRowcontent(); String userid = ""; - final String user = (String)line.get(0); + final String user = ((String)line.get(0)).toLowerCase(); try { userid = ((User)rosterMap.get(user)).getUserUid(); @@ -2105,7 +2161,60 @@ } } + //************************ EXCEL file parsing ***************************** /** + * method converts an input stream to an List consisting of strings + * representing a line. The input stream must be for an xls file. + * + * @param inputStream + * @return contents + */ + private List excelToArray(InputStream inputStreams) throws IOException { + HSSFWorkbook wb = new HSSFWorkbook(inputStreams); + + //Convert an Excel file to csv + HSSFSheet sheet = wb.getSheetAt(0); + List array = new ArrayList(); + Iterator it = sheet.rowIterator(); + while (it.hasNext()){ + HSSFRow row = (HSSFRow) it.next(); + String rowAsString = fromHSSFRowtoCSV(row); + if (rowAsString.replaceAll(",", "").replaceAll("\"", "").equals("")) { + continue; + } + array.add(fromHSSFRowtoCSV(row)); + } + return array; + } + + private String fromHSSFRowtoCSV(HSSFRow row){ + String csvRow = ""; + int l = row.getLastCellNum(); + for (int i=0;i [si esto no es correcto, pulse en el bot\u00F3n Cancelar y seleccione otra] import_preview_assignment_selection_failure=Por favor, seleccione una tarea a importar @@ -352,7 +355,7 @@ javax.faces.validator.LengthValidator.MINIMUM=Debe tener por lo menos {0} caracteres. loading_dock_delete_failure=Imposible borrar hoja de c\u00E1lculo loading_dock_delete_success=La hoja de c\u00E1lculo {0} se ha borrado con \u00E9xito -loading_dock_instructions=Abajo se muestra el Muelle de Carga, un lugar en el que puede subir sus hojas de c\u00E1lculo (en formato .csv) desde su disco duro local.
Una vez que han sido cargadas aqu\u00ED, los datos de la hoja de c\u00E1lculo podr\u00E1n importarse parcial o completamente en Calificaciones. +loading_dock_instructions=Abajo se muestra el Muelle de Carga, un lugar en el que puede subir sus hojas de c\u00E1lculo (en formato CSV o XLS) desde su disco duro local.
Una vez que han sido cargadas aqu\u00ED, los datos de la hoja de c\u00E1lculo podr\u00E1n importarse parcial o completamente en Calificaciones. loading_dock_page_title=Subir/Importar loading_dock_table_creator=Creador loading_dock_table_datecreated=Fecha de creaci\u00F3n @@ -362,7 +365,7 @@ loading_dock_table_modifiedby=Modificado por loading_dock_table_title=T\u00EDtulo loading_dock_table_view=Importar -loading_dock_upload_link_text=Subir una hoja de c\u00E1lculo (en formato csv) al Muelle de Carga +loading_dock_upload_link_text=Subir una hoja de c\u00E1lculo (en formato CSV o XLS) al Muelle de Carga. El fichero CSV debe utilizar la coma (",") como separador de campos y las comillas como delimitador de texto. needed_total=Total necesario opt_categories_and_weighting=Categor\u00EDas y pesos opt_categories_only=S\u00F3lo categor\u00EDas @@ -494,11 +497,11 @@ upload_view_choose_file=Elija un t\u00EDtulo * upload_view_config_error=configuraci\u00F3n de subida err\u00F3nea upload_view_failure=fichero no subido. Por favor, compruebe su configuraci\u00F3n o contacte su administrador del sistema -upload_view_filecontent_error=Una columna de su fichero .csv debe contener nombres de usuario. La primera fila indica las cabeceras de las columnas. +upload_view_filecontent_error=Una columna de su fichero debe contener nombres de usuario. La primera fila indica las cabeceras de las columnas. upload_view_filetype_error=El fichero "{0}" que acaba de subir no es de un tipo v\u00E1lido upload_view_instructions=Instrucciones\: -upload_view_instructions_text=Su hoja de c\u00E1lculo debe estar guardada en formato .csv.
Una columna de su fichero .csv debe contener nombre de usuario individuales.
La primera fila indica las cabeceras de las columnas. -upload_view_page_title=Subir una hoja de c\u00E1lculo (formato csv) al Muelle de Carga +upload_view_instructions_text=Su hoja de c\u00E1lculo debe estar guardada en formato csv o xls.
Una columna de su fichero debe contener nombre de usuario individuales.
La primera fila indica las cabeceras de las columnas. +upload_view_page_title=Subir una hoja de c\u00E1lculo (formato csv o xls) al Muelle de Carga upload_view_save=Guardar upload_view_title=T\u00EDtulo * validation_messages_present=Ha habido problemas con tu \u00FAltimo env\u00EDo. Por favor, mire los detalles abajo. Index: app/ui/src/bundle/org/sakaiproject/tool/gradebook/bundle/Messages.properties =================================================================== --- app/ui/src/bundle/org/sakaiproject/tool/gradebook/bundle/Messages.properties (revision 74669) +++ app/ui/src/bundle/org/sakaiproject/tool/gradebook/bundle/Messages.properties (working copy) @@ -174,7 +174,8 @@ course_grade_details_no_enrollments=No students to display course_grade_details_export_excel=Export for Excel course_grade_details_export_csv=Export CSV -course_grade_details_export_course_grades=Export Course Grades +course_grade_details_export_course_grades=Export Course Grades as CSV +course_grade_details_export_course_grades_excel=Export Course Grades for Excel course_grade_details_course_grade_column_name=Course Grade course_grade_details_log=Log course_grade_details_log_type=Grade @@ -206,7 +207,7 @@ roster_student_id=Student ID roster_average_category=Average Category Grade roster_course_grade_column_name=Course Grade -roster_export_csv=Export Gradebook +roster_export_csv=Export as CSV roster_export_excel=Export for Excel roster_footnote_symbol1=* roster_footnote_symbol2=** @@ -403,17 +404,17 @@ groupbox_main=Gradebook Groupbox (Main) #messages for upload view -upload_view_page_title = Upload a spreadsheet (csv format) to Loading Dock +upload_view_page_title = Upload a spreadsheet (csv or xls format) to Loading Dock upload_view_instructions = Instructions: -upload_view_instructions_text = Your spreadsheet file must be saved in csv format.
One column of your csv file must contain individual's usernames.
The first row of your csv must contain headings for the columns. +upload_view_instructions_text = Your Spreadsheet file must be saved in csv or xls format.
One column of your file must contain individual's usernames.
The first row of your file must contain headings for the columns. upload_view_title = Title * upload_view_choose_file = Filename * upload_view_save = Save upload_view_cancel = Cancel -upload_view_filetype_error = The file "{0}" that you uploaded is not a valid file type. Only "csv" files are valid for uploading. +upload_view_filetype_error = The file "{0}" that you uploaded is not a valid file type. Only "csv" or "xls" files are valid for uploading. upload_view_failure= file not uploaded, please check your configuration or contact your system administrator upload_view_config_error = upload configuration error -upload_view_filecontent_error = One column of your csv file must contain individual's usernames. The first row of your csv must contain headings for the columns. +upload_view_filecontent_error = One column of your file must contain individual's usernames. The first row of your file must contain headings for the columns. # {0} = the number of rows in the uploaded spreadsheet, {1} = the number of students in the site upload_view_filerows_error = The number of rows in the uploaded spreadsheet ({0}) is greater than the number of students in this site ({1}). You may only upload grade information for the students in your site. Please verify that you are uploading the correct spreadsheet for this site. # {0} = the upload maximum in MB @@ -421,8 +422,8 @@ #messages for loading dock view loading_dock_page_title = Import Gradebook Item -loading_dock_instructions = Below is the loading dock, a holding place for spreadsheets (csv format) that have been uploaded from your local computer.
Once loaded here, the data in the spreadsheet can be imported in part or whole into your gradebook. -loading_dock_upload_link_text = Upload spreadsheet (csv format) to Loading Dock +loading_dock_instructions = Below is the loading dock, a holding place for spreadsheets (csv or xls format) that have been uploaded from your local computer.
Once loaded here, the data in the spreadsheet can be imported in part or whole into your gradebook. +loading_dock_upload_link_text = Upload spreadsheet (csv or xls format) to Loading Dock loading_dock_table_header = Loading Dock loading_dock_table_title = Title loading_dock_table_creator = Creator @@ -436,7 +437,7 @@ #messages for upload preview upload_preview_page_title = Verify Upload -upload_preview_instructions = You are uploading a spreadsheet file (csv format) to the loading dock: +upload_preview_instructions = You are uploading a spreadsheet file (xls or csv format) to the loading dock: upload_preview_column_count =
  • Spreadsheet file has {0} gradebook items
    • upload_preview_row_count =
  • Spreadsheet file contains {0} students
    • upload_preview_additional_text = Below is a display of the contents of your spreadsheet.
    [if it is not correct, click the back button, make changes to your file, and upload it again.] @@ -484,11 +485,13 @@ #messages for entire gradebook import import_entire_main_title=Import Grades import_entire_instructions=If you would like to edit your grades in a spreadsheet application (e.g., Excel) and import the grades into the gradebook, then follow the steps below: -import_entire_template=Download Spreadsheet Template +import_entire_template=Download Spreadsheet Template as CSV +import_entire_template_excel=Download Spreadsheet Template for Excel +import_entire_template_or=-or- import_entire_template_prefix=1. import_entire_edit=Edit Spreadsheet import_entire_edit_prefix=2. -import_entire_edit_inst=Edit the spreadsheet in your favorite spreadsheet application, such as Excel, and save it as a csv file. +import_entire_edit_inst=Edit the spreadsheet in your favorite spreadsheet application, such as Excel, and save it as a csv or xls file. import_entire_import=Import Spreadsheet import_entire_import_prefix=3. import_entire_import_inst=Click Choose File, then Browse to select the file you saved in step 2, then click Import Spreadsheet. Index: app/ui/src/webapp/courseGradeDetails.jsp =================================================================== --- app/ui/src/webapp/courseGradeDetails.jsp (revision 74415) +++ app/ui/src/webapp/courseGradeDetails.jsp (working copy) @@ -176,7 +176,13 @@ value="#{msgs.course_grade_details_export_course_grades}" actionListener="#{courseGradeDetailsBean.exportCsv}" rendered="#{!courseGradeDetailsBean.emptyEnrollments}" + style="margin-left: 5em;" /> +

    Index: app/ui/src/webapp/roster.jsp =================================================================== --- app/ui/src/webapp/roster.jsp (revision 74415) +++ app/ui/src/webapp/roster.jsp (working copy) @@ -13,6 +13,13 @@

    +


    - - - + + + + + + + + + + <%-- End of download csv file form --%> @@ -60,7 +67,7 @@ - + */%>