Python excel.WorkbookJSONReader类代码示例

 def clean_contact_upload_file(self):
     value = self.cleaned_data.get("contact_upload_file", None)
     if self.cleaned_data.get("use_contact_upload_file", "N") == "Y":
         if value is None:
             raise ValidationError("Please choose a file.")
         
         try:
             workbook = WorkbookJSONReader(value)
         except InvalidFileException:
             raise ValidationError("Invalid format. Please convert to Excel 2007 or higher (.xlsx) and try again.")
         
         try:
             worksheet = workbook.get_worksheet()
         except WorksheetNotFound:
             raise ValidationError("Workbook has no worksheets.")
         
         contacts = []
         for row in worksheet:
             if "PhoneNumber" not in row:
                 raise ValidationError("Column 'PhoneNumber' not found.")
             contacts.append({"phone_number" : validate_phone_number(row.get("PhoneNumber"))})
         
         if len(contacts) == 0:
             raise ValidationError(_("Please add at least one contact."))
         
         return contacts
     else:
         return None

def upload_sms_translations(request, domain):
    try:
        workbook = WorkbookJSONReader(request.file)
        translations = workbook.get_worksheet(title='translations')

        with StandaloneTranslationDoc.get_locked_obj(domain, "sms") as tdoc:
            msg_ids = sorted(_MESSAGES.keys())
            result = {}
            for lang in tdoc.langs:
                result[lang] = {}

            for row in translations:
                for lang in tdoc.langs:
                    if row.get(lang):
                        msg_id = row["property"]
                        if msg_id in msg_ids:
                            result[lang][msg_id] = str(row[lang]).strip()

            tdoc.translations = result
            tdoc.save()
        messages.success(request, _("SMS Translations Updated."))
    except Exception:
        notify_exception(request, 'SMS Upload Translations Error')
        messages.error(request, _("Update failed. We're looking into it."))

    return HttpResponseRedirect(reverse('sms_languages', args=[domain]))

def update_domains(request):
    if request.method == "POST":
        try:
            workbook = WorkbookJSONReader(request.file)
            domains = workbook.get_worksheet(title="domains")
            success_count = 0
            fail_count = 0
            for row in domains:
                try:
                    name = row["name"]
                    domain = Domain.get_by_name(name)
                    if domain:
                        for k, v in row.items():
                            setattr(domain, k, v)
                        domain.save()
                        success_count += 1
                    else:
                        messages.warning(request, "No domain with name %s found" % name)
                        fail_count += 1
                except Exception, e:
                    messages.warning(request, "Update for %s failed: %s" % (row.get("name", "<No Name>"), e))
                    fail_count += 1
            if success_count:
                messages.success(request, "%s domains successfully updated" % success_count)
            if fail_count:
                messages.error(request, "%s domains had errors. details above." % fail_count)

        except Exception, e:
            messages.error(request, "Something went wrong! Update failed. Here's your error: %s" % e)

    def test_archive_forms_basic(self):
        uploaded_file = WorkbookJSONReader(join(BASE_PATH, BASIC_XLSX))

        response = archive_forms(self.domain_name, self.user, list(uploaded_file.get_worksheet()))

        # Need to re-get instance from DB to get updated attributes
        for key, _id in self.XFORMS.iteritems():
            self.assertEqual(XFormInstance.get(_id).doc_type, 'XFormArchived')

        self.assertEqual(len(response['success']), len(self.xforms))

    def test_archive_forms_missing(self):
        uploaded_file = WorkbookJSONReader(join(BASE_PATH, MISSING_XLSX))

        response = archive_forms(self.domain_name, self.user, list(uploaded_file.get_worksheet()))

        for key, _id in self.XFORMS.iteritems():
            self.assertEqual(XFormInstance.get(_id).doc_type, 'XFormArchived')

        self.assertEqual(len(response['success']), len(self.xforms))
        self.assertEqual(len(response['errors']), 1,
                         "One error for trying to archive a missing form")

 def clean_mach_file(self):
     if 'mach_file' in self.cleaned_data:
         mach_file = self.cleaned_data['mach_file']
         try:
             mach_file = WorkbookJSONReader(mach_file)
             mach_file = mach_file.get_worksheet()
         except InvalidFileException:
             raise forms.ValidationError("Please convert to Excel 2007 or higher (.xlsx) and try again.")
         except Exception as e:
             raise forms.ValidationError("Encountered error: %s" % e)
         return mach_file

    def post(self, request):
        """View's dispatch method automatically calls this"""

        try:
            workbook = WorkbookJSONReader(request.file)
        except AttributeError:
            return HttpResponseBadRequest("Error processing your Excel (.xlsx) file")

        try:
            data_types = workbook.get_worksheet(title='types')
        except KeyError:
            return HttpResponseBadRequest("Workbook does not have a sheet called 'types'")

        for dt in data_types:
            print "DataType"
            print dt

            for di in workbook.get_worksheet(title=dt['tag']):
                print "DataItem"
                print di

        for dt in data_types:
            data_type = FixtureDataType(
                domain=self.domain,
                name=dt['name'],
                tag=dt['tag'],
                fields=dt['field'],
            )
            data_type.save()
            data_items = workbook.get_worksheet(data_type.tag)
            for di in data_items:
                print di
                data_item = FixtureDataItem(
                    domain=self.domain,
                    data_type_id=data_type.get_id,
                    fields=di['field']
                )
                data_item.save()
                for group_name in di.get('group', []):
                    group = Group.by_name(self.domain, group_name)
                    if group:
                        data_item.add_group(group)
                    else:
                        messages.error(request, "Unknown group: %s" % group_name)
                for raw_username in di.get('user', []):
                    username = normalize_username(raw_username, self.domain)
                    user = CommCareUser.get_by_username(username)
                    if user:
                        data_item.add_user(user)
                    else:
                        messages.error(request, "Unknown user: %s" % raw_username)

        return HttpResponseRedirect(reverse('fixture_view', args=[self.domain]))

def import_locations_async(domain, file_ref_id, update_existing=False):
    task = import_locations_async

    DownloadBase.set_progress(task, 0, 100)
    download_ref = DownloadBase.get(file_ref_id)
    workbook = WorkbookJSONReader(download_ref.get_filename())
    worksheet = workbook.get_worksheet()

    results = list(import_locations(domain, worksheet, update_existing, task))

    DownloadBase.set_progress(task, 100, 100)

    return {
        'messages': results
    }

 def __init__(self, file_or_filename):
     try:
         self.workbook = WorkbookJSONReader(file_or_filename)
     except AttributeError:
         raise FixtureUploadError(_("Error processing your Excel (.xlsx) file"))
     except Exception:
         raise FixtureUploadError(_("Invalid file-format. Please upload a valid xlsx file."))

    def post(self, request, *args, **kwargs):
        upload = request.FILES.get("bulk_upload_file")
        """View's dispatch method automatically calls this"""
        try:
            self.workbook = WorkbookJSONReader(upload)
        except InvalidFileException:
            try:
                csv.DictReader(io.StringIO(upload.read().decode("ascii"), newline=None))
                return HttpResponseBadRequest(
                    "CommCare HQ no longer supports CSV upload. "
                    "Please convert to Excel 2007 or higher (.xlsx) "
                    "and try again."
                )
            except UnicodeDecodeError:
                return HttpResponseBadRequest("Unrecognized format")
        except JSONReaderError as e:
            messages.error(request, "Your upload was unsuccessful. %s" % e.message)
            return self.get(request, *args, **kwargs)
        except HeaderValueError as e:
            return HttpResponseBadRequest("Upload encountered a data type error: %s" % e.message)

        try:
            self.user_specs = self.workbook.get_worksheet(title="users")
        except WorksheetNotFound:
            try:
                self.user_specs = self.workbook.get_worksheet()
            except WorksheetNotFound:
                return HttpResponseBadRequest("Workbook has no worksheets")

        try:
            self.group_specs = self.workbook.get_worksheet(title="groups")
        except WorksheetNotFound:
            self.group_specs = []

        self.location_specs = []
        if Domain.get_by_name(self.domain).commtrack_enabled:
            try:
                self.location_specs = self.workbook.get_worksheet(title="locations")
            except WorksheetNotFound:
                # if there is no sheet for locations (since this was added
                # later and is optional) we don't error
                pass

        try:
            check_headers(self.user_specs)
        except UserUploadError as e:
            return HttpResponseBadRequest(e)

        task_ref = expose_cached_download(None, expiry=1 * 60 * 60)
        task = bulk_upload_async.delay(
            self.domain, list(self.user_specs), list(self.group_specs), list(self.location_specs)
        )
        task_ref.set_task(task)
        return HttpResponseRedirect(reverse(UserUploadStatusView.urlname, args=[self.domain, task_ref.download_id]))

class FixtureWorkbook(object):
    """
    Helper class for working with the fixture workbook
    """

    def __init__(self, file_or_filename):
        try:
            self.workbook = WorkbookJSONReader(file_or_filename)
        except AttributeError:
            raise FixtureUploadError(_("Error processing your Excel (.xlsx) file"))
        except Exception:
            raise FixtureUploadError(_("Invalid file-format. Please upload a valid xlsx file."))


    def get_types_sheet(self):
        try:
            return self.workbook.get_worksheet(title='types')
        except WorksheetNotFound as e:
            raise FixtureUploadError(_("Workbook does not contain a sheet called '%(title)s'") % {'title': e.title})

    def get_data_sheet(self, data_type):
        return self.workbook.get_worksheet(data_type.tag)

    def get_all_type_sheets(self):
        type_sheets = []
        seen_tags = set()
        for number_of_fixtures, dt in enumerate(self.get_types_sheet()):
            table_definition = FixtureTableDefinition.from_row(dt)
            if table_definition.table_id in seen_tags:
                raise DuplicateFixtureTagException(
                    _(FAILURE_MESSAGES['duplicate_tag']).format(tag=table_definition.table_id))

            seen_tags.add(table_definition.table_id)
            type_sheets.append(table_definition)
        return type_sheets

    def validate(self):
        self.get_types_sheet()
        self.get_all_type_sheets()

    def post(self, request):
        """View's dispatch method automatically calls this"""
        redirect = request.POST.get('redirect')

        try:
            self.workbook = WorkbookJSONReader(request.file)
        except InvalidFileException:
            try:
                csv.DictReader(io.StringIO(request.file.read().decode('ascii'),
                                           newline=None))
                return HttpResponseBadRequest(
                    "CommCare HQ no longer supports CSV upload. "
                    "Please convert to Excel 2007 or higher (.xlsx) "
                    "and try again."
                )
            except UnicodeDecodeError:
                return HttpResponseBadRequest("Unrecognized format")
        except JSONReaderError as e:
            messages.error(request,
                           'Your upload was unsuccessful. %s' % e.message)
            return HttpResponseRedirect(redirect)

        try:
            self.user_specs = self.workbook.get_worksheet(title='users')
        except WorksheetNotFound:
            try:
                self.user_specs = self.workbook.get_worksheet()
            except WorksheetNotFound:
                return HttpResponseBadRequest("Workbook has no worksheets")

        try:
            self.group_specs = self.workbook.get_worksheet(title='groups')
        except WorksheetNotFound:
            self.group_specs = []

        try:
            check_headers(self.user_specs)
        except Exception, e:
            return HttpResponseBadRequest(e)

    def clean_message_bank_file(self):
        value = self.cleaned_data.get("message_bank_file")

        if not value:
            raise ValidationError(_("Please choose a file."))

        try:
            workbook = WorkbookJSONReader(value)
        except InvalidFileException:
            raise ValidationError(_("Invalid format. Please convert to Excel 2007 or higher (.xlsx) and try again."))

        try:
            worksheet = workbook.get_worksheet()
        except WorksheetNotFound:
            raise ValidationError(_("Workbook has no worksheets."))

        message_ids = {}
        messages = []
        row_num = 2
        for row in worksheet:
            if "ID" not in row:
                raise ValidationError(_("Column 'ID' not found."))
            if "Message" not in row:
                raise ValidationError(_("Column 'Message' not found."))

            msg_id = row.get("ID")
            text = row.get("Message")

            try:
                assert isinstance(msg_id, basestring)
                msg_id = msg_id.strip()
                assert len(msg_id) > 1
                assert msg_id[0].upper() in "ABCDEFGH"
            except Exception:
                raise ValidationError(_("Invalid ID at row %(row_num)s") % {"row_num" : row_num})

            if msg_id in message_ids:
                raise ValidationError(_("Duplicate ID at row %(row_num)s") % {"row_num" : row_num})

            try:
                assert isinstance(text, basestring)
                text = text.strip()
                assert len(text) > 0
            except Exception:
                raise ValidationError(_("Invalid Message at row %(row_num)s") % {"row_num" : row_num})

            try:
                msg_id.encode("ascii")
            except Exception:
                raise ValidationError(_("ID at row %(row_num)s contains invalid character(s)") % {"row_num" : row_num})

            try:
                text.encode("ascii")
            except Exception:
                raise ValidationError(_("Message at row %(row_num)s contains invalid character(s)") % {"row_num" : row_num})

            if len(text) > 160:
                raise ValidationError(_("Message at row %(row_num)s is longer than 160 characters.") % {"row_num" : row_num})

            messages.append({
                "msg_id" : msg_id,
                "text" : text,
            })
            message_ids[msg_id] = True
            row_num += 1

        return messages

class UploadCommCareUsers(TemplateView):

    template_name = 'users/upload_commcare_users.html'

    def get_context_data(self, **kwargs):
        """TemplateView automatically calls this to render the view (on a get)"""
        context = _users_context(self.request, self.domain)
        context["show_secret_settings"] = self.request.REQUEST.get("secret", False)
        return context

    @method_decorator(get_file)
    def post(self, request):
        """View's dispatch method automatically calls this"""

        try:
            self.workbook = WorkbookJSONReader(request.file)
        except InvalidFileException:
            try:
                csv.DictReader(io.StringIO(request.file.read().decode('ascii'), newline=None))
                return HttpResponseBadRequest(
                    "CommCare HQ no longer supports CSV upload. "
                    "Please convert to Excel 2007 or higher (.xlsx) and try again."
                )
            except UnicodeDecodeError:
                return HttpResponseBadRequest("Unrecognized format")

        try:
            self.user_specs = self.workbook.get_worksheet(title='users')
        except KeyError:
            try:
                self.user_specs = self.workbook.get_worksheet()
            except IndexError:
                return HttpResponseBadRequest("Workbook has no worksheets")

        try:
            self.group_specs = self.workbook.get_worksheet(title='groups')
        except KeyError:
            self.group_specs = []

        try:
            check_headers(self.user_specs)
        except Exception, e:
            return HttpResponseBadRequest(e)

        response = HttpResponse()
        response_writer = csv.DictWriter(response, ['username', 'flag', 'row'])
        response_rows = []
        async = request.REQUEST.get("async", False)
        if async:
            download_id = uuid.uuid4().hex
            bulk_upload_async.delay(download_id, self.domain,
                                    list(self.user_specs),
                                    list(self.group_specs))
            messages.success(request, 
                'Your upload is in progress. You can check the progress at "%s%s".' %  \
                (get_url_base(), reverse('retrieve_download', kwargs={'download_id': download_id})),
                extra_tags="html")
        else:
            ret = create_or_update_users_and_groups(self.domain, self.user_specs, self.group_specs)
            for error in ret["errors"]:
                messages.error(request, error)
            
            for row in ret["rows"]:
                response_writer.writerow(row)
                response_rows.append(row)

        redirect = request.POST.get('redirect')
        if redirect:
            if not async:
                messages.success(request, 'Your bulk user upload is complete!')
            problem_rows = []
            for row in response_rows:
                if row['flag'] not in ('updated', 'created'):
                    problem_rows.append(row)
            if problem_rows:
                messages.error(request, 'However, we ran into problems with the following users:')
                for row in problem_rows:
                    if row['flag'] == 'missing-data':
                        messages.error(request, 'A row with no username was skipped')
                    else:
                        messages.error(request, '{username}: {flag}'.format(**row))
            return HttpResponseRedirect(redirect)
        else:
            return response

    def test_archive_forms_wrong_domain(self):
        uploaded_file = WorkbookJSONReader(join(BASE_PATH, BASIC_XLSX))

        response = archive_forms('wrong_domain', self.user, list(uploaded_file.get_worksheet()))

        self.assertEqual(len(response['errors']), len(self.xforms), "Error when wrong domain")